This monitor checks the status of the Microsoft SQL Server instance Service Principal Name configuration.
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.
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.
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.
You may experience connectivity issues to SQL Server if SPNs are misconfigured
Name | Description | Default Value |
Enabled |
| Yes |
Generates Alerts |
| Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 900 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Target | Microsoft.SQLServer.2014.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.2014.DBEngine.Configuration.SPNStatus | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2014.DBEngine.Configuration.SPNStatusMonitor" Accessibility="Public" Enabled="true" Target="SQL2014Core!Microsoft.SQLServer.2014.DBEngine" ParentMonitorID="SystemHealth!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2014.DBEngine.Configuration.SPNStatus" ConfirmDelivery="false">
<Category>AvailabilityHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2014.DBEngine.Configuration.SPNStatusMonitor.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Warning</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.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="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</InstanceName>
<Account>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/Account$</Account>
<ServiceName>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</ServiceName>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>