Cannot connect to 2000 from 2005 via linked server

Interesting issue.. I think

I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas?

Here is the error I get:

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" 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 10061, Level 16, State 1, Line 0

TCP Provider: No connection could be made because the target machine actively refused it.

To make sure I understand, you got box A running Win2K3 SP1 that has a SQL2K5 instance (inst1) and a SQL2K instance (inst2). You've got another box B that has a SQLK2K5 instance (inst3). Inst1 has linked server entries for inst2 and inst3. Inst3 has linked server entries for inst1. The problem is that from inst1 connecting to inst3 isn't working.

What is the OS of box B? It sounds like you may have a firewall running on box B preventing connectivity. Also, is the TCP protocol enabled on inst3?

Here's a link to a bunch of connectivity troubleshooting tips:
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

Hope this helps,
Vaughn

|||

Close.... but not right...

Box 1 -- Windows 2003 SP1, SQL 2005 SP1 (inst2) and SQL 2000 SP3 (inst1)

Box 2 -- Windows 2003 SP1, SQL 2000 SP3 (inst3)

So inst2 has a linked server to inst1 and inst3. It can talk to inst1 with no problem but gets the error posted above when talking to inst3. Inst3 can talk to inst1 and inst2 with no issues. also inst1 can talk to inst3 with no issues. It does it every day so I know it has nothing to do with the physical servers (hopefully).

|||

I solved the issue.. at least on a temporary basis. I created an ODBC connection using the SQL 2000 driver. It is not that fast but I am only selecting from a table with only a couple hundred records...

If anything else comes up please let me know.