Login Failed for User NT Authority\Anonymous Logon

  • Hi guys,

    I have different environment with the same replication setup. I am trying to create a stored procedure to check the replicated tables of the publisher matches with subscriber( row count too). This stored procedure is created in a seperate server. We use only windows authentication. I have created the linked server ( previously created) for all the publisher , distributor and subscriber.Now when executing this stored procedure i get the msg at in my subject

    Login Failed for user NT Authority\Anonymous Logon

    what might be the problem? How can I resolve this issue?

    Thanks

  • srawant (10/27/2008)


    Hi guys,

    I have different environment with the same replication setup. I am trying to create a stored procedure to check the replicated tables of the publisher matches with subscriber( row count too). This stored procedure is created in a seperate server. We use only windows authentication. I have created the linked server ( previously created) for all the publisher , distributor and subscriber.Now when executing this stored procedure i get the msg at in my subject

    Login Failed for user NT Authority\Anonymous Logon

    what might be the problem? How can I resolve this issue?

    Thanks

    Validate the SPN's (Service Principal Names) for both instances and enable delegation. These articles should point you in the right direction 🙂

    http://technet.microsoft.com/en-us/library/bb742516.aspx

    http://support.microsoft.com/kb/319723

  • When using windows authentication and querying linked servers over 3 or more "tiers" - you need to enable a setting in AD called something like "trust SQL Server for delegation" or something like that. (can't remember)

    ie.

    you are working on your laptop and are connected to SERVERA via SSMS. You execute QueryA and

    in turn Query A is Querying ServerB via a linked server

    Your windows authentication does not delegate down to ServerB so SQL do not know if you have a valid login or not - and you get the error

    to test this: connect remotely to ServerA via Windows Remote desktop then execute the same query. Now it will work...

    I only know of the AD setting, but maybe someone else knows of a another solution?



    What's this "backup strategy" everyone is on about?

  • Aahhh, and Tommy just gave another solution 🙂



    What's this "backup strategy" everyone is on about?

  • Exactly TVR.. Its a double hop issue.. Thanks for the reply..

  • For anyone else coming across this issue, here's a brilliant article explaining this...

    http://www.sqlservercentral.com/articles/Security/65169/



    What's this "backup strategy" everyone is on about?

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

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