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.
<APM_HOME>\working\conf\database_params.conf to the authentication method used before Kerberos; any keytab work already done stays valid.Ensure that Kerberos authentication has been configured correctly in Applications Manager by following the steps in the relevant KB article:
Verify that the SQL Server hostname is specified using its Fully Qualified Domain Name (FQDN), for example mssql-kerberos-dc1.mssqlkerberos.com.
Ensure that:
Run the following command:
nslookup <SQL_Server_FQDN>
Example:
nslookup mssql-kerberos-dc1.mssqlkerberos.com
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.
Ensure that the correct krb5.ini file is being used. Download the appropriate file based on your environment:
krb5_file_for_single_domain_environment.zipkrb5_file_for_cross_domain_environment.zipAfter downloading:
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.
Kerberos authentication requires synchronized system clocks. Ensure that the clocks on the following systems are synchronized:
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
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.
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 error | Most likely cause | Section |
|---|---|---|
| Cannot generate SSPI context | SPN missing, duplicated, or on the wrong account | 7.1 |
| Defective token detected (AP_REP token id does not match) | SQL Server could not decrypt the ticket, or Kerberos was never attempted | 7.2 |
| No valid credentials provided ... Attempt to obtain new INITIATE credentials failed | Keytab or ticket cache cannot authenticate the Applications Manager account | 7.3 |
| Cannot locate default realm | krb5.ini missing, not read, or has no default_realm | 7.4 |
| Fail to create credential (63) - No service creds | No SPN for the name the monitor connects to | 7.5 |
| Server not found in Kerberos database (7) | SPN not registered, or registered in another domain | 7.6 |
| Client not found in Kerberos database (6) | The account name or realm in login.conf is wrong | 7.7 |
| Pre-authentication information was invalid (24) / Checksum failed | The keytab no longer matches the account password | 7.8 |
| Clock skew too great (37) | More than 5 minutes between the three machines | 7.9 |
| KDC has no support for encryption type (14) | Encryption types of the account, the keytab and the JRE do not overlap | 7.10 |
| Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' | The connection fell back to NTLM instead of using Kerberos | 7.11 |
| Login failed for user '<domain>\<account>' | Kerberos succeeded; SQL Server has no login for that account | 7.12 |
| Login timed out / connect timed out | The domain controller or SQL Server port is not reachable | 7.13 |
| Unable to obtain password from user / keyTab not found | The keytab path in login.conf is wrong, or the file is corrupt | 7.14 |
Any of the above, on an account whose name begins with $ | The account may be a gMSA, for which no keytab can exist | 7.15 |
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:
setspn -T <Domain_Name> -F -Q MSSQLSvc/<SQL_Server_FQDN>*
setspn -X
setspn -D MSSQLSvc/<SQL_Server_FQDN>:1433 <wrong_account>
setspn -S MSSQLSvc/<SQL_Server_FQDN>:1433 <SQL_Server_service_account>
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:
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.
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:
login.conf matches the account exactly, including the realm in uppercase.keyTab entry exists at <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.klist.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.What it means: the JRE found no usable krb5 configuration, so it does not know which domain to authenticate against.
Resolution:
krb5.ini exists at <APM_HOME>\working\conf\KerberosConfigurations\.default_realm is set, and that the realm name is in uppercase everywhere it appears.kdc and admin_server name the domain controller by FQDN.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:
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:
krb5.ini, with a domain_realm mapping for the SQL Server's DNS suffix.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:
login.conf. It must be user@REALM, with the realm in uppercase - not DOMAIN\user.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,ObjectClassIf 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:
ktpass /princ <account>@<REALM> /mapuser <account>@<REALM> /pass * /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL -setupn +DumpSalt /out apm_new.keytab
+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.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.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./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.
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:
w32tm /resync
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:
-crypto AES256-SHA1, and copy it into place (8.2).permitted_enctypes exclusions in <APM_HOME>\working\jre\lib\security\java.security.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:
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.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:
VIEW SERVER STATE and CONNECT on each monitored database.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:
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:
<APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf and note the file name in the keyTab entry.<APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.principal="<account>@<REALM>"
keyTab="<APM_HOME>//working//conf//KerberosConfigurations//MSSQL//apm.keytab"
<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.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?
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.
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.
Until this is done, Kerberos logins to SQL Server fail with the error in 7.2.
The keytab holds a key derived from the account's password, so it stops working the moment the password changes.
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.<APM_HOME>/working/jre/bin:
kinit.exe -J-Djava.security.krb5.conf=<path_to_krb5.ini> -k -t apm_new.keytab <account>@<REALM>
<APM_HOME>\working\conf\KerberosConfigurations\MSSQL\.
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.keyTab entry of <APM_HOME>\working\conf\KerberosConfigurations\MSSQL\MicrosoftJDBCDriver\login.conf, or update that entry to match the new name.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.
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
setspn commandnslookup commandklist commandCollect the following files from Applications Manager
| File | Location |
|---|---|
| database_params.conf | APM_HOME/working/conf |
| java.security | APM_HOME/working/jre/lib/security/ |
| krb5.ini | APM_HOME/working/conf/KerberosConfigurations/ |
| login.conf | APM_HOME/working/conf/KerberosConfigurations/MSSQL/MicrosoftJDBCDriver/ |
| wrapper.conf | APM_HOME/working/conf |
Refer to our feature page to learn more about SQL Server monitoring in Applications Manager.