This monitor checks the status of the Microsoft SQL Server instance Service Principal Name configuration.
Note that the monitor is always in "Healthy" state for non-domain joined machines.
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.
Note that the monitor is always in "Healthy" state for non-domain joined machines.
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 –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.
Name | Description | Default Value |
Alert Priority | Defines Alert Priority. | Normal |
Alert Severity | Defines Alert Severity. | Warning |
Enabled | Enables or disables the workflow. | Yes |
Generates Alerts | Defines whether the workflow generates an Alert. | Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 900 |
Scope of search | Use LDAP search when the scope of a search is the domain or an organizational unit. When the scope of a search is the forest, the query can be resolved within any partition by using a Global Catalog (GC) search. List of values: LDAP GC | LDAP |
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>
<SearchScope>LDAP</SearchScope>
</Configuration>
</UnitMonitor>