使用非管理员帐户时，SQL 链接服务器返回错误“不存在登录映射”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32084453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Linked Server returns error "no login-mapping exists" when non-admin account is used
提问by Tony
I have a local SQL Server 2008R2. I have configured Linked Server to a remote database.
我有一个本地 SQL Server 2008R2。我已将链接服务器配置为远程数据库。
The Linked Server works great when I login to the local server using a SQL-login account with sysadminserver role. I can query against the remote server, so I know the Linked Server setting is correct. However, I would get the error below if I use an account that does not have the sysadminserver role.
当我使用具有sysadmin服务器角色的 SQL 登录帐户登录到本地服务器时，链接服务器运行良好。我可以查询远程服务器，所以我知道链接服务器设置是正确的。但是，如果我使用没有sysadmin服务器角色的帐户，则会出现以下错误。
Msg 7416, Level 16, State 2, Line 2
Access to the remote server is denied because no login-mapping exists.
For both local and remote servers, SQL login is used (Windows authentication is not used)
对于本地和远程服务器，都使用 SQL 登录（不使用 Windows 身份验证）
What kind of security I need to configure for a regular SQL-login account to use Linked Server?
我需要为常规 SQL 登录帐户配置什么样的安全性才能使用链接服务器？
回答by Tony
According to this blog, I have to specify User IDin the provider string if non-sysadmin accounts are used. Here is an example.
根据此博客，User ID如果使用非系统管理员帐户，我必须在提供程序字符串中指定。这是一个例子。
EXEC master.dbo.sp_addlinkedserver
@server = N'MyLinkServerName',
@provider = N'SQLNCLI',
@srvproduct = 'SQLNCLI',
@provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'
This exactly matches what I have encountered and it solves my problem.
这完全符合我遇到的情况，它解决了我的问题。
UPDATE: See @Anton's and @Wouter's answer for alternative solution.
更新：有关替代解决方案，请参阅@Anton 和@Wouter 的回答。
回答by Anton R
As alternative solution you can use the parameter @datasrc instead of @provstr. @dataSrc works without setting the User ID
作为替代解决方案，您可以使用参数@datasrc 而不是@provstr。@dataSrc 无需设置用户 ID 即可工作
Sample:
样本：
EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @datasrc=N'serverName\InstanceName'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'
I've added a comment here, too, but it's not visible (don't know why).
我也在这里添加了评论，但它不可见（不知道为什么）。
回答by Wouter
After playing around with this, i found that you can avoid having to use the User IDproperty altogether, by using @datasrcinstead of @provstr. This is very poorly documented, but the example below works for me:
在玩弄这个之后，我发现您可以User ID通过使用@datasrc代替@provstr. 这是非常糟糕的记录，但下面的例子对我有用：
EXEC master.dbo.sp_addlinkedserver
@server = 'SOME_NAME',
@srvproduct='', -- needs to be explicitly empty, default is NULL and is not allowed
@datasrc='some_server.com\SOME_INSTANCE',
@provider='SQLNCLI';
-- Set up the Linked server to pass along login credentials
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='SOME_NAME',
@useself='true', -- Optional. This is the default
@locallogin=NULL; -- Optional. This is the default
Using this method you can reuse the same Linked server for all of your users. The currently accepted answer has the huge drawback that you need to set up a separate linked server for each user.
使用此方法，您可以为所有用户重复使用相同的链接服务器。当前接受的答案有一个巨大的缺点，即您需要为每个用户设置单独的链接服务器。
