locked
Linked server logon failed RRS feed

  • Question

  • Hi,

    I’m having some difficulty setting up access to a linked server for one of our SQL admins. I can add the link but when the admin tries to run a task from her machine she gets the error:  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    The config is as below, is there something obviously wrong here?

    Server1 SQL server service running as Domain service account

    Server2 SQL server service running as Local system

    Server2 added as a link on Server1. Security set to Server1 Domain1 service account and impersonate ticked. RPC in and out set to true.

    Both servers AD computer accounts are set to “Trust this computer for delegation to any service (Kerberos only)”

    Server2 SPNs (checked in ADSI Edit)

    HOST/Server2

    HOST/Server2.domain

    MSSQLSvc/Server2.domain

    MSSQLSvc/Server2.domain:1433

    RestrictedKrbHost/Server2

    RestrictedKrbHost/Server2.domain

    TERMSRV/Server2

    TERMSRV/Server2.domain

    WSMAN/Server2

    WSMAN/Server2.domain

    Server1 SPNs (checked in ADSI Edit)

    HOST/Server1

    HOST/Server1.domain

    RestrictedKrbHost/Server1

    RestrictedKrbHost/Server1.domain

    TERMSRV/Server1

    TERMSRV/Server1.domain

    WSMAN/Server1

    WSMAN/Server1.domain

    The SQL admin user has sys admin role access to both servers.

    Thanks in advance

    Friday, November 1, 2013 11:30 AM

Answers

All replies

  • I understand from your description that you have used a point-and-click dialog in SSMS to add the linked server. I recommend that you use direct T-SQL commands (or in this case system procedures), as a UI introduces the risk for bugs, due to bad dialog handling or poor understanding of SQL Server. And, yes, I've seen this with SSMS.

    Unless, you need an explicit login mapping, this SELECT:

    SELECT s.name, ll.*
    FROM     sys.servers s
    JOIN     sys.linked_logins ll ON s.server_id = ll.server_id
    WHERE    s.name = 'Server2'

    should return exactly one line with local_principal_id = 0 and uses_self_credential = 1. If not, get rid of the other rows with sp_droplinksrvlogin.

    This may or may not be the issue. The other possibility is that there is a problem with trust in a double-hop scenario.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, November 4, 2013 8:09 AM
    Friday, November 1, 2013 2:58 PM
  • Hi, thanks for the reply.

    I'll remove the link and try adding via the command as suggested. With the command line how do you specify the local server login to remote server login mapping?

    Monday, November 4, 2013 9:01 AM
  • Hi Wedders,

    When you setting up a linked server for a remote SQL Server instance, usually, we can create the linked server via GUI or you can use “sp_addlinkedserver” system stored procedure to add a linked server. There are two different ways a local login can be mapped to a remote login. The first method is to impersonate, and the second is to associate the local login with a remote login and password. The impersonate option takes the local Windows login and uses it to connect to the linked server.

    For more information about setting up a linked server for a remote SQL Server instance, you can review the following article.
    http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

    There is a similar issue about this loggon failed, you can refer to the following post.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e75b7d7-641f-4519-b5ea-9567985a2cce/linked-server-error-login-failed-for-user-nt-authorityanonymous-logon

    Thanks,
    Sofiya Li


    Your Name
    TechNet Community Support

    • Marked as answer by Sofiya Li Friday, November 15, 2013 7:41 AM
    Monday, November 11, 2013 2:01 AM
  • Thanks for the reply. I've changed server1 to use a local service account rather than the domain account and added the linked server back in with the command line. So far everything seems to be ok.
    • Marked as answer by Sofiya Li Friday, November 15, 2013 7:40 AM
    Monday, November 11, 2013 2:32 PM