Kerberos do SQL Servera: SPN, stały port i nazwa DNS

Serwer bazodanowy z kilkoma instancjami nazwanymi działa od miesięcy, aplikacje łączą się kontami domenowymi i nikt nie zgłasza problemów. Przy konfiguracji serwera połączonego (linked server), który ma przekazać tożsamość użytkownika do drugiej instancji, zapytanie kończy się błędem Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Przegląd połączeń pokazuje przyczynę: wszystkie sesje używają NTLM, a NTLM nie pozwala przekazać tożsamości dalej.

Uwierzytelnianie Windows w SQL Serverze nie przestaje działać, gdy Kerberos zawodzi. Klient po cichu przechodzi na NTLM, a problem ujawnia dopiero delegacja, audyt albo ograniczenie NTLM w domenie. Kerberos działa wtedy, gdy zgadzają się trzy elementy: nazwa, pod którą łączy się klient, port instancji i SPN zarejestrowany na koncie usługi. Ten wpis pokazuje, jak je ze sobą zgrać.

Jak klient wybiera Kerberos albo NTLM

Przy uwierzytelnianiu Windows sterownik klienta prosi kontroler domeny o bilet dla usługi o określonej nazwie, czyli SPN (Service Principal Name). Kontroler szuka w katalogu konta, które ma ten SPN w atrybucie servicePrincipalName, i szyfruje bilet kluczem tego konta. SQL Server odszyfrowuje bilet kluczem konta, na którym działa usługa. Dla SQL Servera SPN ma jedną z dwóch postaci:

MSSQLSvc/sql-crm.corp.example.com:14331     połączenia TCP: nazwa i port
MSSQLSvc/srv-sql-01.corp.example.com:CRM    instancja nazwana przez potoki nazwane

Sterownik nie bierze nazwy wprost z ciągu połączenia. Najpierw rozwiązuje ją w DNS i używa nazwy kanonicznej, a port bierze z ciągu połączenia albo od usługi SQL Browser, gdy klient łączy się przez serwer\INSTANCJA. Jeśli kontroler domeny nie znajdzie SPN o tak zbudowanej nazwie, mechanizm Negotiate wybiera NTLM bez żadnego komunikatu. Połączenie działa, ale bez wzajemnego uwierzytelnienia serwera i bez delegacji, więc serwery połączone i aplikacje wielowarstwowe przekazują dalej logowanie anonimowe.

Objaw	Najczęstsza przyczyna
auth_scheme równe NTLM przy połączeniu z innej maszyny	brak SPN dla nazwy i portu, których używa klient
Cannot generate SSPI context	SPN zarejestrowany na innym koncie niż konto usługi albo ten sam SPN na dwóch kontach
NTLM tylko przy połączeniu przez alias	alias jest rekordem CNAME albo nie ma SPN dla nazwy aliasu
NTLM przy połączeniu po adresie IP	zachowanie domyślne: dla adresu IP Windows nie używa Kerberosa
NTLM po restarcie instancji	port dynamiczny inny niż port w zarejestrowanym SPN
ANONYMOUS LOGON na serwerze połączonym	pierwsze połączenie uwierzytelnione przez NTLM albo brak skonfigurowanej delegacji

Konto usługi

Klucz do odszyfrowania biletu ma konto, na którym działa usługa SQL Server, więc SPN musi być zarejestrowany właśnie na nim. Najlepiej sprawdza się grupowe zarządzane konto usługi (gMSA): hasło zmienia kontroler domeny, nikt go nie zna, a jedno konto może obsługiwać instancje na kilku serwerach. W przykładach w tym wpisie wszystkie instancje działają na koncie CORP\svc-sql$. Tworzenie i wdrażanie konta opisuje piąta część przewodnika, gMSA jako konto usługi SQL Servera.

Dlaczego SQL Server nie rejestruje SPN sam

Przy starcie usługa SQL Server próbuje zarejestrować swoje SPN na koncie usługi, a przy zatrzymaniu je usunąć. Udaje się to tylko wtedy, gdy konto ma prawo odczytu i zapisu atrybutu servicePrincipalName na samym sobie. Ani konto gMSA, ani zwykłe konto domenowe domyślnie go nie ma, więc w dzienniku błędów SQL Servera pojawia się wpis:

The SQL Server Network Interface library could not register the Service Principal Name (SPN)
[ MSSQLSvc/srv-sql-01.corp.example.com:CRM ] for the SQL Server service. Windows return code: 0x2098,
state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

Kod 0x2098 oznacza brak uprawnień w katalogu. Prawo do rejestracji automatycznej nadaje polecenie:

dsacls 'CN=svc-sql,CN=Managed Service Accounts,DC=corp,DC=example,DC=com' /G 'SELF:RPWP;servicePrincipalName'

Rejestracja automatyczna ma jednak ograniczenia, przez które przy instancjach nazwanych lepiej sprawdza się rejestracja ręczna:

