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 error 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 messages:
Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]
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 SQL Server ERRORLOG?
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 is registered for the SQL Server service. This is an informational message. No user action is required.
In 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 good 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 (http://blog.sqlauthority.com)