- You run your MS SQL database engine service under a low privileged domain account
- You use Windows Authentication.
- Your SQL Server instance logs this at every start:
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Not running Kerberos is a security risk as well as performance/feature degradation.
While you’re at it, I suggest you run this on all your server to see in what state they are in:
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;
So, to resolve this problem, there are 3 options:
- You register register SPN manually (which is a horrid journey, but probably most secure).
It can get confusing over which of the two options below that you should use…since the one that Microsoft recommends also denotes the following:
Important We recommend that you do not grant WriteServicePrincipalName right to the SQL service account when the following conditions are true:
- There are multiple domain controllers.
- SQL Server is clustered.
Altho, Microsofts only documentation on this particular subject only seem to touch upon Win 2003/SQL 2000..but seriously, how many are happy with only one domain controller – and yet this method is recommended?
So, the two options:
- You grant the service account running SQL Server database engine the “Write Public Information” permissions on the “SELF”. Hark! This is a potential security risk (or troll risk) since this permission implies more than simply dabbling with SPN.
Many caves on the interwebs suggest this method.
- You open your service account in adsiedit.msc (recommended by Microsoft) and assign the follow permission on the “SELF”:
Manual registration of SPN won’t keep your AD database as clean as when adequate permissions are given the service account so it can register/unregister at start and shutdown. But as as Microsoft kindly enlighten, latency can cause problem, if you have lots of DC latency you probably have shitloads of other problems anyway. Just monitor it if you’re scared.
Whatever choice you have made, you must restart the SQL instance in order for it to start registering the SPN.
You will see a message saying that the Network Interface Library successfully registered your serviceaccount SPN.
Register a Service Principal Name for Kerberos Connections
Active Directory & SQL Server – How AD can affect your SQL Servers (PDF)