Overview of Service Principal Name and Kerberos authentication in SQL Server

SQL Server supports two authentication modes: Windows Authentication mode and SQL Server Authentication mode. In Windows Authentication mode, SQL Server uses Kerberos security protocol for authentication. Kerberos is a computer network authentication protocol that works on the basis of “tickets” to allow nodes communicating over a non-secure network to prove their identity to one another in a secure manner. SQL Server can use Kerberos Authentication Protocol to authenticate itself to Active Directory Domain Services (AD DS) so that AD DS can trust SQL Server’s identity. SQL Server can also use Kerberos Authentication Protocol to authenticate users who are connecting to SQL Server. When SQL Server is configured to use Kerberos Authentication, the service principal name (SPN) for SQL Server must be registered in AD DS. The SPN is used by Kerberos to identify the service that a client wants to connect to. SQL Server must be able to locate the SPN in order to request a ticket from the Key Distribution Center (KDC) in AD DS. If the SPN is not registered or if SQL Server cannot locate the SPN, then Kerberos authentication will fail and the connection will be denied.

In SQL Server Authentication mode, SQL Server uses its own internal authentication process that does not rely on AD DS. In this mode, SQL Server stores details of all authorized users, including passwords, in a local security database. When a user attempts to connect to SQL Server using their username and password, SQL Server compares the supplied credentials with those stored in the security database. If there is a match, then the connection is allowed; if not, then the connection is denied. Although SQL SERVER Overview of Service Principal Name and Kerberos authentication in SQL does support both Windows Authentication mode and SQL Sever Authentication mode, it is recommended that you use Windows Authentication whenever possible as it offers better security and many convenience features such as Single Sign-On (SSO).

–Use the Command line to run these on the domain controller to either register or deregister
 
setspn -D MSSQLSvc/servername.domain  us\serviceAccount     – De-Register
setspn -D MSSQLSvc/servername.domain:1433  us\serviceAccount  – De-Register
 
setspn -s MSSQLSvc/servername.domain  us\serviceAccount  – Register
setspn -s MSSQLSvc/servername.domain:1433  us\serviceAccount  – Register
 

— Command to Verify what registered

setspn -L account name
setspn -L Server name