Linking Server in SQL Server 2008 R2

sql-server sql-server-2008 linked-server
Jeff picture Jeff · Mar 4, 2011 · Viewed 28.8k times · Source

Can anyone guide me around to linking a server to another in SQL Server 2008 R2? I am getting the following error when trying to do so in Management Studio.

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "CDSPM1" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "CDSPM1". OLE DB provider "SQLNCLI" for linked server "CDSPM1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

Answer

VDMT picture VDMT · Mar 5, 2011

Seems like Authentication problem.

Test by creating the linked Server using "Server Type" as "Sql Server". Then go into "Security" and set your user mapping. As a test, create a SQL login on the remote system and specify that on the "Be made using this security context"

Be sure that you can PING the "Linked Server" name first. HTH

Related questions

"Deferred prepare could not be completed" error when using local database as linked server

I created a Linked Server from my local Sql Server, as given in http://sqlserverplanet.com/dba/local-linked-server However when I try to execute a stored procedure in Linked Server, it throws: Deferred prepare could not be completed error and …

Read More
SQL Server spatial and linked servers

I have a SQL Server instance that I've added a linked server to another SQL instance. The table I'm accessing on the linked server contains spatial types. When I try to query the table I receive an error: Objects exposing …

Read More
"ASP.NET" error: Access to the remote server is denied because no login-mapping exists

I'm able to execute a stored procedure which queries a Linked Server directly, but when I call the same sproc though ASP.NET then I get the following error: Access to the remote server is denied because no login-mapping exists. …

Read More