September 27, 2009 at 8:46 pm
I have recently migrated a db from SQL 2005 to SQL2008. An application needs to access that database from the original server but the linked server object I create within SQL 2005 fails to connect, getting a time out. I have tested and appear to have the same problem trying to created a linked server object for any of my SQL 2008 environments within the context of SQL 2005.
I have tested credentials and they are ok. I have created linked server object from one SQL 2008 server to another. I have even connected to the SQL2008 environment using the SQL 2005 Management Studio on the the original server. It's just the linked server object from SQL 2005 to SQL 2008 that is giving me grief.
Is it possible? If so how do I make it work?
Scott Pettman
February 22, 2010 at 1:02 pm
I am having a very similiar problem. I could create the linked server to the SQL2008 box from the SSMS2005 interface just fine, without any errors. I am certain the credentials work however when I attempt to run a SELECT from the linked server I receive the following error message.
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].
February 22, 2010 at 1:30 pm
So I thought that using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) would make a difference. It did not. As a matter of fact, the error message still indicates a "timeout" issue and further the SQLOLEDB provider is not accurately reflected in the error message. It is like the linked server FROM SQL2005 (using the SSMS2005 interface) to SQL2008 is still using the SQLNCLI provider regardless of how I set it up.
Here is the error message:
/*------------------------
SELECT * FROM TESTING.dbDBA.dbo.TestingShowContig
------------------------*/
OLE DB provider "SQLNCLI" for linked server "TESTING" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TESTING" 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 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
February 22, 2010 at 1:32 pm
I am running SQL2008 Express Edition. As it turns out, apparently all I needed to do to resolve this issue was to fix my firewall settings and turn on the SQL Browser Service. I had the SBS disabled, thinking I would not need it.
February 22, 2010 at 4:19 pm
Ulysses Brown (2/22/2010)
So I thought that using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) would make a difference. It did not. As a matter of fact, the error message still indicates a "timeout" issue and further the SQLOLEDB provider is not accurately reflected in the error message. It is like the linked server FROM SQL2005 (using the SSMS2005 interface) to SQL2008 is still using the SQLNCLI provider regardless of how I set it up.Here is the error message:
/*------------------------
SELECT * FROM TESTING.dbDBA.dbo.TestingShowContig
------------------------*/
OLE DB provider "SQLNCLI" for linked server "TESTING" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TESTING" 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 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
Check SQL server settings, whether Remote server connection is turned on or not, by default it is turned off.
EnjoY!
February 22, 2010 at 4:22 pm
Scott Pettman (9/27/2009)
I have recently migrated a db from SQL 2005 to SQL2008. An application needs to access that database from the original server but the linked server object I create within SQL 2005 fails to connect, getting a time out. I have tested and appear to have the same problem trying to created a linked server object for any of my SQL 2008 environments within the context of SQL 2005.I have tested credentials and they are ok. I have created linked server object from one SQL 2008 server to another. I have even connected to the SQL2008 environment using the SQL 2005 Management Studio on the the original server. It's just the linked server object from SQL 2005 to SQL 2008 that is giving me grief.
Is it possible? If so how do I make it work?
Scott Pettman
Is it just timeout error message? Can you post complete message, what do you see in SQL error logs?
EnjoY!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply