Service Principal Name Configuration Status

Microsoft.SQLServer.2012.DBEngine.Configuration.SPNStatusMonitor (UnitMonitor)

This monitor checks the status of the Microsoft® SQL Server™ instance Service Principal Name configuration.

Knowledge Base article:

Summary

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

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 –A <SPN> <Account>

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

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

Element properties:

Target

Microsoft.SQLServer.2012.DBEngine

Parent Monitor

System.Health.ConfigurationState

Category

AvailabilityHealth

Enabled

True

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2012.DBEngine.Configuration.SPNStatus

Remotable

True

Accessibility

Public

Alert Message

SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.

SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.
Service Account: {0}
Missing SPNs: {1}
Misplaced SPNs: {2}
Duplicate SPNs: {3}

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2012.DBEngine.Configuration.SPNStatusMonitor" Accessibility="Public" Enabled="true" Target="SQL2012Core!Microsoft.SQLServer.2012.DBEngine" ParentMonitorID="SystemHealth!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2012.DBEngine.Configuration.SPNStatus" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2012.DBEngine.Configuration.SPNStatusMonitor.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/Account$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='MissingSpnList']$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='MisplacedSpnList']$</AlertParameter3>

      <AlertParameter4>$Data/Context/Property[@Name='DuplicateSpnList']$</AlertParameter4>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>

    <OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <ComputerNetworkName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerNetworkName>

    <NetbiosComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetbiosComputerName$</NetbiosComputerName>

    <InstanceName>$Target/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</InstanceName>

    <Account>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/Account$</Account>

    <ServiceName>$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</ServiceName>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>