Rajendra Gupta
SPN process for failover clustering

Overview of Service Principal Name and Kerberos authentication in SQL Server

July 18, 2019 by

This article gives an overview of Service Principal Name (SPN) for using the Kerberos authentication in SQL Server connections. We use the Kerberos authentication to authenticate windows users securely for providing access to SQL Server.

Introduction of Service Principal Name and Kerberos authentication SQL Server

Let’s start this article with a scenario that you might have faced in your environment. Suppose you have a SQL Server and its services are running under the local system account. As per the organization requirement, you changed the service account from a local system to a domain account. After the restart of SQL Service, you can connect to SQL Server after taking RDP to the system, but no application users can connect to it. We will understand the solution to this problem in the latter part of this article.

We have two kinds of Server authentication methods in SQL Server.

Server Authentication

  • SQL authentication: We can create a SQL login and provide appropriate rights to that login. SQL Server handles SQL login authentication
  • Windows authentication: We can use domain accounts to add to SQL Server and connect with the Windows authentication method. SQL Server does not handle the authentication part for a windows login account. It passes the authentication to Windows Security Support Provider Interface (SSPI) which is a component of the operating system

Connect to SQL Server and execute the following query:

You can see the following results

  • KERBEROS – it shows the connections using the KERBEROS authentication
  • SQL – if the connections are using the SQL authentication, we get auth_scheme SQL
  • NTLM – it shows the connections using the NTLM authentication

Authentication scheme

Before we move ahead, let us connect to a SQL Server instance and look at the error logs.

You will find following logs:

SQL Server error log for Service Principal Name issue

You can have a high-level overview of the Service Principal Name (SPN) connection process. For a windows user, Kerberos authentication check for valid SPN. In case SPN is not available, it uses the NTLM authentication method.

High-level overview of the Service Principal Name (SPN) connection process

SSPI first tries to use the default authentication method (starting from Windows 2000). Kerberos requires SPN for the authentication purpose. If there is no SPN exists, it switches the authentication to the old NTLM process.

In case SPN exists, but it is not valid, an entry is logged into SQL Server Error logs.

SSPI handshake error message

Let’s understand the Service Principal Name (SPN) process in detail.

  • The client machine gets the IP address and fully qualified domain name (FQDN) of SQL Server using forward and reverses lookups
  • The client driver generates an SPN in a predefined format. For SQL Server, it uses format MSSQLSvc/FQDN: Port Number
  • It submits requests to the domain controller with the SPN parameter details. It uses windows API InitializeSecurityContext for this work
  • 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 purpose
  • SQL Server receives the 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

If there is an error in this AcceptSecurityContext process, SQL Server returns the following error messages:

  • Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
  • Login failed for user ‘(null)’
  • SSPI handshake failed error
  • Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

You can understand the Kerberos authentication process using the following image.

Process of Kerberos authentication

Prerequisites to use Kerberos authentication in SQL Server

SQL Server should meet the following prerequisites to use Kerberos authentication in SQL Server.

  • SQL Server and client machine should be part of the domain
  • We might use multiple domains in the environment. We can still use Kerberos authentication, but the domain trust relationship should be there
  • Service Principal Name (SPN) should be registered successfully for the SQL Services

Once we start SQL Services, you can look entry for the Service principal name in the error logs.

  • Successful SPN Registration message

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL.testdomain.in:24629 ] for the SQL Server service.

  • Failed SPN Registration message

Failed Service Principal Name Registration message

Let’s go back to our introduction part scenario. In that example, previous SQL Services were running under the local system. At that time, SQL Server registered the Service Principal Name (SPN) successfully, and users can connect to the SQL using Kerberos authentication. Now once you changed the service account, SQL Server failed to deregister the old SPN associated with the local system account. Once the users try to connect to SQL Server, it fails because existing SPN is not associated with the existing service account. You get Cannot Generate SSPI Context error in this case.

Comparison of Kerberos authentication and NTLM

  • Kerberos provides a faster Authentication method compare to the NTLM
  • NTLM allows only single hop from the client machine to the SQL Server. Nowadays, we require many hopes between servers. For example, using a linked server, we connect to a different server, or we might think of an SSRS solution in which SSRS components and databases are on different servers

Kerberos allows multiple hops, and we can use it to communicate with the end machine using the same credentials. It helps to effectively manage the authentication solution and also reduces the load on the domain controller.

