Why Service Principal Name (SPN) need to configure for SQL Server? Unable to connect SQL Server due to SPN issue?

On servers without an SPN, we sometimes see the following kinds of intermittent connectivity errors:

“SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.”

Config Item	Default Value	Standard
SPN for SQL Servers running under domain accounts	Not created (because domain accounts lack permissions to create & register SPNs, by default)	All SQL servers running under a domain account must have an SPN created and registered in Active Directory, for the domain account under which the MsSQLserver service is running.

What is SPN?

A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service.  When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.   The benefit of having an SPN is that it allows the system to use Kerberos authentication along with NTLM.  In general, Kerberos(Kerberos is a network authentication protocol provides a highly secure method to authenticate client and server entities (security principals) on a network.) is known to perform better and is more secure than NTLM.

To reduce the possibility of network connectivity errors, all SQL servers running under a domain account must have an SPN created and registered in Active Directory, for the domain account under which the MsSQLserver service is running. 

How to create SPN ?

Window Server Team responsible to create it- 

·  To create an SPN for the NetBIOS name of the SQL Server use the following command: 

C:\> setspn –A MSSQLSvc/<SQL Server computer name or Virtual name of SQL Server>:1433 <Domain\Account>

·  To create an SPN for the FQDN of the SQL Server use the following command: 

  C:\> setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>

How to verify registered SPN ?

To view registered SPNs on a server for the domain account under which SQL Server is running, we can use the Setspn command as follows (the output below is the simplified output for just one named SQL instance your on server 

C:\> setspn -U -L Redmond\_backup

For more information about SPNs see the following links: MSDN important Links- 

• How to Configure an SPN for SQL Server Site Database Servers
• Registering a Service Principal Name for Kerberos Connections
• How to use SPNs when you configure Web applications that are hosted on Internet Information Services Service Principal Names (SPNs)
• Service Principal Names (SPNs) SetSPN

Please comment below if know more about SPN - Jainendra Verma