Skip to content
Home » Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step

Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”. This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

Hopefully, with this blog post, I’ll provide you with an easy-to-understand step-by-step guide, which would help you resolve the issue the right way, without any workarounds.

Problem

This issue is often called “double-hop pass-through authentication”, also known as “Kerberos delegation“, which I’ll try to illustrate with the following diagram:

Double-hop with Linked Server illustrated

In some cases, you would notice that the same error does NOT happen when you’re connected directly to the SQL Server (i.e. via RDP):

Single-hop with Linked Server illustrated

This is an issue that I see coming up quite often, but very difficult to find a solution for it. Solving it involves a bunch of stuff outside our comfort zone as DBAs, which is also why most people would instead go with a workaround, either using SQL authentication instead of Windows authentication or dangerously misconfiguring their linked servers.

Solution

Solving this issue requires several steps:

1. Both SQL Servers and the Client must be in the same Domain

If all parties involved are already within the same domain, that makes everything much easier.

If you have a multiple-domain environment and the servers are in different domains, then at the very least, both of the domains must be within the same Active Directory (aka Domain “Forest”).

Taking care of this is totally not your job as a DBA, though. You’re gonna have to bother the folks at IT/DevOps/Whichever relevant department for this.

If this is not possible for whatever reason, then you’ll have no choice but to go with a workaround: Using SQL Authentication, and/or Linked Server login mapping.

2. Both SQL Servers must be able to register SPNs

The SPN (Service Principal Name), after it’s registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn’t been performed or fails, the Windows security layer can’t determine the account associated with the SPN, and Kerberos authentication isn’t used.

First, to find out whether a SQL Server was able to register itself as SPN or not, you can look in the SQL Server Error Log, right near the start where the SQL instance has just started up. You’ll find messages such as these:

  • The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/******* ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

These messages indicate that the SQL Server logon account did NOT have enough permissions to register itself as SPN.

To make this work, the SQL Server service accounts must be either LocalSystem, NetworkService, or a Domain account with the permissions “Read servicePrincipalName” and “Write servicePrincipalName“.

To give permissions to the SQL Server startup account to register and modify SPN, do the following for each SQL Server machine:

  1. Connect to the Domain Controller machine, and start Active Directory Users and Computers.
  1. Select View > Advanced.
  2. Under Computers, locate the SQL Server computer, and then right-click and select Properties.
  3. Select the Security tab and click Advanced.
  4. In the list, if the SQL Server startup account is not listed, click Add to add it and once it is added, do the following:
    1. Select the account and click Edit.
    2. Under Permissions select:
      • Validated Write servicePrincipalName.
    3. Scroll down and under Properties select:
      • Read servicePrincipalName
      • Write servicePrincipalName
    4. Click OK twice.
  5. The above must be done for each of the SQL Server machines involved and their corresponding service account(s).
  6. Close Active Directory Users and Computers.

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.

For more details on enabling SPN registration, please refer to Register a Service Principal Name for Kerberos Connections at Microsoft Learn.

After enabling SPN registration, you will have to restart the SQL Server service and look for a message in the SQL Server Error Log indicating that the SPN was registered successfully.

To determine the authentication method of a connection, execute the following query:

SELECT net_transport, auth_scheme   
FROM sys.dm_exec_connections   
WHERE session_id = @@SPID;

The expected result should be KERBEROS.

Note that you must run this query from a remote computer, and NOT by running it from within the SQL Server machine itself (i.e. opening SSMS while inside a Remote Desktop session).

If it says NTLM, that means you did something wrong and/or missed a step and/or executed the query from within the SQL Server machine itself instead of remotely.

3. Enable Resource-Based Kerberos Constrained Delegation between the two servers

Resource-Based Kerberos Constrained Delegation (a.k.a. “RBKCD“) was introduced in Windows 2012 and is a way of getting Kerberos authentication to work in a web application for users from multiple domains in a forest (I realize that’s probably a bunch of bizarre buzzwords that don’t mean much to you, but hey that’s what the documentation says!).

Anyways, to make a long story short, below is a Powershell script that enables RBKCD for two specified domain accounts so that they’d be able to delegate Kerberos things to each other. Just replace “SqlSvc1” and “SqlSvc2” with the names of the two relevant SQL Server service accounts (lines 1 and 2):

$SQLServiceAccount1 = Get-ADUser -AuthType Negotiate -Filter 'SamAccountName -like "SqlSvc1"'
$SQLServiceAccount2 = Get-ADUser -AuthType Negotiate -Filter 'SamAccountName -like "SqlSvc2"'

Set-ADUser $SQLServiceAccount1.SID -PrincipalsAllowedToDelegateToAccount $SQLServiceAccount2
Set-ADUser $SQLServiceAccount2.SID -PrincipalsAllowedToDelegateToAccount $SQLServiceAccount1

# Check RBKCD and SPNs:
Clear
$Users = Get-ADUser -AuthType Negotiate -Filter '*' -Properties SamAccountName, DistinguishedName, ServicePrincipalNames, PrincipalsAllowedToDelegateToAccount

foreach ($User in $Users) {
"============================="

( $env:USERDOMAIN + "\" + $User.SamAccountName )
$User.DistinguishedName
""
"Service Principal Names:"
foreach( $SPN in $User.ServicePrincipalNames ){ ( "`t`t" + $SPN ) }
""
"RBKCD:"
foreach( $RBKCD in $User.PrincipalsAllowedToDelegateToAccount ){ ( "`t`t" + $RBKCD ) }

"============================="
}

For more information about Resource-Based Kerberos Constraint Delegation, please refer to the following resources:

Conclusion

Assuming that you followed the above steps properly, then both servers should now be able to register SPNs and then delegate Kerberos authentication to each other via Linked Servers.

You will be able to configure the linked server(s) using “the login’s current security context” like so:

Which will then delegate (or “forward”) the login’s Windows credentials over to the linked server for authentication, thus getting rid of the NT AUTHORITY\ANONYMOUS LOGON error.

There is also an alternative way to resolve this using a tool that Microsoft released a long while ago called “Kerberos Configuration Manager for SQL Server (KCM)“. However, it’s an old tool that rarely works in newer environments.

If you’re interested anyway, then please refer to the following for more information about this tool:

I hope this was helpful to you, and good luck!

If you have anything to add and/or you think I missed something and/or didn’t explain something properly, please let me know in the comments below.

2 thoughts on “Resolved: Login failed for user NT AUTHORITY\ANONYMOUS LOGON – Delegation Step-by-Step”

  1. Thanks for the help – regarding step 3, what if the service account for server 1 and server 2 is the same domain account on both servers? Would I do something like Set-ADUser $SQLServiceAccount1.SID -PrincipalsAllowedToDelegateToAccount $SQLServiceAccount1 ?

    1. That’s a good question.

      Unfortunately, I don’t recall such a situation so I don’t know. But I would guess that either the answer is “yes”, or the answer is “no, there’s no need”.

      Best to simply try it out and see. It would definitely be appreciated if you could share your findings here later 😄

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: