How to Verify and Register SPN for SQL Server Authentication with Kerberos Connections

Introduction

This article explains how to verify and register Service Principal Names (SPN) for SQL Server Authentication with Kerberos Connections. Kerberos Authentication is a widely accepted network authentication Protocol. It is used to provide a highly secure method to authenticate Windows users.

What is an SPN?

MSDN Describes Service Principal Name (SPN) as:- “SPN is the name by which a client uniquely identifies an instance of a service. If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host. Before the Kerberos authentication service can use an SPN to authenticate a service, the SPN must be registered on the account object that the service instance uses to log on. 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.” Source MSDN Article: Service Principal Names

It is always recommended to run SQL Server Services under a Domain User Account which has minimal permissions. If you are looking for different ways to secure SQL Server within your environment then read the following “SQL Server Security Best Practices” article.

TSQL Query to verify SQL Server/Windows Authentication scheme used by SQL Server Connection

Execute the below TSQL Query to verify authentication used by SQL Server Connections.

USE master
GO

SELECT auth_scheme FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;
GO

Expected Results

SQL – When SQL Server authentication is used
NTLM – When NTLM authentication is used
KERBEROS – When KERBEROS authentication is used

Prerequisites when configuring SQL Server to use Kerberos Authentication

• All client and servers should be joined to a domain.
• If the clients and servers are in different domains then a two-way trust must be setup between domains.
• SPN must be successfully registered for the SQL Server Service to be identified on the network.

Different Ways to Verify SPN has been successfully registered for SQL Server Authentication with Kerberos Connections

• Using SETSPN Command Line Utility
• Using Active Directory Service Interfaces Editor (ADSIEdit.msc)

Verify SPN has been successfully registered Using SETSPN Command Line Utility

In Command Line enter the following command: setspn -L <Domain\SQL Service Account Name> and press enter. Next, you need to look for registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.

Registered ServicePrincipalNames for CN=SQLServiceAccountName,OU=SQL,OU=Service Accounts,OU=Admin Roles,DC=SGP,DC=mytechmantra,DC=com:

Error Message: When SPN is not configured correctly for SQL Server Service

If SPN is not configured correctly then you will see the below mentioned error message in command line.

FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account ServiceAccount

Verify SPN has been successfully registered by reading SQL Server Error Log

If SPN is not registered successfully for the SQL Server Service then you will see the below mentioned warning message within the SQL Server Error Logs. You can search for the same in SQL Server Error Log file using the filtering option which is available in Log File Viewer.

The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. 

How to manually create a domain user Service Principle Name (SPN) for the SQL Server Service Account

A Domain Administrator can manually set the SPN for the SQL Server Service Account using SETSPN.EXE utility. However, to create the SPN, one must use the can use the NetBIOS name or Fully Qualified Domain Name (FQDN) of the SQL Server. SPN must be created for both the NetBIOS name and the FQDN. In case if you are creating for a Clustered SQL Server then specify the virtual name of the SQL Server Cluster as the SQL Server computer name. It is assumed that you are running SQL Server on the default port which is 1433. If you have configured to use SQL Server under a different port then specify that port number.

Create SPN for NetBIOS name of SQL Server

setspn –a MSSQLSvc/<computer name>:1433 <Domain\SQL Server Account>

Create SPN for the FQDN of the SQL Server

setspn -a MSSQLSvc/:1433

How to Automatically register a Service Principle Name (SPN) for the SQL Server Service Account

If you wish to register SPN for SQL Server Account Automatically then refer the following Microsoft Knowledge Base Article titled “How to use Kerberos authentication in SQL Server”.

References

• How to Configure an SPN for SQL Server Site Database Servers
• Register a Service Principal Name for Kerberos Connections
• How to troubleshoot the “Cannot generate SSPI context” error message
• How to use Kerberos authentication in SQL Server
• Download Microsoft Kerberos Configuration Manager for SQL Server – A diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server and SQL Server Reporting Services