At first, a client gets a TGT from the domain controller after providing the credentials. The client uses this TGT and asks a Service ticket from the domain controller. In this example, it asks a service ticket to connect with SQL Server 1.

In SQL Server 1, we have a linked server that connects to another SQL Server using the current login security context. If you have used linked server before, you might know that we do not need to provide the credentials again for the windows user. It automatically takes care of the credentials. It is due to the double hop method of Kerberos authentication.

Now, SQL Server uses existing TGT and connects with the domain controller to ask for another service ticket to connect with SQL Server 2. Once it gets a service ticket, it can authenticate to SQL Server 2 successfully.

Multi-hop authentication

  • Kerberos authentication is more secure than NTLM
  • Kerberos authentication is an open standard solution
  • You can use smart card login using the Kerberos authentication while NTLM does not provide this functionality

Service Principal Names overview

Service Principal Names (SPN) is a unique identifier for each service. We must have an SPN for each SQL instance. In the case of multiple instances, we must register all the SPN. It is a mandatory step for SQL Server connections to use Kerberos authentication.

For SQL Services, the format of SPN is MSSQLSvc/SQLFQDN: Port number.

SPN for the default standalone SQL instance

Suppose the FQDN for SQL Server is SQLA.TestDomain.com and its running on the default port 1433.

SPN for default instance will be as follows.

  • MSSQLSvc/ SQLA.TestDomain.com
  • MSSQLSvc/ SQLA.TestDomain.com:1433

In the SQL Error log, you will get the following entry:

SQL Server error log for default instance

Service Principal Name (SPN) for named standalone instance

Suppose we have a named instance for SQL Server SqlShack and the FQDN for SQL Server is SQLA.TestDomain.com.

The named instance is also running on port 64234.

SPN for the named instance will be as follows:

  • MSSQLSvc/ SQLA.TestDomain.com:SqlShack
  • MSSQLSvc/ SQLA.TestDomain.com:64234

In the SQL Error log, you will get the following entry.

SQL Server error log for named  instance

SPN for Failover Cluster instance

If we are using a failover cluster instance, we do not connect SQL with the individual SQL Services. We use the failover cluster virtual name for this.

Instance Type

FQDN

Standalone

FQDN of the standalone instance

Failover Cluster

Failover cluster virtual name

Suppose we have the following environment.

  • Nodes: SQLA and SQLB
  • Failover Cluster Virtual name: SQLC
  • SQL Server port: 1433 (default)

The SPN of this failover instance will be as follows:

  • MSSQLSvc/ SQLC.TestDomain.com
  • MSSQLSvc/ SQLC.TestDomain.com: 1433

In the SQL Server error logs, you can see the following entries.

SQL Server error log for failover cluster  instance

In the following image, you can understand the Service Principal Name (SPN) for the failover cluster SQL.

SPN process for failover clustering

SPN for Always-On SQL Listener

We use SQL Listener to connect with the primary replica in SQL Server Always On. We should create SPN for each availability group SQL Listener. It enables the Kerberos authentication for the client connection. We should use the same SQL Service account for all Availability group replicas.

We need to use FQDN of SQL Listener along with listener port to configure SPN for SQL Server Always On.

Suppose we have an availability group SQLAGProd-DB-LSN and all replicas are running with domain service account testdomain\SQLprod. The listener is configured to run with port 81234

In this case, we can use the following SETSPN command to configure an SPN.

setspn -A MSSQLSvc/ SQLAGProd-DB-LSN.testdomain.com: 81234 testdomain\SQLprod

The SETSPN Command

We can use SETSPN command to list the available SPN for the specific domain account. Execute the following query with administrative rights.

List all registered SPN

We can use –L parameter with the setspn command to list all available SPN associated with a service account.

setspn -L <Domain\Service Account>

Manually Register SPN

A domain administrator can manually register the SPN as well using the following command.

setspn –a MSSQLSvc/<hostnameFQDN>:1433 <Domain\Service account>

We need to register SPN for each SQL Service.

Automatically Register Service Principal Name

SQL Server can automatically register SPN during startup of SQL Services. In this case, SQL Services should be running under a local system or network service or the domain account has sufficient permissions to register an SPN. It requires Read servicePrincipal name and writes ServicePrincipal name permissions in the active directory.

Conclusion

In this article, we explored the Service Principal Name along with the Kerberos authentication method to connect to SQL Server. It helps to troubleshoot the issues if you are familiar with the internal processes.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

1,388 Views