添加链接的SQL Server时遇到问题 [英] Having trouble adding a linked SQL server

查看:87
本文介绍了添加链接的SQL Server时遇到问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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.

我正在尝试这样的事情:

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

这就是我得到的:



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

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

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.

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

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?

推荐答案

它确实适用于sql身份验证.实际上,它更容易,因为如果使用SQL身份验证,则不必设置Kerberos.

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

关于您的错误消息,令人困惑的是,您的地址是一个IP地址:123.45.678.90

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

您的错误消息是有关命名管道的.这可能是个线索吗?

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

您不必通过TSQL的IP地址来引用该服务器.如果您在本地服务器上添加别名,指向远程服务器,则可以提供更多信息明智的名字.通过IP地址引用它显然不令人满意.

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.

这篇关于添加链接的SQL Server时遇到问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