3 Minuten Lesezeit (651 Worte)

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

Principal Consultant bei ORDIX

 

Kommentare 4

Schönen guten Tag,

erst mal ein guter Beitrag habe nach einer derartig zusammengefassten Information vergeblich gesucht.

Mein Fall gestaltet sich allerdings ein wenig komplexer denke ich (aber hoffe nicht).

Ich benötige Kerberos oder NTLM Authentifizierung im SQL-Sever um mich mit eingetragenen LinkedServern zu authentifizieren (die aber eigentlich Excel-Dateien sind und auf Dateisystem-Ebene liegen).

Hatte das Problem umgangen in dem ich die SQL-Server Dienste mit benötigten Benutzern habe laufen lassen, was keine Lösung ist da andere Datenbanken auf Basis von anderen Nutzern laufen müssen um korrekt zu arbeiten.

Und selbst dann scheitert es in einem weiterführenden System, wenn SQL-Abfragen gegenüber dem LinkedServer durchgeführt werden auf Grund einer gescheiterten Windows Authentifizierung.

Müssen wir Kerberos dafür nutzen oder NTLM?

Obwohl ich seit vielen Jahren mit Microsoft SQL Servern arbeite und diese Einrichte bin ich noch nie in eine derartige Situation gekommen.

Schönen guten Tag, erst mal ein guter Beitrag habe nach einer derartig zusammengefassten Information vergeblich gesucht. Mein Fall gestaltet sich allerdings ein wenig komplexer denke ich (aber hoffe nicht). Ich benötige Kerberos oder NTLM Authentifizierung im SQL-Sever um mich mit eingetragenen LinkedServern zu authentifizieren (die aber eigentlich Excel-Dateien sind und auf Dateisystem-Ebene liegen). Hatte das Problem umgangen in dem ich die SQL-Server Dienste mit benötigten Benutzern habe laufen lassen, was keine Lösung ist da andere Datenbanken auf Basis von anderen Nutzern laufen müssen um korrekt zu arbeiten. Und selbst dann scheitert es in einem weiterführenden System, wenn SQL-Abfragen gegenüber dem LinkedServer durchgeführt werden auf Grund einer gescheiterten Windows Authentifizierung. Müssen wir Kerberos dafür nutzen oder NTLM? Obwohl ich seit vielen Jahren mit Microsoft SQL Servern arbeite und diese Einrichte bin ich noch nie in eine derartige Situation gekommen.
Andreas Jordan am Dienstag, 25. Juli 2023 09:11

Guten Tag Herr Fleischer,

vielen Dank für Ihren Kommentar. Es freut mich, dass Ihnen der Beitrag gefällt.

Ich arbeite sehr selten mit Linked Servern und bisher noch nicht in Kombination mit dem Zugriff auf Excel-Dateien. Daher kann ich Ihre Frage leider nicht aus dem Stand heraus beantworten. Eine kurze Recherche hat auch keine Lösung ergeben, die ich Ihnen empfehlen kann.

Gerne kann ich die Situation bei Ihnen analysieren und wir können gemeinsam eine für Sie geeignete Lösung erarbeiten. Bei Interesse wenden Sie sich bitte an unseren Vertrieb: https://www.ordix.de/kontakt

Viele Grüße
Andreas Jordan

Guten Tag Herr Fleischer, vielen Dank für Ihren Kommentar. Es freut mich, dass Ihnen der Beitrag gefällt. Ich arbeite sehr selten mit Linked Servern und bisher noch nicht in Kombination mit dem Zugriff auf Excel-Dateien. Daher kann ich Ihre Frage leider nicht aus dem Stand heraus beantworten. Eine kurze Recherche hat auch keine Lösung ergeben, die ich Ihnen empfehlen kann. Gerne kann ich die Situation bei Ihnen analysieren und wir können gemeinsam eine für Sie geeignete Lösung erarbeiten. Bei Interesse wenden Sie sich bitte an unseren Vertrieb: https://www.ordix.de/kontakt Viele Grüße Andreas Jordan
Gäste - Richard am Sonntag, 15. Oktober 2023 12:43

Hi Jordan,

Thanks for your great article. I followed it and added the needed permission to my SQL Service account. And now I can see entries like this in the event log.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node1.dev.com:1433 ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node1.dev.com ] for the SQL Server service.

Here is my Test-DbaSPN's output:

Cluster : False
ComputerName : node1.dev.com
DynamicPort : False
Error : None
InstanceName : MSSQLSERVER
InstanceServiceAccount : dev\sqlad
IsSet : True
Port :
RequiredSPN : MSSQLSvc/node1.dev.com
SqlProduct : 13.0.1601.5 Enterprise Edition: Core-based Licensing (64-bit)
TcpEnabled : True
Warning : None

Cluster : False
ComputerName : node1.dev.com
DynamicPort : False
Error : None
InstanceName : MSSQLSERVER
InstanceServiceAccount : dev\sqladm
IsSet : True
Port : 1433
RequiredSPN : MSSQLSvc/node1.dev.com:1433
SqlProduct : 13.0.1601.5 Enterprise Edition: Core-based Licensing (64-bit)
TcpEnabled : True
Warning : None

I opened several connection in SSMS, but I still only see NTLM and no Kerberos. Where am I wrong? Thanks.

Hi Jordan, Thanks for your great article. I followed it and added the needed permission to my SQL Service account. And now I can see entries like this in the event log. The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node1.dev.com:1433 ] for the SQL Server service. The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node1.dev.com ] for the SQL Server service. Here is my Test-DbaSPN's output: Cluster : False ComputerName : node1.dev.com DynamicPort : False Error : None InstanceName : MSSQLSERVER InstanceServiceAccount : dev\sqlad IsSet : True Port : RequiredSPN : MSSQLSvc/node1.dev.com SqlProduct : 13.0.1601.5 Enterprise Edition: Core-based Licensing (64-bit) TcpEnabled : True Warning : None Cluster : False ComputerName : node1.dev.com DynamicPort : False Error : None InstanceName : MSSQLSERVER InstanceServiceAccount : dev\sqladm IsSet : True Port : 1433 RequiredSPN : MSSQLSvc/node1.dev.com:1433 SqlProduct : 13.0.1601.5 Enterprise Edition: Core-based Licensing (64-bit) TcpEnabled : True Warning : None I opened several connection in SSMS, but I still only see NTLM and no Kerberos. Where am I wrong? Thanks.
Andreas Jordan am Dienstag, 17. Oktober 2023 12:31

Hi Richard,

thanks for the feedback! Configuration of the SQL Server instance looks good, I would have a closer look at the client side. Or maybe there are problems with kerberos at the Active Directory level. Can you use kerberos with other services?

Hi Richard, thanks for the feedback! Configuration of the SQL Server instance looks good, I would have a closer look at the client side. Or maybe there are problems with kerberos at the Active Directory level. Can you use kerberos with other services?
Donnerstag, 28. März 2024

Sicherheitscode (Captcha)

×
Informiert bleiben!

Bei Updates im Blog, informieren wir per E-Mail.

Weitere Artikel in der Kategorie