SQL Server und Kerberos: Bitte nicht die Service Principal Names vergessen

Bei der Nutzung der Windows-Authentifizierung für eine Verbindung zu einem Microsoft SQL Server ist Kerberos die erste Wahl. Erst wenn die Nutzung dieses Authentifizierungsschemas nicht möglich ist, wird auf NTLM (NT LAN Manager) zurückgegriffen. Für den Benutzer ist das transparent und NTLM bedeutet in den meisten Fällen keine Einschränkung. Daher fällt es in vielen Umgebungen gar nicht auf, dass Kerberos nicht korrekt konfiguriert ist und daher nicht genutzt werden kann.  

Wird Kerberos genutzt?

Kerberos wird nur bei TCP/IP-Verbindungen und nur bei der Authentifizierung mit einem Domänen-Konto verwendet. In einer so hergestellten Verbindung können Sie das Authentifizierungsschema mit dem folgenden Kommando ermitteln:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID 

Wird hier „KERBEROS“ zurückgegeben, ist alles in Ordnung. Bei „NTLM“ lesen Sie bitte weiter, denn dann konnte Kerberos nicht verwendet werden.

Als Nutzer des PowerShell-Moduls dbatools können Sie zur Ermittlung auch den Befehl Test-DbaConnection verwenden.  

Gibt es den passenden Service Principal Name?

Sollte Kerberos nicht genutzt werden, fehlen meist die dafür benötigten Service Principal Names (SPN) im Active Directory. Denn der SQL Server versucht zwar bei jedem Start, die SPNs selbst anzulegen. In vielen Fällen fehlen aber die notwendigen Rechte.

Bei der Nutzung eines lokalen Dienstkontos für den SQL Server wird die Erstellung der SPNs über das Computerkonto durchgeführt und dieses besitzt typischerweise die erforderlichen Rechte im Active Directory. Bei der Nutzung eines Domänenbenutzers als Dienstkonto wird jedoch in vielen Fällen vergessen, diesem die benötigten Rechte zu erteilen. Welche SPNs zum verwendeten Domänenbenutzer registriert wurden, kann über den Befehl „setspn.exe“ mit dem Parameter „-L“ ermittelt werden. Alternativ kann der Befehl Test-DbaSpn aus dem PowerShell-Modul dbatools genutzt werden.

Neben der automatischen Erstellung der SPNs gibt es auch die Möglichkeit, die SPNs durch einen Administrator manuell im Active Directory anlegen zu lassen. Auch hier hilft der Befehl „setspn.exe“ oder das PowerShell-Modul dbatools mit dem Befehl Set-DbaSpn. Gerade bei der Nutzung eines Domänenbenutzers, der als Dienstkonto für mehrere SQL Server Instanzen verwendet wird, muss dann aber bei jeder neuen Installation auch wieder an die Erstellung der SPNs gedacht werden. Daher lautet unsere Empfehlung in diesem Fall, das Konto mit den erforderlichen Rechten auszustatten und den SQL Server die notwendigen SPNs selbst anlegen zu lassen.

Best Practice Konfiguration

Um dem als Dienstkonto genutzten Domänenbenutzer die erforderlichen Rechte zu geben, ist lediglich dieser PowerShell-Befehl als Active Directory Administrator auszuführen:

dsacls.exe (get-ADUser -Identity <Name des Dienstkontos hier einfügen>).DistinguishedName /G "SELF:RPWP;servicePrincipalName"

Die Dokumentation des Programms dsacls finden Sie hier. Der Parameter „/G“ steht für das Erteilen von Rechten (GRANT), „SELF“ bezieht sich auf das angegebene Konto selbst, „RP“ und „WP“ stehen für das Lesen (Read) sowie das Schreiben (Write) einer Eigenschaft (Property), „servicePrincipalName“ benennt die entsprechende Eigenschaft.

Den Erfolg testen

Nach dem Neustart des Dienstes sollte der SQL Server nun in der Lage sein, bei jedem Start die benötigten SPNs selbst zu registrieren. Kontrollieren können Sie das in der Windows Ereignisanzeige oder in der Datei ERRORLOG. Ich nutze sehr gerne PowerShell zum Zugriff auf die Ereignisanzeige. Daher hier als Beispiel der Befehl, um von der Standardinstanz auf dem Computer SQL01 alle Meldungen zu erhalten, in denen der Begriff „SPN“ auftaucht:

Get-EventLog -ComputerName SQL01 -LogName Application -Source MSSQLSERVER -Message *SPN*

Eine Übersicht erstellen

Um über das gesamte Active Directory hinweg eine Übersicht über alle mit dem SQL Server in Zusammenhang stehenden SPNs zu erhalten, kann dieser PowerShell-Code genutzt werden: 

$adObjectWithSQLServerSPN = 
    (Get-ADComputer -Filter '*' -Properties servicePrincipalNames) + 
    (Get-ADUser -Filter '*' -Properties servicePrincipalNames) | 
    Where-Object servicePrincipalNames -match ^MSSQLSvc |
    ForEach-Object {
        [PSCustomObject]@{
            ObjectClass       = $_.ObjectClass
            Name              = $_.Name
            DistinguishedName = $_.DistinguishedName
            SQLServerSPNs     = $_.ServicePrincipalNames | Where-Object { $_ -match '^MSSQLSvc' }
        }
    }
$adObjectWithSQLServerSPN | Out-GridView
 

Fazit

Mit der richtigen Konfiguration des Dienstkontos kann die automatische Erstellung von SPNs durch den SQL Server genutzt werden, um bei allen Verbindungen automatisch Kerberos nutzen zu können. 

Seminarempfehlung