How do I troubleshoot SQL Server Kerberos configuration issues?

How do I troubleshoot SQL Server Kerberos configuration issues?

Applies to: MS SQL monitors added with Kerberos authentication, and Applications Manager's own MS SQL back-end database when it is configured for Kerberos.

When Kerberos authentication is used, Applications Manager obtains a ticket from the domain controller and presents it to SQL Server. A failure at any point in that exchange is reported as Kerberos authentication failed while connecting to the database, with the driver's own text alongside it. This article explains how to verify the configuration, what each of the common errors means, and the steps to resolve them.

Work through steps 1 to 6 to confirm the configuration, then use Common Kerberos errors to find the exact message you received.

If this is Applications Manager's own databaseApplications Manager cannot start while it cannot reach its back-end database, so the failure appears in the startup log rather than on a monitor page, and the product is down until it is fixed. If you need it running before the fix can be arranged, revert <APM_HOME>\working\conf\database_params.conf to the authentication method used before Kerberos; any keytab work already done stays valid.

1. Verify the Kerberos configuration in Applications Manager

Ensure that Kerberos authentication has been configured correctly in Applications Manager by following the steps in the relevant KB article:

2. Verify the hostname and domain configuration

Verify that the SQL Server hostname is specified using its Fully Qualified Domain Name (FQDN), for example mssql-kerberos-dc1.mssqlkerberos.com.

Ensure that:

  • The SQL Server hostname is configured using its FQDN. An IP address or a short name cannot be matched to an SPN, so Kerberos will not be attempted.
  • The FQDN can be resolved by the DNS server.

Run the following command:

nslookup <SQL_Server_FQDN>

Example:
nslookup mssql-kerberos-dc1.mssqlkerberos.com

3. Verify the Service Principal Name (SPN) configuration

Run the following command on the SQL Server machine to verify the SPN configuration:

setspn -T <Domain_Name> -F -Q MSSQLSvc/<SQL_Server_FQDN>*

Replace:

  • <Domain_Name> - with your Active Directory domain.
  • <SQL_Server_FQDN> - with the SQL Server's fully qualified domain name.

Verify that the expected SPN exists and is registered correctly. Two SPNs are expected for a default instance, one with the port and one without:

MSSQLSvc/mssql-kerberos-dc1.mssqlkerberos.com
MSSQLSvc/mssql-kerberos-dc1.mssqlkerberos.com:1433

Both must be registered against the account that SQL Server runs as, and each must appear only once in the domain.

4. Verify the krb5.ini configuration

Ensure that the correct krb5.ini file is being used. Download the appropriate file based on your environment:

  • Single-domain environment: krb5_file_for_single_domain_environment.zip
  • Cross-domain environment: krb5_file_for_cross_domain_environment.zip

After downloading:

  • Replace XXX with your domain name in uppercase.
  • Set both kdc and admin_server to the FQDN of the Active Directory domain controller.

Place the file at <APM_HOME>\working\conf\KerberosConfigurations\ and restart Applications Manager.

5. Verify system clock synchronization

Kerberos authentication requires synchronized system clocks. Ensure that the clocks on the following systems are synchronized:

  • Domain Controller
  • SQL Server
  • Applications Manager server

By default, Kerberos allows a maximum clock skew of 5 minutes. On Windows, the current offset can be checked with:

w32tm /stripchart /computer:<Domain_Controller_FQDN> /samples:3 /dataonly

6. Verify the Kerberos ticket cache (if applicable)

If ticket cache authentication is being used, verify that the Kerberos ticket is valid. Navigate to <APM_HOME>/working/jre/bin and run:

klist -l <username>@<DOMAIN>

Example:
klist -l Administrator@APMKERBEROS.COM

Confirm that a valid Kerberos ticket is available and has not expired.

7. Common Kerberos errors and how to resolve them

Applications Manager reports every one of these as Kerberos authentication failed while connecting to the database and prints the driver's own text after it. Match that text against the table below, then follow the corresponding section.