Cecha	Rejestracja automatyczna	Rejestracja ręczna
Wymagane prawa	konto usługi zapisuje własny atrybut servicePrincipalName	administrator rejestruje SPN raz, konto usługi nie potrzebuje dodatkowych praw
Nazwa w SPN	tylko nazwa serwera	dowolna nazwa z rekordem A, także nazwa instancji
Port dynamiczny	obsługiwany, SPN odświeżany przy każdym starcie	wymaga stałego portu
Ryzyko	konto usługi może dopisać sobie dowolny SPN, także należący do innej usługi	brak

Stały port zamiast dynamicznego

Instancja nazwana domyślnie nasłuchuje na porcie dynamicznym. SQL Server wybiera go przy pierwszym starcie i próbuje go zachować, ale po restarcie port może się zmienić, jeśli zajmie go inny proces. Klienci odnajdują port przez SQL Browser (UDP 1434), więc połączenie nadal działa, ale ręcznie zarejestrowany SPN i reguła zapory przestają pasować.

Stały port ustawia się w SQL Server Configuration Manager: SQL Server Network Configuration, Protocols for CRM, TCP/IP, zakładka IP Addresses, sekcja IPAll (przy domyślnym ustawieniu Listen All na Yes). Pole TCP Dynamic Ports musi być puste, a TCP Port zawiera numer portu. To samo przez PowerShell:

$instance = 'CRM'
# Identyfikator instancji, np. MSSQL16.CRM, odczytany z rejestru
$id = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$instance
$ipAll = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$id\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"

Set-ItemProperty -Path $ipAll -Name TcpDynamicPorts -Value ''
Set-ItemProperty -Path $ipAll -Name TcpPort -Value '14331'

# Nowy port działa dopiero po restarcie usługi
Restart-Service -Name 'MSSQL$CRM' -Force

Klienci łączący się przez nazwa,port nie potrzebują usługi SQL Browser. Jest ona potrzebna tylko dla połączeń w postaci serwer\INSTANCJA.

Własna nazwa DNS dla instancji

Kilka instancji na jednym serwerze można udostępniać pod nazwą serwera z różnymi portami. Wygodniejsza jest jednak osobna nazwa dla każdej instancji. Aplikacja nie zna wtedy nazwy serwera, a przeniesienie instancji na inny serwer sprowadza się do zmiany rekordu DNS. Gdy oba serwery korzystają z tego samego konta gMSA, SPN również zostaje bez zmian.

Instancja	Nazwa DNS (rekord A)	Port TCP	SPN
CRM	sql-crm.corp.example.com	14331	MSSQLSvc/sql-crm.corp.example.com:14331
ERP	sql-erp.corp.example.com	14332	MSSQLSvc/sql-erp.corp.example.com:14332
HR	sql-hr.corp.example.com	14333	MSSQLSvc/sql-hr.corp.example.com:14333

# Wszystkie nazwy wskazują adres serwera srv-sql-01
'sql-crm', 'sql-erp', 'sql-hr' | ForEach-Object {
    Add-DnsServerResourceRecordA -ZoneName corp.example.com -Name $_ -IPv4Address 10.10.20.11 -ComputerName dc01.corp.example.com
}

Ciąg połączenia aplikacji zawiera wtedy pełną nazwę i port, na przykład Server=sql-crm.corp.example.com,14331.

Ręczna rejestracja SPN

SPN rejestruje się poleceniem setspn na koncie usługi. Przełącznik -S przed dodaniem sprawdza, czy taki SPN nie istnieje już na innym koncie, i w takim przypadku odmawia. Rejestracja wymaga uprawnień administratora domeny albo delegowanego prawa zapisu servicePrincipalName na koncie usługi.

setspn -S MSSQLSvc/sql-crm.corp.example.com:14331 'CORP\svc-sql$'
# Tylko gdy klienci łączą się przez potoki nazwane
setspn -S MSSQLSvc/srv-sql-01.corp.example.com:CRM 'CORP\svc-sql$'

Pozostałe przełączniki służą do kontroli: -L wyświetla SPN konta, -Q wskazuje konto, które ma dany SPN, a -X wyszukuje duplikaty w domenie. Przy przenoszeniu instancji na serwer z innym kontem usługi SPN najpierw usuwa się ze starego konta (setspn -D), a dopiero potem dodaje do nowego.

Delegacja dla serwerów połączonych

Kerberos jest warunkiem koniecznym scenariusza z początku wpisu, ale nie wystarczającym. Gdy instancja CRM wykonuje zapytanie na serwerze połączonym w instancji ERP w imieniu użytkownika, konto usługi CRM musi mieć prawo przekazania jego tożsamości do usługi ERP. Bezpieczny wariant to delegacja ograniczona tylko z protokołem Kerberos: konto usługi może delegować wyłącznie do wymienionych SPN i wyłącznie na podstawie biletu, który przedstawił użytkownik.

