How To Check If Spn Is Registered In Sql Server

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 Chief Proper noun (SPN) as:- "SPN is the name past which a client uniquely identifies an case of a service. If you install multiple instances of a service on computers throughout a forest, each instance must have its ain SPN. For case, an SPN always includes the proper name of the host reckoner on which the service instance is running, and 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 cosign 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 Commodity: Service Principal Names

It is always recommended to run SQL Server Services under a Domain User Account which has minimal permissions. If y'all are looking for different means to secure SQL Server within your environment then read the following "SQL Server Security Best Practices" commodity.

TSQL Query to verify SQL Server/Windows Hallmark scheme used past SQL Server Connectedness

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

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

Expected Results

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

Prerequisites when configuring SQL Server to utilise 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 betwixt domains.
SPN must exist 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 printing 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:

Mistake Message: When SPN is non configured correctly for SQL Server Service

If SPN is not configured correctly then y'all 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 Fault Log

If SPN is non registered successfully for the SQL Server Service then y'all will see the below mentioned alert message within the SQL Server Fault Logs. You lot can search for the same in SQL Server Error Log file using the filtering option which is bachelor in Log File Viewer.

              The SQL Network Interface library could not annals the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, land: 15. Failure to register an SPN may crusade 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 Proper noun (SPN) for the SQL Server Service Account

A Domain Administrator tin manually set the SPN for the SQL Server Service Account using SETSPN.EXE utility. However, to create the SPN, one must use the tin can utilize the NetBIOS name or Fully Qualified Domain Name (FQDN) of the SQL Server. SPN must exist created for both the NetBIOS name and the FQDN. In instance 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 lot take configured to utilize SQL Server under a different port so 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 Business relationship

If you wish to annals SPN for SQL Server Account Automatically and 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 Managing director for SQL Server" which is a diagnostic tool.

This tool will help DBAs to troubleshoot Kerberos related connectivity problems with SQL Server, SQL Server Assay 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®

SQL Delete Duplicate Rows from a SQL Table in SQL Server
FORMAT SQL Server Dates Using FORMAT Function in SQL Server
SQL Server SELECT DISTINCT Clause T-SQL Tutorial with Examples

References

How to Configure an SPN for SQL Server Site Database Servers
Register a Service Chief Name for Kerberos Connections
How to troubleshoot the "Cannot generate SSPI context" fault message
How to use Kerberos hallmark in SQL Server
Download Microsoft Kerberos Configuration Director for SQL Server – A diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server and SQL Server Reporting Services

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than 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 beyond leading SQL Server technology portals. Over the terminal few years, he has also adult and delivered many successful projects in database infrastructure; information warehouse and business intelligence; database migration; and upgrade projects for companies such equally Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, Great britain. He holds an engineering caste in estimator science and manufacture standard certifications from Microsoft including MCITP Database Ambassador 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.