Text in the errorMost likely causeSection
Cannot generate SSPI contextSPN missing, duplicated, or on the wrong account7.1
Defective token detected (AP_REP token id does not match)SQL Server could not decrypt the ticket, or Kerberos was never attempted7.2
No valid credentials provided ... Attempt to obtain new INITIATE credentials failedKeytab or ticket cache cannot authenticate the Applications Manager account7.3
Cannot locate default realmkrb5.ini missing, not read, or has no default_realm7.4
Fail to create credential (63) - No service credsNo SPN for the name the monitor connects to7.5
Server not found in Kerberos database (7)SPN not registered, or registered in another domain7.6
Client not found in Kerberos database (6)The account name or realm in login.conf is wrong7.7
Pre-authentication information was invalid (24) / Checksum failedThe keytab no longer matches the account password7.8
Clock skew too great (37)More than 5 minutes between the three machines7.9
KDC has no support for encryption type (14)Encryption types of the account, the keytab and the JRE do not overlap7.10
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'The connection fell back to NTLM instead of using Kerberos7.11
Login failed for user '<domain>\<account>'Kerberos succeeded; SQL Server has no login for that account7.12
Login timed out / connect timed outThe domain controller or SQL Server port is not reachable7.13
Unable to obtain password from user / keyTab not foundThe keytab path in login.conf is wrong, or the file is corrupt7.14
Any of the above, on an account whose name begins with $The account may be a gMSA, for which no keytab can exist7.15

7.1 Cannot generate SSPI context

Error messageKerberos authentication failed while connecting to the database.
SQL Error : Cannot generate SSPI context

What it means: the client could not obtain a ticket for the SQL Server service, so the handshake never started. In almost every case the SPN is missing, registered more than once, or registered against an account other than the one SQL Server runs as.

Resolution:

  1. Find which account SQL Server runs as, in SQL Server Configuration Manager → SQL Server Services → Log On As.
  2. List every SPN for the server and check for duplicates:
    setspn -T <Domain_Name> -F -Q MSSQLSvc/<SQL_Server_FQDN>*
    setspn -X
  3. Remove any duplicate registration, keeping only the one on the SQL Server service account:
    setspn -D MSSQLSvc/<SQL_Server_FQDN>:1433 <wrong_account>
    setspn -S MSSQLSvc/<SQL_Server_FQDN>:1433 <SQL_Server_service_account>
  4. Restart the SQL Server service so it re-registers cleanly, then retry the connection.

7.2 Integrated authentication failed - Defective token detected (AP_REP token id does not match)

Error messageSQLServerException: Integrated authentication failed
Caused by: GSSException: Defective token detected (Mechanism level: AP_REP token id does not match!)

What it means: Applications Manager presented its service ticket, and what came back from SQL Server was not an AP-REP. The driver uses a raw Kerberos context, so anything else in that position - a Kerberos error, or an NTLM challenge - fails to parse exactly like this. There are two causes, and they are told apart by one check.

Cause 1: SQL Server could not decrypt the ticket. The ticket is encrypted with the SQL Server service account's current password. An instance that was started before that password changed still holds the old one, so it cannot read tickets issued afterwards. The SQL Server error log confirms it, repeating from the moment of the change:

SSPI handshake failed with error code 0x8009030c, state 14 ... The logon attempt failed

Cause 2: Kerberos was never attempted. With no SPN, or a duplicate one, SQL Server answers with NTLM instead, and the driver cannot parse that either. Check which it is:

setspn -Q MSSQLSvc/<SQL_Server_FQDN>:1433    (must return exactly one account)
setspn -X (lists duplicates across the forest)

With Kerberos debug logging on (step 9), the reply itself names the cause: KRB_AP_ERR_MODIFIED is cause 1, and an NTLMSSP token is cause 2.

How to confirm cause 1: compare the time SQL Server last started with the time the service account's password was last changed. If the password change is the later of the two, this is it.

-- When SQL Server last started
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

-- When the service account password last changed (run on a domain-joined machine)
net user <service_account> /domain

Resolution:

  1. Open SQL Server Configuration Manager → SQL Server Services.
  2. Open the properties of the SQL Server service and enter the current password of its service account.
  3. Restart the SQL Server service.
  4. Restart Applications Manager, or re-test the monitor's credentials.

Resolution for cause 2: register the SPN on the SQL Server service account and remove any duplicate (7.1). If the SPN is correct and the host is still not matching, pin it on the connection instead:

;serverSpn=MSSQLSvc/<SQL_Server_FQDN>:1433@<REALM>

See Keep Kerberos working after a password change for what has to be done each time either password changes.

7.3 GSS Failed: No valid credentials provided

Error messageGSS Failed: No valid credentials provided (Mechanism level: Attempt to obtain new INITIATE credentials failed!)

What it means: Applications Manager could not authenticate itself to the domain controller, so no ticket was ever requested for SQL Server. The keytab or the ticket cache is the problem, not SQL Server.

Resolution:

  1. Confirm that the principal in login.conf matches the account exactly, including the realm in uppercase.
  2. Confirm that the keytab named in the keyTab entry exists at <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.
  3. If the account's password has changed since the keytab was created, regenerate it - see 8.2.
  4. If ticket cache authentication is used instead, renew the ticket and confirm it with klist.
