MyKE / AD – Service Principal Names registration and troubleshooting

Created Wed, 30 Aug 2017 11:20:14 +0000
416 Words
spn kerberos mssql

AD – Service Principal Names registration and troubleshooting

Article describing registration of Active Directory Service Principal Names (SPN) and troubleshooting.

Registration of SPN’s have 2 most common used variants:

  • Registration of SPN to specific Computer Object (this method is used when serviceis not running under AD domain account like SQL Server, etc.)
  • Registration of SPN to specific Domain Account (this method is used when serviceis running under AD domain account like SQL Server, etc.)

Registration of SPN to specific Computer Object

Error log message with wrong kerberos authentication because of missing SPN for “MSSQLSvc” service running under local account “NT Service\MSSQL$DB01” on a server “DBSRV01.contoso.corp” using Instance “DB01”:

SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.
	Service Account: NT Service\MSSQL$DB01
	Missing SPNs: MSSQLSvc/DBSRV01.contoso.corp:DB01
	Misplaced SPNs:

To FIX this problem logon to server/DC which is in same domain like server DBSRV01.contoso.corp and run following command from CMD/Powershell:

setspn -s "MSSQLSvc/DBSRV01.contoso.corp:DB01" "DBSRV01"

Description of command:

  • setspn.exe -s – add arbitrary SPN after verifying no duplicates exist
  • “ MSSQLSvc/DBSRV01.contoso.corp:DB01” – register SPN for service with name MSSQLSvc on server DBSRV01.contoso.corp running Instance with name DB01.
  • _“ DBSRV01” _– register SPN to this AD Computer Object.

Registration of SPN to specific Domain Account

Error log message with wrong kerberos authentication because of missing SPN for “MSSQLSvc” service running under domain account “CONTOSO\SVC_SQL03” on a server “DBSRV03.contoso.corp” using Instance running on port “1433” and on computer object:

SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.
	Service Account: CONTOSO\SVC_SQL03
	Missing SPNs: MSSQLSvc/DBSRV03.contoso.corp, MSSQLSvc/DBSRV01.contoso.corp:1433
	Misplaced SPNs:
	Duplicate SPNs:

To FIX this problem logon to server/DC which is in same domain like server DBSRV03.contoso.corp and run following command from CMD/Powershell:

setspn -s "MSSQLSvc/DBSRV03.contoso.corp" "CONTOSO\SVC_SQL03"
setspn -s "MSSQLSvc/DBSRV03.contoso.corp:1433" "CONTOSO\SVC_SQL03"

Description of command:

  • setspn.exe -s – add arbitrary SPN after verifying no duplicates exist
  • “ MSSQLSvc/DBSRV03.contoso.corp” – register SPN for service with name MSSQLSvc on server DBSRV03.contoso.corp running Instance on Computer Object .
  • “ MSSQLSvc/DBSRV03.contoso.corp:1433” – register SPN for service with name MSSQLSvc on server DBSRV03.contoso.corp running Instance on port 1433.
  • _“ CONTOSO\SVC_SQL03” _– register SPN to this AD Account.

Troubleshooting

  • The operation failed because SPN value provided for addition/modification is not unique forest-wide.

Same SPN value already exists in a domain during registration of SPN. We need to find duplicate and remove it. Start powershell, import AD module (Import-Module ActiveDirectory) and start search query:

Search query for AD User Account:

Get-ADUser -Filter {serviceprincipalname -like "MSSQLSvc/DBSRV03.contoso.corp:1433"}

Search query for Computer Account:

Get-ADComputer -Filter {serviceprincipalname -like "MSSQLSvc/DBSRV01.contoso.corp:DB01"}