查询链接服务器时登录失败 [英] Login failed when querying linked server

问题描述

我正在尝试在SQL Server中创建链接服务器:

I am trying to create a linked server in SQL Server:

--Create the link to server "uranium"
EXEC master.dbo.sp_addlinkedserver 
      @server = N'uranium', 
      @srvproduct=N'', 
      @provider=N'SQLNCLI'

--Add the catch-all login with SQL Server authentication
EXEC master.dbo.sp_addlinkedsrvlogin 
      @rmtsrvname=N'uranium',
      @useself=N'False',
      @locallogin=NULL,
      @rmtuser=N'BatteryStaple',
      @rmtpassword='Horsecorrect'

它创建得很好.但是任何尝试查询链接服务器的尝试，例如:

And it creates fine. But any attempt to query the linked server, e.g.:

SELECT * FROM uranium.Periodic.dbo.Users

产生

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'BatteryStaple'.

除了我知道凭据是正确的:

Except i know the credentials are correct:

• 登录: BatteryStaple
• 密码: Horsecorrect

• Login: BatteryStaple
• Password: Horsecorrect

因为当我直接使用SQL Server Management Studio或任何其他能够连接到数据库的技术进行连接时，我可以登录.

because i can login when i connect directly using SQL Server Management Studio, or any other technology that is able to connect to a database.

• 登录链接服务器失败 (他忘记了呼叫)
• 我为什么创建此链接服务器时出现登录失败"的消息? (他正在尝试使用集成身份验证)
• MSDN博客: (他正在尝试使集成身份验证工作)

• Login Failed for linked server (he forgot to call sp_addlinkedsrvlogin)
• Why am I getting a "login failed" when creating this linked server? (he's trying to use integrated authentication)
• MSDN Blogs: SQL Linked Server Query failed with "Login failed for user …" (he's trying to make integrated authentication work)

注意:安装新的SQL Server 2014.每个现有的SQL 2000、2005、2008、2008 R2都可以与其uranium链接服务器进行通信.我敢肯定这与Microsoft令人沮丧的 broken相关默认情况下政策.

Note: New SQL Server 2014 install. Every existing SQL 2000, 2005, 2008, 2008 R2 can communicate to their uranium linked server. I'm certain it is related to Microsoft's frustrating broken by default policy.

推荐答案

问题是SQL Server Management Studio界面使用OLEDB提供程序创建链接服务器:

The issue is that the SQL Server Management Studio interface creates the linked server using the OLEDB Provider:

这等效于原始的T-SQL:

This is equivalent to the original T-SQL:

--Create the link to server "uranium"
EXEC master.dbo.sp_addlinkedserver 
      @server = N'uranium', 
      @srvproduct=N'', @provider=N'SQLNCLI'

解决方法是将链接服务器创建为 SQL Server :

The fix is to create the linked server as SQL Server:

--Create the link to SQL Server "uranium"
EXEC master.dbo.sp_addlinkedserver 
      @server = N'uranium', 
      @srvproduct=N'SQL Server'

没关系.可能是Microsoft SQL Server 2014 12.0.4213.0中的回归.可能会固定在Service Pack中-如果有的话.

Shouldn't matter. Probably a regression in Microsoft SQL Server 2014 12.0.4213.0. Might be fixed in a service pack - if there is one.

但是确实存在；解决.

这篇关于查询链接服务器时登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！