SQL Server + AD Authentication - Kerberos + NTLM = Login failed for user 'NT AUTHORITY \ ANONYMOUS LOGON'

Reading Time: 4 minutes

Speak guys!
In this article, I would like to document and share an experience I had TODAY, in the consulting where I work, in which we had a problem with a client that caused all Linked Servers pointing to a particular instance to start showing the error below, both to try to query data and to try to alter objects (such as Stored Procedures) that used this linked server. And with great support from Rodrigo Ribeiro Gomes, we managed to solve this problem.

Msg 18456, Level 14, State 1, Line 4
Login failed for user 'NT AUTHORITY \ ANONYMOUS LOGON'.

The first suspicion when I came across this message, obviously, was something wrong with Kerberos Double Hop, which is a term used to describe our method of keeping the client's Kerberos authentication credentials on two or more connections. In this way, we can maintain the user's credentials and act on behalf of the user in other connections to other servers. as when we use a Linked Server configured to use the security context of the logged in user (“Be made using the login's current security context”) and we are using a connection with AD authentication to use that Linked Server.

By default, SQL Server will always try to use Kerberos authentication mode when using an account with AD authentication. If Kerberos is not available then Kerberos will attempt to use NTLM authentication mode (commonly used on stand-alone systems).

Using a simple query, we can verify the number of connections in each authentication mode:

Example:

As with parsing, there were no connections using Kerberos authentication, only SQL (when you use SQL Server logins to connect to SQL Server) and NTLM (Connection using Windows Authentication when Kerberos is not available). This is a strong indication that Kerberos is not working correctly.

I have enabled Kerberos logging to try to identify any issues. To do this, I created the LogLevel (DWORD) registry key with the value = 1 at the address HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Lsa \ Kerberos \ Parameters.

After enabling Kerberos logging, I reviewed the logs in Event Viewer and identified the following Kerberos error:

Error Code: 0x19 KDC_ERR_PREAUTH_REQUIRED

When we consult on the internet, we can understand that this error is “normal” from Kerberos and can be ignored. Therefore, I analyzed the servers registered in the SPN of the SQL Server Database Engine service account, which is an AD user in the format “DOMINIO \ Usuario”:

Listing registered SPNs for the AD account running the SQL Server service

Since this instance is part of a Windows Cluster of 2 nodes and the problem started after a failover, I ran the result of setspn -L on 2 nodes and compared the results using Notepad ++, which showed that the records were identical on 2 nodes.

The next step was to analyze the SQL Server logs with the xp_readerrorlog, to verify that the SPN was registered normally:

Result:

If registration is not successful, you will see a message like this in the SQL Server log:

By logging, SQL Server correctly registered the instance SPN, but for some reason it is no longer showing up when I use the setspn -L command that I demonstrated earlier. Probably these missing records that are causing the error of this post.

Therefore, I will manually register the SPN records for this instance just as they were initially registered:

After manually registering these entries, Linked Servers that pointed to this instance resumed their normal operation and the error was corrected. I rerun a query on the sys.dm_exec_connections DMV and new user connections using Windows (AD) authentication are already being made using Kerberos instead of NTLM (existing connections need to be closed so that when they connect again they will use Kerberos).

Result:

Once again, I would like to thank the support and guidance of the Rodrigo Ribeiro Gomes, which were instrumental in solving this problem today.

I hope this article will help you solve this problem if you are experiencing this scenario. Errors in Kerberos often take some work to identify and correct, so this is not always the solution. As Rodrigo himself commented to me, there are cases where even when the SPN records are correct, errors can occur in Kerberos, and it is necessary to parse Kerberos packages using tools like Wireshark.

References:
- https://serverfault.com/questions/808198/how-to-enable-logging-for-kerberos-on-windows-2012-r21
- https://blogs.msdn.microsoft.com/sql_protocols/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections/
- https://blogs.technet.microsoft.com/askds/2008/06/13/understanding-kerberos-double-hop/
- https://comunidadesqlserver.wordpress.com/2017/06/22/troubleshooting-kerberos-configuration/
- https://community.microstrategy.com/s/article/KB34369-How-to-use-Wireshark-to-troubleshoot-Kerberos-Issues
- https://docs.microsoft.com/en-us/windows/desktop/secauthn/microsoft-ntlm
- https://blogs.technet.microsoft.com/askds/2008/05/29/kerberos-authentication-problems-service-principal-name-spn-issues-part-1/

That's it folks!
I hope you enjoyed this article and even more!