SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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


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

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

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

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”.

Microsoft Kerberos Configuration Manager for SQL Server

Microsoft of recently released a downloaded utility name “Microsoft Kerberos Configuration Manager for SQL Server” which is a diagnostic tool.

This tool will help DBAs to troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Analysis Services, and SQL Server Reporting Services.

We would recommend you to download and install this tool to resolve SPN related issue on your servers.

Download Link: Microsoft® Kerberos Configuration Manager for SQL Server®


Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals

Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.