如何配置链接服务器之间的身份验证？
我正在尝试测试一个概念证明，即我可以在两个链接的 SQL Server 之间运行分布式事务，使用 sp_addlinkedserver 进行链接 - 它们的名称是 Server1 和 Server2，两者都在默认实例下运行。 每个服务器分别保存一个数据库（源数据库和目标数据库），目标数据库保存一个名为“输出”的表，即
Server1.Source
Server2.Destination.Output
OUTPUT 表具有以下结构：
OUT_PKEY int identity(1,1) primary key,
OUT_TEXT nvarchar(255)
在 Server1 中，我调用了 sp_addlinkedserver 'Server2' 来链接两个数据库，我尝试运行以下查询来测试该链接是否确实有效：
Select *
From Server2.Destination.dbo.Output
我返回了以下异常：
由于不存在登录映射，因此拒绝访问远程服务器。
很公平，所以从 Server1 中，我运行 sp_addlinkedsrvlogin 'Server2' ，根据文档，它应该获取远程运行查询的人员的用户凭据（即从 Server1 中）并将这些凭据应用到 Server2 。 这意味着由于我使用 Windows 身份验证连接到 Server1，因此这应该意味着我的 Windows 凭据也适用于 Server2。
现在异常消息变为：
用户“NT AUTHORITY\ANONYMOUS LOGON”登录失败。
在谷歌上搜索了这个异常后，我没有发现任何有用的东西可以为我指明正确的方向。 我缺少什么？ 我希望[如果登录完全失败]异常会引用我的 Windows 凭据，而不是匿名登录凭据。
看起来一旦我让链接本身正常工作，分布式事务本身应该是一件相当简单的事情 - 文档暗示我只需要确保 DTC 服务在 Server1 上运行，并且任何查询在将进行事务处理的 Server1 上运行跨链接：
- 在初始化分布式事务之前包含SET XACT_ABORT ON，
- 我使用BEGIN DISTRIBUTED TRANSACTION而不是BEGIN TRANSACTION
- 如果我想引用Server2 上 SQL Server 的非默认实例，我将查询中名称 Server2 的任何实例替换为 [Server2\InstanceName]
我的问题是：
- 如何获取解决了这个登录问题吗？ sp_addlinkedsrvlogin 存储过程本身似乎并不能解决问题。
- 运行分布式事务真的像文档所暗示的那么简单吗？
TIA
I am trying to test a proof of concept that I can run a distributed transaction across two linked SQL Servers, linked using sp_addlinkedserver - their names are Server1 and Server2, both running under default instances. Each server holds a single database, Source and Destination respectively and the destination database holds a single table called Output, i.e.
Server1.Source
Server2.Destination.Output
The OUTPUT table has the following structure:
OUT_PKEY int identity(1,1) primary key,
OUT_TEXT nvarchar(255)
From Server1 I have called sp_addlinkedserver 'Server2' to link the two databases and I've attempted to run the following query to test that the link does indeed work:
Select *
From Server2.Destination.dbo.Output
I am returned the following exception:
Access to the remote server is denied because no login-mapping exists.
Fair enough, so from Server1, I run sp_addlinkedsrvlogin 'Server2' which according to the documentation says that it should take the user credentials of whomever runs the query remotely (i.e. from Server1) and apply those credentials to Server2. This implies that since I am connected to Server1 using Windows Authentication, this should mean that my Windows Credentials are applied to Server2 also.
Now the exception message changes to:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Having Googled this exception, I came up with nothing useful that pointed me in the right direction. What am I missing? I would expect [should the login fail at all] the exception to reference my Windows Credentials, not the anonymous logon credentials.
It looks like once I get the link itself working, the distributed transactions themselves should be a fairly simple affair - the documentation implies that I just need to ensure that the DTC Service is running on Server1 and that any queries run on Server1 that will be transacted across the link:
- Include SET XACT_ABORT ON prior to initializing my distributed transaction
- I use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION
- If I wish to reference a non-default instance of SQL Server on Server2, I replace any instances of the name Server2 in my query with [Server2\InstanceName]
My questions are these:
- how do I get past this login issue? The sp_addlinkedsrvlogin stored procedure alone doesn't seem to be doing the trick.
- Is it indeed as simple as that to run the distributed transaction as the documentation implies?
TIA
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您位于域中，则设置应为“使用登录名的当前安全上下文进行”，但还有一个步骤 - 您需要向事务中涉及的每台服务器授予 SPN。
假设您以域用户身份在两台服务器上运行 SQL 服务（您需要这样做才能完成这项工作 - LocalSystem 不会这样做），以下是您需要的说明：
http://technet.microsoft.com/en-us/library/bb735885.aspx
请记住，用户将需要两个服务器的 SPN，但客户端不需要 - 例如，如果您要从客户端访问 -> 服务器1 -> server2，SQL 服务帐户将需要 server1 和 server2 的 SPN。
如果您感到困惑（这是一个令人困惑的过程），请发表评论，我将澄清说明。
If you're on a domain, then the setting should be "Be made using the login's current security context", but there's one more step - you need to grant an SPN to each of the servers involved in the transaction.
Assuming you're running the SQL Services on both servers as a domain user (which you'll need to in order to make this work - LocalSystem won't do it), here are the instructions you'll need:
http://technet.microsoft.com/en-us/library/bb735885.aspx
Remember that the user will need an SPN for both servers, but not the client - for example, if you are going from client -> server1 -> server2, the SQL Service account will need an SPN for both server1 and server2.
If you're confused (it's a confusing process), post a comment and I'll clarify the instructions.
假设这些服务器都位于同一域中 - 您是否启用了受信任委派以允许您的服务器将凭据传递到目标服务器？ 您将提取服务器的 Active Directory 对象，转到委派选项卡并选择“仅信任此计算机委派指定的服务”，然后输入允许服务器将凭据传递到的 SQL Server 详细信息：
服务类型 = MSSQLSvc
用户/计算机 = YourTargetServer.Your.Domain
端口 = 1433
遗憾的是，链接服务器的许多此类身份验证问题需要重新启动才能完全生效（因此，如果这些是生产服务器，则很难在白天进行故障排除）。
关于分布式事务 - 如果您最终获得链接服务器连接并正确运行，那么分布式事务工作得很好。 尽管一旦你让它工作，你可能会遇到的下一件事是发现巨大的缺陷，即你不能使用任何形式的 SCOPE_IDENTITY()、@@IDENTITY 等来检索主键将某些内容插入链接数据库后。 但这是另一个问题，还有它自己有趣的解决方法......
Assuming these servers are both on the same domain - have you enabled trusted delegation to allow your server to pass the credentials to the targeted server? You would pull up the Active Directory object for the server and go to the Delegation tab and select "Trust this computer for delegation to specified services only" and then enter the SQL Server details that the server is allowed to pass credentials to:
Service Type = MSSQLSvc
User/Computer = YourTargetServer.Your.Domain
Port = 1433
Sadly, a lot of these types of authentication issues with linked servers require a reboot to fully take effect (so if these are production servers it is hard to troubleshoot during the day).
In regards to distributed transactions - if you eventually get the linked server connection up and running correctly then distributed transactions work great. Although the next thing you'll probably run into once you get it working is finding the huge flaw that you can't use any form of SCOPE_IDENTITY(), @@IDENTITY, etc. to retrieve primary keys after inserting something into a linked database. But that's another issue with its own fun workarounds...