Service Principal Names (SPN) get used for Kerberos authentication. SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails. By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos. If it fails, it falls back to NTLM. Often if you have Kerberos issues you might also have SSPI error messages in your SQL Server errorlog.
So question can be asked, if it falls back to NTLM and I can get my work done. Why do I care? Simple answer is delegation. With incorrect configured Kerberos, delegation cannot be completed and logins fail. For example on delegation, read my post on Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services.
So what SPNs we need? Simple answer two SPN per SQL Server instance.
Depending on your configuration, SPNs can look different. We can manage SPNs two ways; 1) using SETSPN utility 2) using Activity Tool call EDITADIS. Most people rely on SETSPN, as other tool is for Active Directory administrators. And they (AD Admins) don’t like it when we mess around with their stuff; like we don’t like it when they mess around with our stuff ;-). So lets talk about various forms of SPNs you can create.
Example #1: Default Instance, Default Port (1433), Using Domain Service Account
Example #2: Default Instance, Non-Default Port, Using Domain Service Account Example #3: Named Instance, Static Non-Default Port, Using Domain Service Account
Example #4: Default Instance, Default Port (1433), Using Computer Account *
Example #5: Default Instance, Non-Default Port, Using Computer Account * Example #6: Named Instance, Static Non-Default Port, Using Computer Account *
Example #7: Named Instance, Dynamic Non-Default Port, Using Domain Service Account **
Example #8: Named Instance, Dynamic Non-Default Port, Using Computer Account *
* An instance is consider using computer account when it is running under built in accounts, such as Network Services.** Generally we do not recommend dynamic port as managing SPN manually can be difficult. Also for us to use InstanceName, the SQL Server Browser service must be running.
So after reading this you are going that’s TOO MUCH TO REMEMBER!
I agree, that is why SQL Server can create and destroy this SPNs by itself without user interaction. However in order to do this, we must grant SQL Server Service Account or Computer Account permissions to Read/Write SPN Property on itself (Reference).
Now there is one scenario where we do not want to grant this permission and mange the SPN manually. Please reference KB2443457. Copied the issue from KB for completeness.
Blog post cross posted on ...