Dynamically set SPN for SQL Server

If you look closely in your SQL Server errorlogs, you might notice that many of the carry the following error: “The SQL Server 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.”

This happens when you run your SQL Server service under a domain account that is not a member of the “Domain Admins” AD group. (Security NOTE: Your SQL Server service account should NEVER be a member of  the “Domain Admins” AD group) It does not happen if you run your SQL Server service under “Local System” or “Network Service”. The error has its base in the fact that your service account does not have the permission to update its own SPN. You need to register a SPN (Service Principal Name) if you want to use Kerberos authentication.

So what do you do? Well, you could manually fix it using setspn.exe. (More info at http://technet.microsoft.com/en-us/library/bb735885.aspx) Or, you could configure your account so that it DOES have the correct permissions, as described in http://support.microsoft.com/kb/319723 . But here comes the twist: When doing this I have found that I sometimes do not see the described permissions, so back to sqare one. What you need to do then is to set the permissions according to http://support.microsoft.com/kb/319723 , but instead give SELF the permissions to “Read all Properties” and “Write all Properties” then it works!