Linked Server Setup Error...Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

  • logicinside22

    SSCrazy Eights

    Points: 9617

    Hi guys

    I am trying to setup linked server connection and i am getting error message shown below.

    Can anyone help me out to identify the root cause please?

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

    Thanks.

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    How are you configuring the security on the linked server object?

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    I am Configuring with my Login as i am sysadmin on Both server...

    Local Login - domain\MSSQL

    Impersonate - yes

    when i am selecting any of the option from below 4 options i am getting error.

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    So, your login is domain\MSSQL?

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    Yes and its windows Authentication...

    My login : domain_name\logicinside

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    I'm confused... What are you using to log into SQL Server and what are you using for the linked server creds? Also, see this: http://technet.microsoft.com/en-us/library/ms189580%28v=sql.105%29.aspx You need these requirements met for impersonation.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    Hi

    I am using xyz\logicinside ( i can't write domain name because of privacy) using same credential for linked server creds. The same name shows in drop down list Local Login in Security tab in Linked Server.

    and it is windows authenticated.

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    1. read the link I posted in the previous post.

    2. I don't assume anything, so both machines are on xyz domain and your user is sysadmin on both instances?

    My guess is that there are some problems with delegation, so focus on the article I posted.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    If you remote into serverA and use SSMS there, do you get the same error? My guess is no.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    No .. i can connect easily..

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    So the error is the "double hop." I don't think you can use the impersonate for remote connections to the server that is housing the linked server. Best to give it a local user on the second server.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    So you mean to user with SQL authentication?

    Aim to inspire rather than to teach.
    SQL Server DBA

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    Yes.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • SpringTownDBA

    SSCertifiable

    Points: 6756

    SQLKnowItAll (7/30/2012)


    So the error is the "double hop." I don't think you can use the impersonate for remote connections to the server that is housing the linked server. Best to give it a local user on the second server.

    It is definitely easier to use a sql login for the linked server. However, it's not terribly difficult to troubleshoot windows authentication/delegation/kerberos issues.

    The link posted by SQLKnowItAll hits the high points, but misses some stumbling blocks.

    Scenario:

    Connect as myDomain\Usr to SqlServer1.myDomain.local (running as myDomain\SqlSrv1Svc) then access a linked server using windows authentication to SqlServer2.myDomain.local (running as myDomain\SqlSrv2Svc).

    1. Verify that you can authenticate to SqlServer1 using Kerberos instead of NTLM.

    SELECT auth_scheme FROM sys.dm_exec_connections

    WHERE session_id = @@spid

    If it's showing NTLM, then check the following: http://msdn.microsoft.com/en-us/library/ms191153.aspx

    Also, this seems to be a good reference as well: http://technet.microsoft.com/library/ee191523.aspx

    One additional gotcha is that you have to use DNS "A" records, not CNAMEs for the Servers. (Kerberos doesn't work with CNAMEs)

    2. Verify that you can authenticate directly to SqlServer2 using Kerberos instead of NTLM.

    3. Verify that myDomain\SqlSrv1Svc is trusted for delegation to MSSQLSvc:SqlServer2.myDomain.local

    Piece of Cake!

Viewing 14 posts - 1 through 14 (of 14 total)

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