Issue with Linked server in sql server 2008 R2

Question

Post reply

Issue with Linked server in sql server 2008 R2

Radh

Mr or Mrs. 500

Points: 545
More actions
June 7, 2012 at 9:05 am

#262469

HI,
I am facing a problem with Linked server in sql server 2008 R2. we have serverA and serverB and we created linked server for this. main problem is I am executing one sql query from serverA for fetching the data from serverB
this will execute some times fine, but sometimes it will shown the below error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
could you please help me the above issue, why it's executes sometimes successfully?
why it's throws the error message sometimes?
NOTE: most of the times the error was happening user try to fetch the data from front end page(UI PAGE).
Thanks for responses,
Radh

sestell1 SSChampion Points: 10230 More actions · Answer 1

Is the server link using pass-through authentication?

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 2

I believe this is issue with NTLM security.

When you try to connect linked server from server B (logged into server B and using SSMS) it should work.

When you are logged into other than server B and using SSMS connectiong to server B, you will receive this error when try to access server A

please refer this for more details[/url]

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

If it succeeds when you are using a SQL login and fails when you use an AD login then it sounds like you are having a "double hop" problem. I wrote up a solution here:

http://www.sqlservercentral.com/articles/Double+Hop/76743/

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

DBA328 Hall of Fame Points: 3952 More actions · Answer 4

You may need to setspn for these two instances.

http://msdn.microsoft.com/en-us/library/ms191153.aspx

sestell1 SSChampion Points: 10230 More actions · Answer 5

If you are using pass-through authentication with AD accounts and find it occasionally gives you an authentication error, check the time on your servers. The time on both SQL Server machines and your Active Directory server need to be within 5 seconds of each other.