在SQL Server 2008 R2中链接服务器

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.

任何人都可以指导我在SQL Server 2008 R2中将服务器链接到另一台服务器吗？我在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)

执行Transact-SQL语句或批处理时发生异常。（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)

链接服务器“CDSPM1”的OLE DB提供程序“SQLNCLI”报告错误。验证失败。无法为链接服务器“CDSPM1”初始化OLE DB提供程序“SQLNCLI”的数据源对象。链接服务器“CDSPM1”的OLE DB提供程序“SQLNCLI”返回消息“无效的授权规范”。（Microsoft SQL Server，错误：7399）

6 个解决方案

#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"

使用“服务器类型”作为“Sql Server”创建链接服务器进行测试。然后进入“安全性”并设置用户映射。作为测试，在远程系统上创建SQL登录，并在“使用此安全上下文创建”中指定

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

确保您可以首先PING“链接服务器”名称。 HTH

#2

Use drop down and choose

使用下拉菜单并选择

SQL Server Native Client

instead of

代替

SQL Server

#3

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

使用以下链接查看将链接服务器与SQL Server连接的屏幕截图

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

解决“无法初始化OLE DB提供程序的数据源对象”SQLNCLI10“链接服务器”问题

Connect MS Access to SQL server 2008 via linked server

通过链接服务器将MS Access连接到SQL Server 2008

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

然后使用以下查询获取有关链接服务器的更多信息

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

select * from openquery（“owner_pc \ sqlserver2008”，'select * from Testing.dbo.test'）

#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..

无论如何，只需将链接服务器对话框上的大部分框留空！使用像@abatishchev那样提到的“SQL Server Native Client”..

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.

查看Kamran的文章，了解设置。在我给“服务器”中的“链接服务器”中的友好名称后，只有数据源作为IP主机名填写，并且在第二个选项卡上不需要打扰任何登录只需单击底部的单选按钮并使用SA登录远程控制台。

#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.

在“新建链接服务器”的常规页面中，在[链接服务器]框中键入sql-server实例名称。选择[服务器类型]时，单击[SQL Server]复选框。键入远程用户名和密码，然后选择安全选项卡的[使用此安全上下文进行]。

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

Best of luck.

祝你好运。

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

有关详细信息，请访问http://msdn.microsoft.com/en-us/library/ff772782.aspx

#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.

尝试再次将资源移动到另一个节点并重新打开Management studio，再次尝试链接服务器连接。

#1