Sql Server Linked Server Issue \"Login failed for

问题:

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

回答1:

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;