# Konto usługi może przekazywać tożsamość użytkowników tylko do instancji ERP
Set-ADServiceAccount -Identity svc-sql -Add @{ 'msDS-AllowedToDelegateTo' = 'MSSQLSvc/sql-erp.corp.example.com:14332' }

Wpis w msDS-AllowedToDelegateTo bez flagi TrustedToAuthForDelegation oznacza delegację tylko przez Kerberos, bez przejścia protokołu. Login serwera połączonego musi używać bieżącego kontekstu zabezpieczeń użytkownika: w SSMS to opcja „Be made using the login’s current security context”, w T-SQL sp_addlinkedsrvlogin z @useself = 'TRUE'. Tożsamości nie da się przekazać dla kont z ustawieniem „Account is sensitive and cannot be delegated” ani dla członków grupy Protected Users, co dla kont administracyjnych jest zamierzone.

Nie należy w zamian włączać delegacji nieograniczonej (w konsoli: Trust this user for delegation to any service). Konto usługi mogłoby wtedy występować w imieniu każdego zalogowanego użytkownika wobec dowolnej usługi w domenie.

Zapora przez GPO

Przy stałych portach zapora na serwerach SQL potrzebuje prostych reguł portowych zamiast reguł programowych dla pliku sqlservr.exe każdej instancji. Reguły najlepiej utrzymywać w jednym GPO podpiętym pod jednostkę organizacyjną serwerów SQL. Polecenia z modułu NetSecurity zapisują je bezpośrednio w GPO, gdy parametr -PolicyStore wskazuje domenę i nazwę obiektu:

$gpo = 'corp.example.com\SQL Servers - Firewall'

New-NetFirewallRule -PolicyStore $gpo -DisplayName 'SQL Server - instancje (TCP 14331-14333)' `
    -Direction Inbound -Protocol TCP -LocalPort '14331-14333' -Action Allow -Profile Domain

# Tylko gdy klienci łączą się przez serwer\INSTANCJA
New-NetFirewallRule -PolicyStore $gpo -DisplayName 'SQL Server Browser (UDP 1434)' `
    -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -Profile Domain

Parametr -RemoteAddress zawęża regułę do podsieci, w których działają aplikacje. Odczyt reguł z GPO działa tak samo: Get-NetFirewallRule -PolicyStore $gpo. Parametr -GPOSession przyjmuje sesję otwartą wcześniej przez Open-NetGPO, a nie nazwę GPO, dlatego do pojedynczych operacji prostszy jest -PolicyStore.

Sprawdź u siebie

SPN konta usługi, właściciel konkretnego SPN i duplikaty w domenie. Dobry wynik to jeden SPN MSSQLSvc/nazwa:port na każdą nazwę i port używane przez klientów, Existing SPN found! przy właściwym koncie oraz found 0 group of duplicate SPNs w wyniku -X.

setspn -L 'CORP\svc-sql$'
setspn -Q MSSQLSvc/sql-crm.corp.example.com:14331
setspn -X

Test z komputera klienckiego, nie z serwera SQL: połączenie z samego serwera pokazuje NTLM także przy poprawnym SPN. klist get prosi kontroler domeny o bilet dla SPN instancji, a błąd oznacza, że biletu nie wydano, najczęściej z powodu braku SPN. Zapytanie przez sqlcmd powinno zwrócić KERBEROS, TCP i port instancji.

klist get MSSQLSvc/sql-crm.corp.example.com:14331

# -E: uwierzytelnianie Windows; -C: zaufanie certyfikatowi serwera, gdy jest samopodpisany
sqlcmd -S sql-crm.corp.example.com,14331 -E -C -Q "SELECT auth_scheme, net_transport, local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;"

Bieżące połączenia TCP z uwierzytelnianiem Windows oraz wpisy o SPN w bieżącym dzienniku SQL Servera. Każdy wiersz z NTLM z innej maszyny to klient, który łączy się nazwą lub portem bez SPN, przez alias CNAME albo po adresie IP. W dzienniku successfully registered oznacza działającą rejestrację automatyczną, a kod 0x2098 przy ręcznie zarejestrowanych SPN jest oczekiwany.

SELECT s.login_name, s.host_name, c.client_net_address, c.auth_scheme, c.local_tcp_port
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE c.net_transport = 'TCP' AND c.auth_scheme <> 'SQL'
ORDER BY c.auth_scheme, s.host_name;

EXEC xp_readerrorlog 0, 1, N'SPN';

Ustawienia delegacji konta usługi. Dobry wynik to False w TrustedForDelegation i TrustedToAuthForDelegation oraz w msDS-AllowedToDelegateTo wyłącznie SPN instancji, do których naprawdę trzeba przekazywać tożsamość. True w TrustedForDelegation oznacza delegację nieograniczoną do wyłączenia.

Get-ADServiceAccount -Identity svc-sql -Properties TrustedForDelegation, TrustedToAuthForDelegation, 'msDS-AllowedToDelegateTo' |
    Select-Object Name, TrustedForDelegation, TrustedToAuthForDelegation, 'msDS-AllowedToDelegateTo'