How does the Service Principal Name process work

Questions: Could you give me a brief step through the process of the Service Principal Name (SPN) and the role as part of Kerberos based authentication to SQL Server?

Answer: A high level view for a windows user:  a connection request using Kerberos authentication checks for valid SPN - in the event  SPN is not available,NTLM is used . 

But let's dig in to some more detail as understanding some of the detail helps to troubleshoot common connection issues

Firstly for Kerberos authentication to SQL Server two requirements must be met :

1)Client and SQL Server servers are in the same domain or in trusted domains

2)Active Directory hosts the SPNS. When an SPN is first registered it maps to the account that starts the SQL Server instance service.

Assuming a valid SPN is registered - 

1) The client host app making the connection request  gets the SQL Server IP  and fully qualified domain name (FQDN) using forward\reverse lookups
2)The client driver generates an SPN in a predefined format. For SQL Server TCP Connection,the  format is MSSQLSvc/FQDN: [Port Number]
3) The client  submits requests to the domain controller with the SPN parameter details. It uses windows API InitializeSecurityContext for this work
4) The Domain controller check for the SPN. If valid SPN exists, it issues a token and client machine submit this token to SQL Server for authentication verification
5) SQL Server receives the Tabular Data Stream (TDS) packet and uses another Windows API AcceptSecurityContext and decrypts the token and contact domain controller to validate the SPN. If validation is successful, SQL Server allows the user to connect to SQL instance as per the assigned permissions

Read more Kerberos and SQL Server

How to register an SPN for SQL Server

How to debug Kerberos and LDAP for SQL Server

 List the differences between Kerberos and NTLM