Service Principal Name Configuration Status

Summary

This monitor checks the configuration of Service Principal Name (SPN) of Microsoft® SQL Server™ instance. This is performed by comparing SQL Server service and Domain Controller configurations.

Note that the monitor is always in "Healthy" state for non-domain joined machines.

Causes

A Service Principal Name (SPN) for the SQL Server Database Engine may either be missing, misplaced, or is a duplicate to other SPNs configured in the Active Directory of the domain.

Note: A misplaced SPN is a SPN that is configured on the wrong account in the Active directory.

Resolutions

The setspn.exe and klist.exe programs could be used to resolve the issue.

Both tools ship with Windows Server 2008 and later. For previous Windows versions you may need to download these separately from the Microsoft Download Center or obtain it from the Windows Support Tools package.

Case 1: How to resolve a Missing SPN:

1. Run the following command to add the missing SPN:

setspn –S <SPN> <Account>

2. Try reconnecting to SQL Server with your client application.

You can also use /S or –A parameter for setspn command. Though the latter is deemed to be deprecated, it can still work in older Windows Server versions (2012, 2012 R2).

Note that in case of using a virtual account (like Local System or NT Service\MSSQL Server), <Account> value for setspn command should be presented by the computer hostname.

Alternatively, you could provide the proper permissions to the SQL Service Account to allow SQL to auto generate the SPNs needed.

Note: A Missing SPN may not result in a connectivity failure but will prevent the application from using Kerberos authentication.

Case 2: How to resolve a Misplaced SPN:

1. Run the following command to remove the misplaced SPN:

setspn –D <SPN> <Account>

2. On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command:

klist purge

3. Try reconnecting to SQL Server with your client application.

Note: If you wish to use Kerberos from your application you need to configure the correct SPN using the resolution from Case 1 above.

Case 3: How to resolve a duplicate SPN:

1. Identify the SPNs that are duplicate and must be removed.

2. Run the following command to remove each of the duplicate SPNs:

setspn –D <SPN> <Account>

3. On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command:

klist purge

4. Try reconnecting to SQL Server with your client application.

External

You may experience connectivity issues to SQL Server if SPNs are misconfigured

Overrideable Parameters