Allowing a Service Account to Manage Its Own Service Principal Name (SPN)

As a best practice, Microsoft recommends that SQL Server be run using a domain account. This account is sometimes referred to as the SQL service account. However, when you configure SQL with a domain account you will get an event in the SQL Logs:

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.

Doing a Google or Bing Search takes you to multiple MSDN articles that have you running SETSPN to assign the SPN to the domain account that SQL server is using. This can be time consuming and subject to typos and other errors. If you don't set the SPN properly, Kerberos Authentication will not work and that stops pass through authentication from working.  See this article from the Microsoft SQL Server Protocols team for more information.

A simple and easier way to fix this is by using Active Directory Users and Computers to assign the Write Public Information permission to Self on the domain account that SQL is using, as shown below:

If you do this before installing SQL, no restarts are needed. If SQL Server is already running then you will need to restart SQL Server.

After applying this fix you will find the following event in the SQL Logs:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domain.com ] for the SQL Server service.

This will allow SQL to register the SPN on start up, and un-register the SPN on shutdown. It also helps when you remove a SQL server from the domain, in that when you shutdown SQL the SPN is unregistered, thus helping to keep your AD database cleaner.

You can also apply this method to other services that use a SPN and you are using a domain service account to run that service.

Special thanks to my colleague, Rick Romack, at Convergent Computing for this tip!