If this error returns every dayA login.conf that uses useTicketCache with a ticketCache path works, but the ticket expires with its lifetime - 24 hours by default - and the connection fails with this error from then on. A ticket cache is a short-term workaround, not a configuration: use a keytab (8.2), or keep a scheduled kinit renewing the ticket ahead of each expiry.

7.4 Cannot locate default realm

Error messageI/O Error: GSS Failed: Invalid name provided (Mechanism level: KrbException: Cannot locate default realm)

What it means: the JRE found no usable krb5 configuration, so it does not know which domain to authenticate against.

Resolution:

  1. Confirm that krb5.ini exists at <APM_HOME>\working\conf\KerberosConfigurations\.
  2. Confirm that default_realm is set, and that the realm name is in uppercase everywhere it appears.
  3. Confirm that kdc and admin_server name the domain controller by FQDN.
  4. Restart Applications Manager. The file is read at start-up only.

7.5 Fail to create credential (63) - No service creds

Error messageI/O Error: GSS Failed: No valid credentials provided (Mechanism level: Fail to create credential (63) - No service creds)

What it means: Applications Manager authenticated successfully, but the domain controller has no SPN matching the name it asked for. The name asked for is built from the host exactly as it is typed in the monitor.

Resolution:

  1. Check how the host is entered in the monitor. It must be the FQDN, not an IP address, a short name or an alias.
  2. Verify that an SPN exists for that exact name, both with and without the port (step 3).
  3. If the monitor connects through a cluster or availability group listener, register the SPN for the listener name as well.
  4. If a named instance is used, make sure the SPN carries the instance's static port.

7.6 Server not found in Kerberos database (7)

Error messageKrbException: Server not found in Kerberos database (7)

What it means: the same cause as 7.5, reported by the domain controller rather than by the client: the service name is unknown in this realm.

Resolution:

  1. Register the missing SPN on the SQL Server service account (step 3).
  2. If SQL Server is in a different domain from Applications Manager, confirm the trust between the two domains and use the cross-domain krb5.ini, with a domain_realm mapping for the SQL Server's DNS suffix.

7.7 Client not found in Kerberos database (6)

Error messageKrbException: Client not found in Kerberos database (6)

What it means: the account Applications Manager presents does not exist in the realm as written. This is usually a spelling, case or realm mistake rather than a missing account.

Resolution:

  1. Check the principal in login.conf. It must be user@REALM, with the realm in uppercase - not DOMAIN\user.
  2. Confirm the account still exists and is enabled in Active Directory.
  3. If the account was renamed, regenerate the keytab for the new name.

7.8 Pre-authentication information was invalid (24) / Checksum failed

Error messageKrbException: Pre-authentication information was invalid (24)
KrbException: Checksum failed

What it means: the key in the keytab does not match the key Active Directory holds for the account. There are two reasons for that, and the second one is not obvious.

Cause 1: the password changed after the keytab was built. Every password change invalidates the keytab, including an automatic expiry-driven one. Regenerate it - see 8.2 - and set the monitoring account's password not to expire so it does not recur on a schedule.

Cause 2: Active Directory is holding a stale salt. AES keys are derived from the password and a salt, and the salt is built from the realm plus the account's sAMAccountName as it stood when the keys were last generated. An account that was created with an auto-generated name and renamed later, without a password change since, keeps advertising the original salt. Every keytab tool derives its key from the account's current name, so the two can never agree. RC4 keys ignore the salt, so this surfaces only after a move to AES.

How to confirm it. Run kinit from <APM_HOME>/working/jre/bin with Kerberos debug on and read the salt the domain controller advertises:

kinit.exe -J-Dsun.security.krb5.debug=true -J-Djava.security.krb5.conf=<path_to_krb5.ini> -k -t <keytab> <account>@<REALM>

Look for:
PA-ETYPE-INFO2 etype = 18, salt = <REALM><sAMAccountName>

If the salt does not end with the account name you expect, it is stale. Confirm the account's real name before going further, rather than inferring it:

Get-ADUser -Filter {UserPrincipalName -eq "<account>@<REALM>"} -Properties sAMAccountName,UserPrincipalName,msDS-SupportedEncryptionTypes,DistinguishedName,ObjectClass

If ObjectClass comes back as a group managed service account, stop here and read 7.15 instead.

Resolution for cause 2. The keytab has to be built with the key Active Directory actually holds, which the JRE's own ktab cannot do - it always derives the salt from the principal name it is given. Use one of:

  1. Regenerate the account's keys on the domain controller, which is the usual fix. Setting the password makes Active Directory rebuild its keys under the account's current name, so the stale salt is gone:
    ktpass /princ <account>@<REALM> /mapuser <account>@<REALM> /pass * /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL -setupn +DumpSalt /out apm_new.keytab
    Check the +DumpSalt line before deploying the file: it must print the salt that the domain controller will now advertise. Then re-run the kinit above - the salt should have changed to the account's current name, and a ticket should be issued.
  2. Ask Active Directory to correct the account's sAMAccountName and reset the password. The salt then matches the name everyone uses, and the next person to build a keytab for this account hits none of this.
  3. Build the keytab with a tool that sets the salt explicitly, if the account cannot be touched. MIT ktutil can: addent -password -p <account>@<REALM> -k 1 -e aes256-cts-hmac-sha1-96 -s '<salt>'. The keytab is just a file; build it anywhere and copy it over in binary mode.
Before running ktpassIt resets the account's password and increments its key version, so anything else authenticating as that account must be checked afterwards. Use /pass * to be prompted rather than putting the password on the command line. And if this same account is the one SQL Server runs as, the reset also invalidates SQL Server's own key - see 7.2.

If a lot of service accounts were created the same way and later renamed, they will each fail like this the first time a keytab is built for them. It is worth telling the Active Directory team once.

7.9 Clock skew too great (37)

Error messageKrbException: Clock skew too great (37)

What it means: the clocks of the Applications Manager server, SQL Server and the domain controller differ by more than the permitted 5 minutes, so the ticket is treated as invalid.

Resolution:

  1. Measure the offset against the domain controller (step 5).
  2. Point all three machines at the same time source and resynchronize:
    w32tm /resync
  3. Check the time zone and daylight-saving settings as well; a one-hour offset is usually a time-zone mistake rather than drift.

7.10 KDC has no support for encryption type (14)

Error messageKrbException: KDC has no support for encryption type (14)

What it means: the encryption types supported by the account, the keytab and the JRE do not overlap. It is most often seen after RC4 has been disabled in the domain while the keytab still carries only an RC4 key.

Resolution:

  1. Enable AES on the account in Active Directory (Account options → This account supports Kerberos AES 128/256 bit encryption).
  2. Reset the account password after enabling AES, so that AES keys are generated for it.
  3. Regenerate the keytab with a matching encryption type, for example -crypto AES256-SHA1, and copy it into place (8.2).
  4. Confirm that the relevant encryption types are not listed in permitted_enctypes exclusions in <APM_HOME>\working\jre\lib\security\java.security.

7.11 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Error messageLogin failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

What it means: Kerberos was not used. The connection fell back to NTLM, and the identity could not be carried to SQL Server, so it arrived anonymous. The usual trigger is connecting by IP address or short name, which gives the client nothing to match an SPN against.

Resolution:

  1. Change the monitor's host field to the FQDN and re-test.
  2. Verify the SPN (step 3).
  3. Confirm which protocol the session actually used, from SQL Server:
    SELECT session_id, auth_scheme FROM sys.dm_exec_connections;
    auth_scheme must read KERBEROS. If it reads NTLM, the SPN is still not being matched.

7.12 Login failed for the domain account itself

Error messageLogin failed for user '<domain>\<account>'

What it means: Kerberos worked. SQL Server identified the account correctly and then refused it, because the account has no login, or the login has no rights on the database being monitored. This is a SQL Server permission problem, not a Kerberos one.

Resolution:

  1. Create a login for the domain account on the SQL Server instance.
  2. Grant the permissions the monitor needs, including VIEW SERVER STATE and CONNECT on each monitored database.
  3. Confirm the account is not disabled or locked out in Active Directory.

7.13 Login timed out or connect timed out

Error messageLogin timed out
connect timed out

What it means: with Kerberos enabled, a timeout usually means the domain controller could not be reached, not SQL Server. Kerberos needs port 88 (TCP and UDP) to the domain controller, in addition to the SQL Server port.

Resolution:

  1. From the Applications Manager server, confirm that the domain controller answers on port 88 and that SQL Server answers on its own port.
  2. Confirm DNS resolves both the domain controller and the SQL Server FQDN (step 2).
  3. If the ticket is large - an account in many groups - make sure TCP 88 is open as well as UDP, because the exchange falls back to TCP.

7.14 Keytab file cannot be read or was not found

Error messageUnable to obtain password from user
KrbException: Keytab file does not exist / cannot be read

What it means: the JAAS configuration points at a keytab the JRE cannot open. A keytab transferred in text mode is the most common cause: the file exists, and its contents are corrupt.

Resolution:

  1. Open <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf and note the file name in the keyTab entry.
  2. Confirm that a file of exactly that name exists at <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.
  3. If the keytab was copied from another machine, copy it again in binary mode.
  4. Keep the path format the existing file uses - doubled forward slashes:
    principal="<account>@<REALM>"
    keyTab="<APM_HOME>//working//conf//KerberosConfigurations//MSSQL//apm.keytab"
  5. Confirm that <APM_HOME>\working\jre\lib\security.policy has a login.config.url.<n> entry pointing at that same login.conf. That is how Applications Manager registers it, so a file in the right place that nothing points at is never read.
  6. Confirm the account Applications Manager runs as can read the file, and that nothing else can: a keytab is password-equivalent.
  7. Restart Applications Manager.

7.15 The account is a group managed service account

What it means: a gMSA has no fixed password - Active Directory rotates it automatically - so no keytab can be built for it and every keytab-based error above will keep returning. An account name beginning with $ is worth checking before anything else.

How to confirm it: Get-ADUser (or Get-ADServiceAccount) returns msDS-GroupManagedServiceAccount as the object class.

Resolution: use the gMSA support in Applications Manager rather than Kerberos keytabs - it needs no user name, password or keytab. See How do I configure a gMSA account for the Applications Manager SQL Server database?

8. Keep Kerberos working after a password change

Two accounts are involved in a Kerberos connection, and a password change to either one breaks it until the corresponding step below is carried out. Both are routine maintenance, not faults.

8.1 The SQL Server service account password changed

A running SQL Server keeps the password it started with, so Kerberos logins begin failing from the moment the password is changed - even though SQL Server itself is still up and serving other connections.

  1. Enter the new password in SQL Server Configuration Manager, in the properties of the SQL Server service.
  2. Restart the SQL Server service.

Until this is done, Kerberos logins to SQL Server fail with the error in 7.2.

8.2 The Applications Manager account password changed

The keytab holds a key derived from the account's password, so it stops working the moment the password changes.

  1. Regenerate the keytab for the account on a domain controller:
    ktpass /princ <account>@<REALM> /mapuser <account>@<REALM> /pass * /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL -setupn +DumpSalt /out apm_new.keytab
    /pass * prompts rather than putting the password in the command history, and -setupn stops ktpass rewriting the account's UPN.
  2. Verify the keytab before deploying it, from <APM_HOME>/working/jre/bin:
    kinit.exe -J-Djava.security.krb5.conf=<path_to_krb5.ini> -k -t apm_new.keytab <account>@<REALM>
    A ticket is issued if the key matches. If it fails with Pre-authentication information was invalid, go to 7.8 - the account's salt is the problem, not the file.
  3. Copy the keytab, in binary mode, to <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.
    NoteRun ktpass with the password the account already has. Used with -pass, it sets the account password in Active Directory to the value given, so a different value here changes the password again and invalidates anything else using it.
  4. Keep the file name used in the keyTab entry of <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf, or update that entry to match the new name.
  5. Restart Applications Manager.

Until this is done, Applications Manager cannot connect to its database, and MS SQL monitors using Kerberos fail with the errors in 7.3 and 7.8.

To avoid a scheduled outage, set both accounts' passwords not to expire, or plan the two steps above into the same change window as the password rotation.

9. Collect diagnostic information if the issue persists

Enable Kerberos debug logging

Add the following entry to <APM_HOME>/working/conf/wrapper.conf:

wrapper.java.additional.33=-Dsun.security.krb5.debug=true

Restart Applications Manager, then reproduce the issue by attempting to add the SQL Server monitor using Kerberos authentication.

Share the following information

  • Screenshot showing the Kerberos configuration in Applications Manager
  • Output of the setspn command
  • Output of the nslookup command
  • Output of the klist command
  • Confirmation that the system clocks are synchronized
  • Screenshot showing the debug entry added to wrapper.conf
  • The SQL Server error log covering the time of the failure, which records the matching SSPI handshake entries
  • The time SQL Server last started, and the time the service account password last changed

Collect the following files from Applications Manager

FileLocation
database_params.confAPM_HOME/working/conf
java.securityAPM_HOME/working/jre/lib/security/
krb5.iniAPM_HOME/working/conf/KerberosConfigurations/
login.confAPM_HOME/working/conf/KerberosConfigurations/MSSQL/MicrosoftJDBCDriver/
wrapper.confAPM_HOME/working/conf

Refer to our feature page to learn more about SQL Server monitoring in Applications Manager.