SQL und Kerberos

Für den Zugriff auf SQL-Server ist Kerberos besonders interessant, da über Constraint Delegation dann auch eine Frontend-Applikation, z.B. Webseite, sich als Benutzer ausgeben kann. Im SQL-Log sind damit nicht nur Analysen und Fehlersuche sondern auch Berechtigungen bis auf den Endanwender bzw. dessen Gruppenmitgliedschaften möglich. Dazu ist es aber erforderlich, dass es für den SQL-Server auch einen SPN gibt.

SQL-SPN Namen

Der SPN eines SQL-Servers leitet sich von dem FQDN des Servers und der Instanz bzw. dem Port ab. Die aktuellen Einträge eines Servers können sie per SetSPN abrufen oder auch einfach als LDIFDE-Export

ldifde -f spn.txt -d "CN=SfB01,OU=Server,DC=msxfaq,DC=net" -l servicePrincipalName

Das Ergebnis ist dann die Liste der SPNs dieses Computers.

dn: CN=SfB01,OU=UM-Server,OU=Server,DC=msxfaq,DC=net
changetype: add
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:RTCLOCAL
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:49575
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:LYNCLOCAL
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:49629
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:RTC
servicePrincipalName: MSSQLSvc/SfB01.msxfaq.net:49552
servicePrincipalName: sip/SfB01.msxfaq.net
servicePrincipalName: http/SfB01.msxfaq.net
servicePrincipalName: WSMAN/SfB01
servicePrincipalName: WSMAN/SfB01.msxfaq.net
servicePrincipalName: TERMSRV/SfB01
servicePrincipalName: TERMSRV/SfB01.msxfaq.net
servicePrincipalName: RestrictedKrbHost/SfB01
servicePrincipalName: HOST/SfB01
servicePrincipalName: RestrictedKrbHost/SfB01.msxfaq.net
servicePrincipalName: HOST/SfB01.msxfaq.net

Sie sehen hier am Beispiel eines Skype Business Standard Server schon, dass ein Computer neben dem Basiseintrag "HOST" auch Einträge für Webserver, SIP-Server, Terminaldienste aber auch MSSQL hat. Nur mit diesen Einträgen kann SQL auch per Kerberos angesprochen werden, da die Clients vom Kerberos Distribution Center ein Ticket bekommen können-

Nebenbei können Sie über diese SPNs per LDAP auch recht einfach zumindest alle Services ermitteln, die so auf Servern installiert sind. Der SPN ist von jedem Benutzer "lesbar".

SQL-SPN werden vom Server eingetragen

In vielen Dokumentationen wird beschrieben, wie ein Administrator den SPN für einen Dienst manuell eintragen muss. Das ist natürlich auch weiterhin erforderlich, wenn ein virtueller Hostname oder ein Service die Anfragen der Clients bedient, insbesondere wenn der gleiche Service auf mehreren Servern unter dem gleichen Namen ansprechbar ist. Da jeder Computer einen eigenen Kerberos-Key hat, muss dazu ein Dienstkonto oder ein eigenes Computerkonto dazu herhalten.

Für den FQDN des Server selbst aber kann Windows bzw.das Produkt selbst die Eintragung vornehmen. Dies gilt auch für SQL-Server

Beim Starten des Database Engine (Datenbankmodul)-Diensts versucht dieser, den Dienstprinzipalnamen (Service Principal Name, SPN) zu registrieren. Wenn das Konto, das SQL Server startet, nicht über die Berechtigung zum Registrieren eines SPN in den Active Directory-Domänendiensten verfügt, schlägt dieser Aufruf fehl, und im Anwendungsereignisprotokoll sowie im SQL Server-Fehlerprotokoll wird eine Warnmeldung protokolliert. Um den SPN zu registrieren, muss Database Engine (Datenbankmodul) unter einem integrierten Konto, z. B. einem lokalen System (nicht empfohlen) oder NETWORK SERVICE, oder unter einem Konto mit Berechtigung zum Registrieren eines SPN, z. B. einem Domänenadministratorkonto, ausgeführt werden. Wenn SQL Server unter dem Betriebssystem Windows 7 oder Windows Server 2008 R2 ausgeführt wird, können Sie SQL Server mit einem virtuellen Konto oder einem verwalteten Dienstkonto (MSA) ausführen. Sowohl virtuelle Konten als auch MSAs können einen SPN registrieren. Wird unter SQL Server kein solches Konto ausgeführt, wird der SPN beim Starten nicht registriert, sodass der Domänenadministrator den SPN manuell registrieren muss.
Quelle  Registrieren eines Dienstprinzipalnamens für Kerberos-Verbindungen
https://technet.microsoft.com/de-de/library/ms191153(v=sql.110).aspx

