Share via

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON', SPN working

Sam Garth 26 Reputation points Microsoft Employee
2021-01-15T11:58:38.287+00:00

Hello, I have set up two fresh SQL Servers and have set up SPN, both servers report the following...

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME ] for the SQL Server service.

I have set up a linked server between the pair of them and then from my desktop SSMS tried to test the connection and get the error.

Has anyone had this before?

Thanks

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,865 questions
0 comments
Accepted answer
  1. AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor
    2021-01-18T06:36:27.327+00:00

    Hi @Anonymous ,
    Is there any update on this case?
    Please make sure SQL Server service account was trusted for delegation in AD.
    You can go to domain controller -> open active directory users and computers -> users -> right-click the SQL Server Service account in users folder -> Properties.
    Then go to delegation tab in the Properties dialog box, ensure that "Trust this user for delegation to any service (Kerberos only)" or "Trust this user for delegation to specified services (Kerberos only) – Use Kerberos only "is selected. If you choose the " Trust this user for delegation to specified services (Kerberos only)", please add the SQL Server service. ( please do the same for the delegation tab in the Properties of server's computer object in active directory users and computers.)

    57453-01.jpg
    Then go to the account tab in properties and ensure that the "account is sensitive and cannot be delegated" option is not selected.

    57487-02.jpg

    Please refer to this article which might help.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,251 Reputation points
    2021-01-15T13:29:10.9+00:00
    1 person found this answer helpful.
  2. Yonggang Huang 81 Reputation points
    2021-01-15T16:58:42.627+00:00

    You need to make sure Kerberos works on both servers. If you can RDP into the server that set up the linked server and connect to the linked server fine, verify it is using Kerberos, you can check the auth_scheme from the sys.dm_exec_connections on the target server to verify it. For example,

    select c.auth_scheme,* from sys.dm_exec_sessions s join sys.dm_exec_connections c on s.session_id=c.session_id where s.host_name ='yourhostname'.

    Make sure it is Kerberos used by the connection.

  3. Dirk Hondong 871 Reputation points
    2021-01-16T14:41:05.997+00:00

    Hi there,

    what you describe is a classic double hop scenario.
    local SSMS->Server A -lkd srv conn -> Server B

    Just having an SPN for both servers is not enough.
    The account running the sql server engine needs the information where to delegate to.

    So, if you have a SQL Server A which runs under yourdomain\mysvcAccforServerA, than this account needs the right to delegate credentials to another SPN, eg MSSQLSvc/MyServerb.yourdomain

    Regards
    Dirk

  4. Kévin Paré 1 Reputation point
    2021-08-17T10:10:15.127+00:00

    hi i wana big name in imposible am Anonymous logon prevwies my accoupt and release the rest

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.