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.
- 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:
|
1 2 3 4 5 |
USE master GO SELECT distinct auth_scheme FROM sys.dm_exec_connections GO |
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
Before we move ahead, let us connect to a SQL Server instance and look at the error logs.
You will find following logs:
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.
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.
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.
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
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.
- 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:
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.
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.
In the following image, you can understand the Service Principal Name (SPN) for the failover cluster SQL.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023