Linking Server in SQL Server 2008 R2

Question

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 1

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

Answer 2

Use drop down and choose

SQL Server Native Client

instead of

SQL Server

Answer 3

Use below link to view the screenshots of connecting the linked server with SQL Server

To solve "Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server" problem

Connect MS Access to SQL server 2008 via linked server

Then use below query to get more information about the linked server

select * from openquery("owner_pc\sqlserver2008",'select * from Testing.dbo.test')

Answer 4

This was driving me round the bend. And the fact you can't edit a Linked server after you entered it is just bananas.

Anyway, just leave most of the boxes empty on the linked server dialogue! Use "SQL Server Native Client" like @abatishchev mentioned then..

Check Kamran's article here for the settings. Only Datasource was filled in for me as IP hostname, after I'd give the server a friendly name in "Linked server", and on the second tab didn't need to bother with any logins just radio button the bottom one and used the SA login on the remote box.

Answer 5

In the general page of New Linked Server, type the sql-server instance name in the [Linked Server] box. Click on the [SQL Server] check box when selecting [Server Type]. Type remote username and password atfer selecting [Be made using this security context] of Security tab.

Select top 10 * from [LINKEDSERVERNAME].[DATABASENAME].[SCHEMANAME].[TABLENAME]

Best of luck.

for more details information http://msdn.microsoft.com/en-us/library/ff772782.aspx

Answer 6

A possible cause, if you have clustered service , and has recently been balanced , it is likely that a connection has been blocked pointing to old resource.

Try moving again resource to another node and reopening the Management studio, try again linked server connection.