Errors related to logins are one of the most searched in this blog. Many of the blogs that talk about these errors are the ones that get viewed often too. This is one of the interesting errors, which you might see in your environments. One of my blog reader contacted me and told that their event log and ERRORLOG is full of below the messages:
Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]
The very first thing which should catch your eye would be “ANONYMOUS LOGON”. It is also referred as NULL session. My first ask from him was if he was seeing any SPN related errors in the SQL Server ERRORLOG?
SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location
Here is the message which we found in ERRORLOG
2015-06-09 16:25:59.86 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN has registered for the SQL Server service. This is an informational message. No user action is required.
In the above lines, SQL is trying to register automatically. But below indicates that SQL was unable to do so.
2015-06-09 16:26:06.90 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:SQL2014 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. 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.
2015-06-09 16:26:06.90 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:1500 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. 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.
So, the fix of the problem would be to create SPN which are needed for this SQL Instance. We need to use SETSPN.exe and create SPNs which SQL was trying to register. How would one get the tool? As per https://technet.microsoft.com/en-us/library/cc731241.aspx
{
Setspn is a command-line tool that is built into Windows Server 2008. It is available if you have the Active Directory Domain Services (AD DS) server role installed. To use setspn, you must run the setspn command from an elevated command prompt. To open an elevated command prompt, click Start, right-click Command Prompt, and then click Run as administrator.
}
For a TCP/IP connection the SPN is registered in the format of MSSQLSvc/<FQDN>:<tcpport>. We should remember that both named instances and the default instance, are registered as MSSQLSvc, but <tcpport> value to would be different for instances. Here are the commands if you want to create them manually.
- To List SPN we need -L parameter.
SetSPN -L domain\account
- To create/add SPN we need to use -A parameter
SetSPN –A MSSQLSvc/<SQL Server FQDN>:<port> <Domain\Account>
While searching for SETSPN.exe command I came across this nice tool which can help a lot of pains like syntax error etc. is called as “Microsoft® Kerberos Configuration Manager for SQL Server®” which can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=39046
The best piece about this tool is that it can help in finding missing SPN and also provide script to run or fix it directly, if you have permission. Basically, it can
- Gather information on OS and Microsoft SQL Server instances installed on a server.
- Report on all SPN and delegation configurations on the server.
- Identify potential problems in SPNs and delegations.
- Fix potential SPN problems.
Once SPN was created, we were able to fix the issue.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Hi Pinal,
Is it good Idea to Use this Login ‘NT AUTHORITYANONYMOUS LOGON’
Unish – did you mean adding that as login? I normally don’t suggest that.
Kerberos tool fails, “Unable to access User Principal information from the System”. Can’t find a solution to this issue, seems like an active directory lookup issue.
means account is not able to query AD to get SPN. You need a proper account which has permissions.
Linked Server setup is successful between A to B and able to query the results on the A server machine, but the issue is outside the sql server machine (from other machine in the same domain) test connection fails and I receive the error Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ when executing the select statement
Same here: Kerberos tool fails, “Unable to access User Principal information from the System”. Logged on as domain admin, running the tool either on SQL or AD servers.
This article saved my day. I was facing logon issue after Fresh Installation of SQL Server 2016. Had to register SPN.
To fix the “Unable to access User Principal information from the System” running the tool check if there is an orphan user in the Local Administrator Group. This user will show as an GUID and will not show the name, delete this user from the group and try the tool again.
Hi Pinal,
Had issue with that connection and registered SPN according to your suggestions via “Microsoft® Kerberos Configuration Manager for SQL Server®”. So just right now I don’t have an error with Login ‘NT AUTHORITYANONYMOUS LOGON’ and can browse linked server. However when I open DB’s tree on linked server I see only DBs from origin server (the one on which linked server is configured). How come? What should be done to have an access to DBs that linked server has? As well when I try to query table from linked server there is an error that there is no such a table on the server (seems to be correct considering fact that I see in the structure the same DBs as origin server has)
Hi Pinal,
Your article really helped to resolve the issue with enabling Kerberos connection between Tableau Server and MS SQL DB / OLAP. Thank you for sharing the Kerberos Configuration Manager for SQL Server.
Hi, Pinal,
I’m having this same issue. SQLCMD crashes on every attempt to run. But my situation is different. This is a SQL development workstation. Stand-alone (workgroup) Windows 10 Pro 64-bit machine with SQL 2014 Developer Edition, no domain, no domain controller. So there can be no Kerberos transactions. The Kerberos Configuration tool will not connect to local machine. It fails saying LDAP server is unavailable.
Is there any other recourse to get around SQL SERVER – Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’ on a stand-alone machine so that SQLCMD can execute?
Thanks!
Jeff
Hi Pinal,
In my case I am getting same error “Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’” for SQL Account (not Active directory account). Yes SPN for that account is not configured, do I need to configure?
Thanks
Robb
Hi Pinal
Wounder if you can help, I have a variation of this issue.
From Server A in SSMS create a link Server to Server B. this works able to see all the databases in the catalog for the linked server (have this working for 6 different SQL 2017 Express Windows 10 systems in a domain).
But if i open SSMS on Server B and connect to Server A it gives me the Login failed for user (‘NT AUTHORITY\ANONYMOUS LOGON’) when browsing the linked server to server B.
Of the 6 above systems 2 work 4 do not work for the same Domain login, the domain login is a local administrator on all 6 systems.
Hi Pinal, I’m getting this error if I run the query with the linked server from my PC SSMS (or any other PC with SSMS), but if I RDP into the SQL were the linked server is defined I can run without error