SQL Server链接服务器问题“用户'NT AUTHORITY \ ANONYMOUS LOGON'的登录失败 [英] Sql Server Linked Server Issue "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

问题描述

我在链接服务器和引用链接服务器的服务器上都有一个域用户. 将同一个域用户添加到Web请求的池标识(asp.net). 发生了什么，该应用程序已成功访问本地数据库，并且所有数据都正常.但是，当应用程序尝试访问正在调用链接服务器的存储过程时，它将显示以下错误 用户'NT AUTHORITY \ ANONYMOUS LOGON登录失败"

但是更烦人的是，如果有人登录到SSMS Web应用程序可以获取此数据，但是如果他从服务器注销，则一段时间后此错误就会开始出现.

当我们在两台服务器(链接的服务器和使用链接的服务器的一台)和Windows 2008上都装有sql server 2008 R2时，效果很好.

但是当我们拥有Windows 2003 Server和sql 2005(使用此数据库中的db链接)以及Windows Server 2008上的sql server 2008时，会失败

解决方案

这是由于SQL Server出现"2-跳跃"问题. 我可以通过两种方式解决此问题: 1.仍在使用链接服务器:在远程服务器上，创建具有SQL Server身份验证的用户并提供所有需要的权限.然后打开链接的服务器属性，并为您的帐户映射到您刚刚创建的用户，如下所示:

之后，您将可以运行脚本

2. 使用openrowset:

   选择a.* 从OPENROWSET('SQLNCLI'，'Server =; Trusted_Connection = yes;'， '选择*来自.dbo.')AS a;

I have a domain user on both linked server and server which is referring the linked server. Same domain user is added to the pool identity of web supplication ( asp.net). What is happening, that application is successfully accessing the local DB and all data is coming fine. But when application try to access the stored procedure which is calling linked server it shows the following error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON"

But more annoying is that it does not fail always, if someone is logged into SSMS web application can fetch this data but if he log off from the server ,after some time this error start appearing.

It works fine when we have sql server 2008 R2 on both servers (linked and one using the linked server) and windows 2008.

But fails when we have windows 2003 server and sql 2005( db from this is used a linked) , and sql server 2008 on windows server 2008

解决方案

This is because of "2-Hops" issue with SQL server. I was able to fix this issue in the two ways: 1. Still using Linked Servers: On the remote server create user with SQL Server authentication and give all needed permissions. Then open linked server properties and mapping for your account to the user that you just created, like this:

After that you will be able to run your scripts

2. Using openrowset:

   SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=;Trusted_Connection=yes;', 'SELECT * from .dbo.') AS a;

这篇关于SQL Server链接服务器问题“用户'NT AUTHORITY \ ANONYMOUS LOGON'的登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！