Login Failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’

I was recently asked to look at an issue that was occurring when someone tried to run a simple SELECT query on a table. The error they received was ‘Login Failed for User ‘NT AUTHORITY\ANONYMOUS LOGON”

Strange.

The first thing I did was run a query using my own account and this worked fine.

My next  port of call was to impersonate the user having the issue to try and recreate the problem. I do this using the SETUSER command

SETUSER 'Domain\Login'

SELECT TOP (100) [TransDate]
      ,[TransDateTime]
      ,[LocationCode]
      ,[ItemCode]
      ,[Quantity]
      ,[TransactionType]
  FROM [DbName].[Schema].[Table] 

SETUSER

Interestingly, I got a different error message: ‘Access to the remote server is denied because the current security context is not trusted’:

Eh?

It works fine with my login but I am sysadmin so that doesn’t mean anything. I tried the same for other users and I got the same error message. There was definitely something wrong with the server set up but I couldn’t figure out if it was SQL Server or Windows related.

My next port of call was the ERRORLOG.

BINGO!

And then Bingo again:

This second message was the more important one as it confirmed that the Server Principal Name (SPN) could not be registered and therefore Kerberos Authentication could not be used. To double-check, I ran the following T-SQL query to confirm. If Kerberos Authentication was in use, it would appear at least once under the ‘Auth_scheme’ column. If  not, then you will see ‘NTLM’ (NT LAN Manager).

SELECT
    dec.session_id,
    dec.auth_scheme
FROM sys.dm_exec_connections AS dec

So to fix this, you need to register the SPN. The following command will do this, however you need the appropriate elevated permissions to be able to do this. If you don’t have them, you’ll have to ask your Windows System Admin to do this for you.

setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\SQLServiceAccount>

Or if you are using a named instance:

setspn -A MSSQLSvc/<SQL Server Computer name>:instancename DOMAIN\SQLServiceAccount

Here’s the Microsoft Technet Document on registering the SPN:

Now for the root cause:

Hmmmm.. Well, I’m not entirely sure but I’m guessing this goes back to when the server was first built and configured. It could actually just be as simple as the SPN not being registered or it could have been because the SQL Server was set up using a different service account, The SPN error however had only started being generated in the last 2 months and previous errors logs didn’t report this. One thing I noticed about this server in particular that the owner of the main data folders for the SQL Server was no longer working at the company and the owner was just displayed a GUID. I changed this over to be on the safe side. This same person appears to have set up the SQL Server and, well to be honest  there were a number of things he did/didn’t do that weren’t what I would call ‘best practice’ so to try and recreate this scenario again, might take a bit of time and is waaaaaay out of scope for this blog post.

Just be sure to check the ERRORLOG at the start of your investigation as this will speed up the resolution.

Along with my previous blog post on Quorum, this is another good example of how external factors can wreak havoc on your SQL Servers and you need at least a basic understanding of non-DBA specific areas such as Clusters, Network Authentication and Active Directory, even if that’s just to be able to explain what your DBA  related issue is to the person who is the real expert in these areas.

Further Reading:

The difference between NTLM and Kerberos Authentication

Understanding NTLM and Kerberos Authentication with SQL Server Connections