Post reply

Issue with Linked server in sql server 2008 R2

  • 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

  • June 7, 2012 at 9:36 am

    #1498257

    Is the server link using pass-through authentication?

  • June 7, 2012 at 2:32 pm

    #1498415

    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]

  • June 7, 2012 at 4:08 pm

    #1498443

    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://qa.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]

  • June 8, 2012 at 12:06 am

    #1498497

    You may need to setspn for these two instances.

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

  • June 8, 2012 at 7:23 am

    #1498699

    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.

    • Viewing 6 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply