Kerberos on Windows: SPNs, logging and Apache SSO with Active Directory
Check SQL Server Kerberos authentication, manage SPNs and set up Apache Kerberos SSO on Linux.
Quick reference for troubleshooting Kerberos authentication on Windows (SQL Server SPNs, duplicate SPNs, Kerberos logging) and for enabling Active Directory Kerberos single sign-on on an Apache web server running on Linux.
Check which authentication scheme SQL Server uses
When a connection fails with SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security, first check the scheme of an existing connection (KERBEROS or NTLM):
sqlcmd -E -S sqlserver01 -Q "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@SPID"
Inspect SPNs
Search the global catalog for an SPN (example for a SQL Server instance listening on port 14403):
LDIFDE -f check_SPN.txt -t 3268 -d "" -l servicePrincipalName -r "(servicePrincipalName=MSSQLSvc/sqlserver01.contoso.com:14403*)" -p subtree
List the SPNs registered for an account with setspn -l <account>, and look for duplicate SPNs in the forest with:
setspn -x
Enable Kerberos event logging
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\ /v LogLevel /t REG_DWORD /d 0x1
The value is LogLevel (REG_DWORD) = 0x1 under HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters.
Kerberos SSO for Apache on Linux
This procedure follows Apache AD Kerberos (imatviyenko). Fictitious setup used below:
| Item | Value |
|---|---|
| AD domain DNS name | contoso.com (realm CONTOSO.COM) |
| Domain controllers | dc1.contoso.com, dc2.contoso.com |
| Web server FQDN | webserver1.contoso.com |
| Web site URL | http://webserver1.contoso.com |
| AD service account | srvaccount1 (srvaccount1@contoso.com) |
On a domain controller
-
Register the SPN for the web site on the service account:
setspn -s HTTP/webserver1.contoso.com srvaccount1 -
Generate the AES256 key and key version number (vno). Only the values printed on screen are needed; the generated
temp.keytabis not used. Note the key and vno:ktpass /out temp.keytab /princ HTTP/webserver1.contoso.com@CONTOSO.COM -SetUPN /mapuser srvaccount1 /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /pass <PASSWORD> -SetPass +DumpSalt /target dc1.contoso.com -
Do the same for RC4 and note the key and vno:
ktpass /out temp.keytab /princ HTTP/webserver1.contoso.com@CONTOSO.COM -SetUPN /mapuser srvaccount1 /crypto RC4-HMAC-NT /ptype KRB5_NT_PRINCIPAL /pass <PASSWORD> -SetPass +DumpSalt /target dc1.contoso.com -
Create an A record
webserver1.contoso.compointing directly to the IP address of the Linux web server (CNAME aliases bring additional issues with Kerberos).
On the Linux server
Install the packages:
apt install apache2 libapache2-mod-auth-kerb krb5-user
/etc/krb5.conf:
[libdefaults]
default_realm = CONTOSO.COM
default_tkt_enctypes = aes256-cts-hmac-sha1-96 rc4-hmac
default_tgs_enctypes = aes256-cts-hmac-sha1-96 rc4-hmac
permitted_enctypes = aes256-cts-hmac-sha1-96 rc4-hmac
[realms]
CONTOSO.COM = {
kdc = dc1.contoso.com
kdc = dc2.contoso.com
admin_server = dc1.contoso.com
}
[domain_realm]
.contoso.com = CONTOSO.COM
contoso.com = CONTOSO.COM
In /etc/apache2/sites-enabled/000-default.conf, add inside the <VirtualHost> section:
<Location />
AuthType Kerberos
AuthName "Active Directory"
KrbAuthRealms CONTOSO.COM
KrbServiceName HTTP
Krb5Keytab /etc/apache2/kerb.keytab
KrbMethodNegotiate On
KrbMethodK5Passwd Off
require valid-user
</Location>
Build the keytab with ktutil. Replace n with the vno captured from the matching ktpass output, and enter the key as hexadecimal digits WITHOUT the 0x prefix (32 digits for AES256, 16 digits for RC4 as printed by ktpass):
ktutil
list
addent -key -p HTTP/webserver1.contoso.com@CONTOSO.COM -k n -e aes256-cts
addent -key -p HTTP/webserver1.contoso.com@CONTOSO.COM -k n -e RC4-HMAC
list
wkt /etc/apache2/kerb.keytab
q
The first list must be empty; the second one must show two entries (AES256 and RC4). Then fix the permissions and restart Apache:
chown root:www-data /etc/apache2/kerb.keytab
chmod 0640 /etc/apache2/kerb.keytab
systemctl restart apache2
On the client
Add the site URL to the Local intranet zone (Internet Options > Security > Local intranet > Sites > Advanced). Internet Explorer and Chrome only send the cached Windows Kerberos credentials automatically to sites in that zone.
Notes
- If Kerberos delegation is involved: users must be allowed to be delegated (the account must not be marked "Account is sensitive and cannot be delegated"), and the service accounts must be trusted for delegation. A recently changed service account is a common cause of delegation failures.
- Changing the service account password changes the keys: regenerate the keytab.