Recently I checked my SQL Server Error Logs. Quite some interesting information in my opinion, however I also found this message:
Date 25-7-2017 18:26:41
Log SQL Server (Archive #3 – 25-7-2017 18:34:00)
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL01.contoso.lan:NAV ] for the SQL Server service. Windows return code: 0x200b, 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.
What’s this message about?
It cleary indicates thats SQL Server couldn’t register SPN’s. I’m running SQL Server under a ‘Virtual’ account so that should be the cause of the ‘error’.
From security perspective it is recommend to run SQL Server under the least privileged account: a virtual of MSA. For more information please go to Microsoft Docs.
In order to use Kerberos authentication with SQL Server there are some conditions to be met:
– The client and server computers should be in the same domain or trusted (2 way) domains.
– SPN’s must be registered for SQL Server
In theory I can’t connect to my SQL Server using Kerberos authentication so why I’m still able to connect to my SQL Server? What kind of authentication is being used? Even other services from other machines are still able to connect (like Microsoft Dynamics NAV). In order to get an answer you could query SQL Server. With this query you’re able to view what kind of authentication scheme is being used:
select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections
It turns out that Microsoft Dynamics NAV for example is falling back to Ntlm, intereseting… So let’s fix the SPN, restart SQL Server and look what’s happening? Now Microsoft Dynamics NAV 2017 is also connected to SQL Server but instead of Ntlm it is using Kerberos now.
In order to fix the SPN problem I manually registered the SPN in Active Directory (on the SQL Computeraccount). The errorlog states two SPN’s couldn’t be registered:
For your information: I’m running SQL Server in a named instance called ‘NAV’ using Dynamic Ports. If you’re running SQL Server in the default instance on TCP Port 1433 the SPN’s are a little bit different. Please keep this in mind!