| Doownai 2006-10-24, 6:29 pm |
| This may be in the same class as the discussion entitled "SQL Server 2005
Linked Server Security"
But it is just different enough that I thought that I would start my own.
Situation:
I am trying to create a linked server between SQL Server 2005 SP1 Standard
Edition (x64) and SQL Server 2000 SP3 Enterprise Edition (x86)
The linked server appears to create; however, when I try to query it I
recieve the following message:
OLE DB provider "SQLNCLI" for linked server "report" returned message "Login
timeout expired".
OLE DB provider "SQLNCLI" for linked server "report" 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 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Environments:
Microsoft SQL Server 2005 - 9.00.2153.00 (X64)
May 9 2006 13:58:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
I have created an alias (TCP/IP) to access the SQL Server 2000 instance.
This alias works for SSMS and SSIS.
As per information that is already out there, I have run instcat.sql (SP3)
against the SQL Server 2000 instance. This has been sufficient to resolve
the issue in all of my test environments but not, of course, in the
environment that I really care about (sigh).
I have also ensured the remote connectivity has been enabled on 2005.
I have tried creating the linked sever both in SSMS and by Scripting, with
the same result.
Firewall changes had to be made to allow these machines to talk to each
other... could something be amiss there?
I am about to begin my SSIS workaround but I sure would prefer to get this
working. Any help would be appreciated.
|