MS SQL Server 2017 - Linked Servers mit Windows Authentication

[Capet]

Hallo,

folgende Testumgebung ist vorhanden:

1. Eine Windows-Domäne (testdom) mit einem Domain Controller (Windows Server 2019)
2. zwei SQL Server 2017, "SQL1" und "SQL2" (beide stand-alone, CU20, Windows Server 2019)
3. Auf den SQL-Servern ist "Windows Authentication" aktiv
4. Auf beiden SQL-Servern läuft der SQL-Server-Dienst unter dem gleichen Domänen-User ("testdom\sqlservice")
5. Auf beiden SQL-Servern ist ein identisches Windows-Login ("testdom\SQLAdmin") eingerichtet, welches die Serverrolle "sysadmin" hat

Auf SQL1 soll eine Linked-Server-Verbindung zu SQL2 eingerichtet werden, mit "Data Access". Die Verbindung soll über das Login "testdom\SQLAdmin" laufen. Dieser Account ist in der Linked-Server-Verbindung als "Local Account" eingerichtet und die Option "Impersonate" ist aktiv.

Auf docs.microsoft.com - Linked Servers (Database Engine) heißt es:

Linked servers support Active Directory pass-through authentication when using full delegation. Starting with SQL Server 2017 CU17, pass-through authentication with constrained delegation is also supported; however, resource-based constrained delegation is not supported.

Auf docs.microsoft.com - Configuring Linked Servers for Delegation sind die Voraussetzungen aufgeführt:

• The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2 (hier SQL und SQL2. => ist gegeben, "testdom\SQLAdmin" kann sich auf beiden Servern anmelden
• The user Active Directory property, "Account is sensitive and cannot be delegated", must not be selected. => ist für "testdom\SQLAdmin" gegeben
• The server must have an SPN registered by the domain administrator. => ist für beide SQL-Server gegeben

• setspn -l SQL1
  Registered ServicePrincipalNames for CN=SQL1,OU=SQLServer,DC=testdom,DC=de:
          WSMAN/SQL1
          WSMAN/SQL1.testdom.de
          RestrictedKrbHost/SQL1
          HOST/SQL1
          RestrictedKrbHost/SQL1.testdom.de
          HOST/SQL1.testdom.de
          
  setspn -l SQL2
  Registered ServicePrincipalNames for CN=SQL2,OU=SQLServer,DC=testdom,DC=de:
          WSMAN/SQL2
          WSMAN/SQL2.testdom.de
          RestrictedKrbHost/SQL2
          HOST/SQL2
          RestrictedKrbHost/SQL2.testdom.de
          HOST/SQL2.testdom.de

  
  
• The account under which SQL Server is running must be trusted for delegation. => Der User "testdom\sqlservice" wurde in der Domain Controllers Policy der Policy "Enable computer and user accounts to be trusted for delegation" zugeordnet, ist also gegeben
• The server must be using TCP/IP or named pipes network connectivity. => ist für beide SQL-Server gegeben (TCP/IP)

Ein Verbindungstest für die Linked-Servers-Verbindung von SQL1 an SQL2 scheitert, mit der Meldung "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". Es sieht so aus, dass, obwohl das entsprechende Login "testdom\SQLAdmin" für die Verbindung eingerichtet ist, dieses nicht an SQL2 für die Anmeldung durchgereicht wird.

Irgendetwas übersehe ich bei der Einrichtung. Ich habe für beide Computerobjekte testweise im AD die Option "Trust this computer for delegation to any serivce (Kerberos only)" aktiviert, jedoch ändert dies nichts am Ergebnis.

Irgendetwas übersehe ich bei der Einrichtung. Hat jemand eine Idee?

 

[ando99]

Soweit ich weißt, funktioniert eine externe Kommunikation bei SQLs nur mit einem Domänenbenutzer, also testdom\SQLAdmin es wird aber ein lokaler Benutzer verwendet 'NT AUTHORITY\ANONYMOUS LOGON' , der nicht über den eigenen Rechner hinaus agieren darf

 

[Capet]

Ja, das sagt ja die Meldung aus. Der Domänenbenutzer wird nicht verwendet, ob wohl er eingetragen ist und es grundsätzlich mittels Windows-Authentication funktionieren soll. Mit einem reinen SQL-Benutzer geht das auch, aber das ist halt nicht das, was ich will.

 

[Michael.Scherr]

Hallo,

wenn ich deine Ausgabe von setspn richtig sehe fehlt doch der entsprechende SPN (MSSQLSvc)für den SQL oder nicht? Wichtig ist hier das dieser für den Dienstaccount des SQL Servers gesetzt ist.

mfg

Micha

 

[Capet]

Hallo,

ich glaube, das war ein klassischer Fall von "Wald vor Bäumen nicht gesehen. Die Ausgabe von setspn ist Unsinn, da hast du Recht. Benötigt wird die Ausgabe für den Serviceuser:

 setspn -l sqlservice
Registered ServicePrincipalNames for CN=sqlservice,CN=Users,DC=testdom,DC=de:
        HOST/sqlservice
        HOST/sqlservice.testdom.de
        MSSQLSvc/SQL1.testdom.de:INSTANZ1
        MSSQLSvc/SQL1.testdom.de:44444
        MSSQLSvc/SQL2.testdom.de:INSTANZ2
        MSSQLSvc/SQL2.testdom.de:44445

Hier sind auch die SPNs für beide SQL-Server und die jeweils darauf laufende Instanz dem user "sqlservice" zugeordnet, unter dem auf beiden Servern der SQL-Server läuft, einmal mit dem Namen der Instanz und einmal mit dem Port, auf dem die Instanz läuft.

Die SPNs werden jeweils manuell für den user registriert. Eine automatische Registrierung durch den user selbst ist zwar möglich, jedoch wird die Vergabe der dafür notwendigen Rechte seitens Microsoft nicht empfohlen (Sicherheitsrisiko).

Ergänzung (17. Juni 2020)

So, ich konnte das Problem jetzt lösen, nachdem ich mir das Ganze im "Kerberos Configuration Manager for SQL Server" angesehen habe. Dort stand für den Serviceuser weiterhin noch, dass dem user nicht für die Delegierung vertraut wird.

Dem user muss im AD dieses Recht noch explizit gegeben werden. Aus Sicherheitsgründen wird es beschränkt auf Kerberos, die beiden SQL-Server und den darauf laufenden SQL-Server-Dienst. Die im ersten Beitrag erwähnte Policy auf dem Domänen-Controller ist damit hinfällig.

Damit funktioniert die Linked-Server-Verbindung jetzt auch, wie sie soll.

 

[Michael.Scherr]

Hallo,

das die Option „Use Any Authentication Protocol“ nicht wirkt hatte ich auch, hier musste man wie in deinem Bild die entsprechenden Dienste manuell hinzufügen.

Mit freundlichen Grüßen

Micha