添加链接的SQL服务器有困难

I'm trying to pull in data from a remote SQL Server. I can access the remote server using SQL authentication; I haven't had any luck using the same credentials with sp_addlinkedserver.

我正在尝试从远程SQL服务器中提取数据。我可以使用SQL身份验证访问远程服务器;我还没有在sp_addlinkedserver中使用相同的凭证。

I'm trying something like this:

我正在尝试这样的东西:

Exec sp_dropserver 'Remote', 'droplogins'
go

EXEC sp_addlinkedserver   
   @server='Remote', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='Remote',
    @rmtuser='User',
    @rmtpassword='Secret'

Select Top 10 * from Remote.DatabaseName.dbo.TableName

Here's what I get:

这就是我得到:


OLE DB provider "SQLNCLI" for linked server "Remote" returned message 
"Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "Remote" 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 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Again, I can access the server directly (in SQL Management Studio) using these exact credentials, so it's not a problem with my network or credentials.

同样，我可以使用这些确切的凭据直接访问服务器(在SQL Management Studio中)，因此我的网络或凭据没有问题。

Most of the examples I've seen online seem to involve Windows domain accounts, as opposed to SQL security logins. Does this not work with SQL authentication?

我在网上看到的大多数示例似乎都涉及Windows域帐户，而不是SQL安全登录。这对SQL身份验证不起作用吗?

6 个解决方案

#1

It does work with sql authentication. In fact its easier, as you don't have to set up Kerberos if you use SQL authentication.

它使用sql身份验证。事实上，它更简单，因为如果使用SQL身份验证，您不必设置Kerberos。

One thing is confusing about your error messages, your address is an IP address : 123.45.678.90

有一件事令您的错误消息感到困惑，您的地址是一个IP地址:123.45.678.90

your error message is talking about Named Pipes. Could this be a clue?

错误消息是指命名管道。这可能是一条线索吗?

You don't have to reference the server in TSQL by it's IP address. If you add an alias on the local server, pointing to the remote server, you can give a more sensible name. Refering to it by IP address is obviously unsatisfactory.

您不必在TSQL中引用服务器的IP地址。如果您在本地服务器上添加别名，指向远程服务器，您可以给出一个更合理的名称。IP地址的引用显然是不令人满意的。

#2

To solve the issue above I amended the Linked Server definition to specify TCP specifically:

为了解决上述问题，我修改了链接服务器定义，具体指定TCP:

EXEC sp_addlinkedserver   
    @server='TEST_LINK', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='tcp:0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='TEST_LINK',
    @rmtuser='user',
    @rmtpassword='secret'

This worked for me. This method also allowed me to specify a non-standard port for the remote server:

这为我工作。该方法还允许我为远程服务器指定一个非标准端口:

EXEC sp_addlinkedserver   
    @server='TEST_LINK', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='tcp:0.0.0.0,1111'

I hope this helps

我希望这有助于

#3

Figured it out, thanks to a line of instructions in the SMS GUI wizard for adding a linked servers: "If SQL Server is selected, then the Linked Server name is also the network name of the server."

由于在SMS GUI向导中添加了连接服务器的一条指令，所以找到了它:“如果选择了SQL服务器，那么链接服务器名也是服务器的网络名称。”

I thought the name of the linked server was just an arbitrary alias.

我认为链接服务器的名称只是一个任意的别名。

This works like a charm - it's a shame that I have to type the IP (in brackets) every time I want to use this linked server, but there you have it.

这非常有用——每次我想要使用这个链接服务器时，都必须在括号中输入IP，这是一种遗憾。

Exec sp_dropserver '0.0.0.0', 'droplogins'
go

EXEC sp_addlinkedserver   
    @server='0.0.0.0', 
    @srvproduct='',
    @provider='SQLNCLI', 
    @datasrc='0.0.0.0'

EXEC sp_addlinkedsrvlogin
    @useself='FALSE',
    @rmtsrvname='0.0.0.0',
    @rmtuser='user',
    @rmtpassword='secret'
go

Select Top 10 * from [0.0.0.0].DatabaseName.dbo.TableName

#4

If it's failing on Named Pipes when you're specifying an IP address, try prefixing the IP address with "tcp:" and see if it helps set it on the right path.

如果指定IP地址时在命名管道上失败，请尝试在IP地址前面加上“tcp:”，看看它是否有助于将其设置在正确的路径上。

#5

sp_configure 'Ad Hoc Distributed Queries',1

sp_configure 'Ad Hoc Distributed Queries'，1

下一个

reconfigure with override

重新配置与覆盖

have checked the SQL Surface area configuraton-->Databaseengine-->Remoteconnections-->Loacal and remoteconnections-->shouild be Using both TCP/IP and named pipes option ticked (if not choose that option and restart)

检查了SQL表面积配置——>数据库引擎——>Remoteconnections——>Loacal和Remoteconnections——>应该同时使用TCP/IP和命名管道选项(如果不选择该选项并重新启动)

this has to resolve the problem OLE DB provider "SQLNCLI" for linked server "Remote" 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.".

这必须解决连接服务器“远程”返回消息“在建立到服务器的连接时发生错误”的问题OLE DB提供程序“SQLNCLI”。当连接到SQL Server 2005时，这种失败可能是由于在默认设置下SQL Server不允许远程连接。

#6

Sometimes the protocols have been enabled and the surface area configuration allows remote connections, but the SQL Server Browser service hasn't been restarted since the configuration was set. This means that the configuration is not active.

有时协议已经启用，表面积配置允许远程连接，但是自从设置好配置后，SQL Server浏览器服务还没有重新启动，这意味着配置没有活动。

Try restarting SQL Server Browser as a troubleshooting step if your configuration settings all appear to be correct.

如果您的配置设置都是正确的，请尝试重新启动SQL Server浏览器作为故障排除步骤。

Mike

迈克

#1