Sql-server – MSSQL 2005 > Linked Server Connection Fails > MSSQL 2000

linked-serversql serversql-server-2005

At the company I work for, we currently are running 3xSQL 2005 servers and 1xSQL 2000 server. All of the 2005 servers can communicate with each other, however they cannot communicate with the 2000 server. On the reverse side, the 2000 server can communicate with all 3 2005 servers.

When a 2005 server attempts to connect with the 2000 server, I get the following error:

OLE DB provider "SQLNCLI" for linked
server "2000srv" returned message
"Login timeout expired". OLE DB
provider "SQLNCLI" for linked server
"2000srv" returned message "An error
has occurred while establishing a
connection to the server. When
connecting to SQL Server 2005, this
failure may be caused by the fact that
under the default settings SQL Server
does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0 Named
Pipes Provider: Could not open a
connection to SQL Server [5].

I have ensured that each server is added using the stored procedure:

EXEC master.dbo.sp_addlinkedserver
@server = N'2000srv',
@srvproduct=N'SQL Server'

I then tested using sp_testlinkedserver. All succeeded the test except for any 2005 server attempting to connect to the 2000 server.

What suggestions and possible solutions would you recommend following to resolve this error?

Best Answer

System stored procedures don't get updated properly with SQL2000 SP3/SP4 You have to run instcat.sql manually. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Related Solutions

Sql-server – Can’t see all catalogs on a MSSQL Linked Server

You will need to edit the security settings of the linked server to specify a login on the SQL 2005 instance that has permissions to all of the catalogs you wish to access through the linked server. I don't have a SQL 2000 server around anymore to give you the exact steps, but here is an MSDN article describing how to establish security for SQL 2000 linked servers.

EDIT:
See this article on SQL Server Central for configuring Kerberos authentication in order to allow linked servers to use the credentials of the currently logged in user in order to authenticate against the target server. See also the answers to my question on setting up trust delegation for SQL Server.

How to create a Linked Server in SQL Server 2005 to a password protected Access 95 database

After hours of struggling with this problem I finally found not one, but two solutions! I'm using SQL Server 2008 and Access 2000, but I guess the solution is the same.

Solution 1: Use OPENDATASOURCE

Use this approach when you will be accessing data on the linked server infrequently:

select * from 
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Data Source=C:\MyAccessDB.mdb;User ID=Admin;Password=;Jet OLEDB:Database Password=MyDBPassword;')...MyTable

For this to work you also need to turn on the Ad Hoc Distributed Queries option:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Solution 2: Use Linked Server

As you already stated, the linked server doesn't seem to work. However, there's a small gotcha that makes everything work smoothly; you need to specify the database password in the provider string like ;PWD=password.

exec sp_addlinkedserver 
    @server = 'TestLinkServer', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @srvproduct = 'Access',
    @datasrc = 'C:\MyAccessDB.mdb', 
    @provstr = ';PWD=MyDBPassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

GO

Now you can query your Access database as a linked server:

select * from TestLinkServer...MyTable

Related Topic