SquareOneResources
asked on
SQL Server 2008 Linked Server Error
We are trying to setup two SQL servers as linked servers to push data from our live production environment to a data warehouse.
When trying to test the linked server connection we get the following error(s):
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5)
I can confirm the servers are both up and running, and accessible from the network.
When trying to test the linked server connection we get the following error(s):
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5)
I can confirm the servers are both up and running, and accessible from the network.
ASKER
Sorry something to add, if we reference them as SERVERA (data warehouse) and SERVERB (live production server).
If we test linked server connection from SERVERA it completes successfully, however if we test linked server connection from SERVERB this is when it fails.
I'll take a look at the list and report back.
If we test linked server connection from SERVERA it completes successfully, however if we test linked server connection from SERVERB this is when it fails.
I'll take a look at the list and report back.
Have you created a linked server from SERVERB to SERVERA..
If so, then ideally it should work if remote connections are enabled in SERVERA and make sure Remote connections are enabled in SERVERA as per
http://support.microsoft.com/kb/914277
If so, then ideally it should work if remote connections are enabled in SERVERA and make sure Remote connections are enabled in SERVERA as per
http://support.microsoft.com/kb/914277
ASKER
Hi,
The two tech notes you've mentioned reference SQL 2005, we're using SQL 2008.
The two tech notes you've mentioned reference SQL 2005, we're using SQL 2008.
The pointers mentioned is applicable for both..
ASKER
Yes but the names of the locations, apps used, and settings are different. Do you have these for 2008?
To enable Remote connections:
http://msdn.microsoft.com/en-us/library/dd857537%28VS.85%29.aspx
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
And for the error, check this
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
http://msdn.microsoft.com/en-us/library/dd857537%28VS.85%29.aspx
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
And for the error, check this
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
ASKER
I've checked and on SERVERA (data warehouse) SQL Server Browser Service was disabled, I've
started this and now get the following message. SQL Server configuration manager is also set to Enabled for all types of connection except VIA.
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
started this and now get the following message. SQL Server configuration manager is also set to Enabled for all types of connection except VIA.
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
you need to specify a particular login for this linked server using sp_addlinkedsrvlogin..
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7/
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7/
ASKER
We have setup a SQL user login on both servers, its the same login name and password on both servers.
The Local Login setting on SERVERB is this SQL login, we have Impersonate ticked, and the option to Be made using the login's current security setting is selected. These settings are the same as on SERVERA which connects successfully.
The Local Login setting on SERVERB is this SQL login, we have Impersonate ticked, and the option to Be made using the login's current security setting is selected. These settings are the same as on SERVERA which connects successfully.
ASKER
We're now getting this error when testing the connection:
TITLE: Microsoft SQL Server Management Studio
--------------------------
The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
The OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "SQLRPRT".
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
TITLE: Microsoft SQL Server Management Studio
--------------------------
The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
The OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "SQLRPRT".
OLE DB provider "SQLNCLI10" for linked server "SQLRPRT" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
>> Invalid authorization specification
Right click Linked Server and then Choose Properties -> Go to Security.
Choose "Be made using this security context" and then specify the login credentials which has access to ServerA which should work..
Right click Linked Server and then Choose Properties -> Go to Security.
Choose "Be made using this security context" and then specify the login credentials which has access to ServerA which should work..
ASKER
Hi, done this and we get:
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
One thing I have noticed though (and excuse me if this is a basic error but I don't know SQL well and am picking this up for someone else) but the local database that the SQL login on SERVERA has access to doesn't exist?!?! Will the test connection option try to access the database specified in the login or does that not matter at this stage?
TITLE: Microsoft SQL Server Management Studio
--------------------------
The test connection to the linked server failed.
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
One thing I have noticed though (and excuse me if this is a basic error but I don't know SQL well and am picking this up for someone else) but the local database that the SQL login on SERVERA has access to doesn't exist?!?! Will the test connection option try to access the database specified in the login or does that not matter at this stage?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kindly make sure the things listed over here are configured properly..
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/