How to register an SPN for SQL Server

I received a question from a colleague asking how to register a Service Principal Name (SPN) for SQL Server. The specific situation relates to upgrades and new SQL Server instances deployed onto new servers\server names . 

Defining an SPN results in an Active Directory name - allowing a client to uniquely identify  the service instance.  The SPN is comprised of a service name with a computer and user account resulting in a Service id. 

Prerequisistes for Kerberos:

-Clients & Servers must be joined to the domain. If they are in different domains - there must be a trust established between the domain s

-An SPN must be successfully registered

How to check if SPN is registered

-If the SQL Service instance is running under a domain account - use this command to check 

                   setspn -l MYDOMAIN\mysqlserverserviceaccount 

You can also use the SQL Server Error Log to check if the SPN has not been registered successfully . Typically you will check the SQL Server Error Log  with xp_readerrorlog  

                 xp_readerrorlog 0, 1, "SPN"

                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.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.domain.com:MYSQLSERVER ] 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.

Manual registration - If you don't have an SPN registered successfully - you can either register the SPN manually  via the setspn - s MSSQLSvc/myserver.domain.com:MYSQLSERVER

Automatic registration - For automatic SPN registration the SQL Server startup account needs to be running either as Local Systemr / Network Service/Domain Administrator account or an account with sufficient permissions to register an SPN

Either way the account registering the SPNS must have permissions in the Active Directory to register the SPN   

         "Read servicePrincipalName"

        "Write servicePrincipalName"

Read more on SQL Server and Active Directory 

List the differences between Kerberos and NTLM

 How does the Service Principal Name process work