Diese Aktionen können Sie im Eventlog kontrollieren:

Log Name: Application
Source: MSSQL$LYNCLOCAL
Event ID: 26076
Task Category: Server
Level: Information
Keywords: Classic
Description:
SQL Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service.
Kerberos authentication will not be possible until a SPN is registered for the SQL Server service.
This is an informational message. No User action is required.
Log Name: Application
Source: MSSQL$LYNCLOCAL
Event ID: 26059
Task Category: Server
Level: Information
Keywords: Classic User: N/A
Computer: SfB01.msxfaq.de
Description:
The SQL Server Network Interface library successfully registered the Service 
PThe SQL Server Network Interface library successfully registered the Service 
Principal Name (SPN) [MSSQLSvc/sfb01.msxfaq.net:LYNCLOCAL ] for the SQL Server service.

SQL mit dynamischen IP-Ports

Dies ist insbesondere dann wichtig, wenn der SQL-Server dynamische Ports verwendet, die sich bei jedem Start wieder ändern können:

Der Port ist auch Bestandteil des SPN.

Fehler bei der SPN-Registrierung

Es ist gar nicht mal so ungewöhnlich, dass der SPN-Eintrag vom Server nicht bearbeitet werden kann. Auch das meldet zumindest der SQL-Server im Eventlog. Es ist allerdings "nur" eine "Information" und kein Fehler oder zumindest eine Warnung. Es gibt aber auch Monitoring-Lösungen und Produkte, die die Existenz des SPN prüfen. So überwacht das SQL-Management-Pack sehr wohl, ob der SPN korrekt gesetzt ist. Dennoch ist eine Überwachung des Eventlogs ebenfalls möglich:

Log Name: Application
Source: MSSQL$LYNCLOCAL
Event ID: 26067
Task Category: Server
Level: Information
Keywords: Classic User: N/A User: N/A
Description:<Description:
The SQL Server Network Interfac e library could not register the Service Principal Name 
(SPN) [ MSSQLSvc/sfb01.msxfaq.net:53735] for the SQL Server service. 
Windows return code: 0x2 098, state: 15. Failure to register a SPN might cause 
integrated authentication to use NTLM instead of Kerberos.
This is an informational message. Further action is only required if Kerberos 
authentication is required by authentication policies and if the SPN has not been manually registered.

Auch ein Fehler beim Entfernen eines SPN beim Herunterfahren wird gemeldet:

Log Name: Application
Source: MSSQL$LYNCLOCALEvent ID: 26068
Task Category: Server
Level: Information
Keywords: Classic User: N/A
Description:< /p> 
The SQL Server Network InterfaThe SQL Server Network Interfac e library could not deregister
 the Service Principal Name (SPN) [ MSSQLSvc/sfb01.msxfaq.net:LYNCLOCAL] for the SQL Server 
service. Error: 0x200b, sta te: 15. Administrator should deregister this SPN manually
to avoid client authentication errors.

Ein SPN "zu viel" schadet eigentlich nicht, da ein Client nur gezielt die SPNs für Dienste anfragt, mit denen er sich verbinden will. So ein verlassener SPN könnte aber natürlich Überwachungsprogramme verwirren, die auf Basis des SPN auch einen Dienst erwarten aber nicht finden.

Meistens sind solche Fehler aber auf mangelnde Berechtigungen zurückzuführen. Wenn z.B. eine SQL-Express-Instanz startet, dann generiert sich auch noch folgenden Eintrag:

Log Name: Application
Source: MSSQL$LYNCLOCAL
Event ID: 49904
Task Category: Server
Level: Information
Keywords: Classic User: The service account is 'MSXFAQ\SfB01$'.
This is an informational message; no User action is required.

Auch wenn der SQL-Server als "NETWORK SYSTEM" läuft und daher reduzierte Rechte hat, erfolgt die Registrierung doch mit dem Computerkonto. Wenn ein Computer oder Dienste die SPNs nicht selbst aktualisieren kann, dann liegt das meist an fehlenden Berechtigungen von "SELF" auf das Computer-Objekt. Siehe auch SPN ACLs.

Weitere Links