I am facing weired problem whenever trying to fire query over linked server.
I have Sql 2k8 server enterprise edition which has linked server pointing to Sql 2k8 R2 server enterprise edition.
i have windows authenticated login created on both db and used the same in linked server access rights/credential.
Whenever i m firing select query , it shows a message that "Login Failed for NT AUTHORITYAnonymous logon."
I tried to search a lot on internet but nothing could resolve my issue. This is i am facing on production.
I would be very thankful if you have any resolution to this problem. Will wait for your reply. Thanks.
Are both the servers running under the same service account? if not then they should be.
answered May 02 '12 at 02:25 PM
It sounds like you need to configure your SQL Server instances for Kerberos authentication. When trying to go over more than one hop, which you are (client > 1st SQL instance > 2nd SQL instance via linked connection), Kerberos authentication is the only protocol that SQL Server can use to do this action. NTLM cannot pass your credentials.
Here is another link to look over as well.