Troubleshoot Active Directory authentication for SQL Server on Linux and containers
Applies to: 
SQL Server - Linux
This article helps you troubleshoot Active Directory Domain Services authentication issues with SQL Server on Linux and containers. It includes prerequisite checks and tips for a successful Active Directory configuration, and a list of common errors and troubleshooting steps.
Validate current configuration
Before you begin troubleshooting, you must validate the current user, mssql.conf, Service Principal Name (SPN), and realm settings.
Obtain or renew the Kerberos TGT (ticket-granting ticket) using
kinit:kinit privilegeduser@CONTOSO.COMRun the following command, making sure that the user under which you're running this command has access to the
mssql.keytab:/opt/mssql/bin/mssql-conf validate-ad-config /var/opt/mssql/secrets/mssql.keytabFor more information about the
validate-ad-configcommand, view the help using/opt/mssql/bin/mssql-conf validate-ad-config --helpcommand.
DNS and reverse DNS lookups
DNS lookups on the domain name and NetBIOS name should return the same IP address, which normally matches the IP address for the domain controller (DC). Run these commands from the SQL Server host machine.
nslookup contoso nslookup contoso.comIf the IP addresses don't match, see Join SQL Server on a Linux host to an Active Directory domain to fix DNS lookups and communication with the DC.
Perform a reverse DNS (rDNS) lookup for each IP address listed in the previous results. This includes IPv4 and IPv6 addresses where applicable.
nslookup <IPs returned from the above commands>All should return
<hostname>.contoso.com. If that isn't the case, check the PTR (pointer) records that are created in Active Directory.You might have to work with your domain administrator to get rDNS working. If you can't add PTR entries for all the IP addresses returned, you can also limit SQL Server to a subset of domain controllers. This change affects any other services using
krb5.confon the host.For more information about reverse DNS, see What is reverse DNS?
Check keytab file and permissions
Check that you've created the keytab (key table) file, and that mssql-conf is configured to use the correct file with appropriate permissions. The keytab must be accessible to the
mssqluser account. For more information, see Use adutil to configure Active Directory authentication with SQL Server on Linux.Make sure that you can list the contents of the keytab, and that you've added the correct SPNs, port, encryption type, and user account. If you don't type the passwords correctly when creating the SPNs and keytab entries, you'll encounter errors when attempting to sign in using Active Directory authentication.
klist -kte /var/opt/mssql/secrets/mssql.keytabAn example of a working keytab follows. The example uses two encryption types, but you can use just one or more depending on the encryption types supported in your environment. In the example,
sqluser@CONTOSO.COMis the privileged account (which matches the network.privilegedadaccount setting in mssql-conf), and the host name for SQL Server issqllinux.contoso.comlistening on the default port 1433.$ kinit privilegeduser@CONTOSO.COM Password for privilegeduser@CONTOSO.COM: $ klist Ticket cache: FILE:/tmp/krb5cc_1000 Default principal: privilegeduser@CONTOSO.COM Valid starting Expires Service principal 01/26/22 20:42:02 01/27/22 06:42:02 krbtgt/CONTOSO.COM@CONTOSO.COM renew until 01/27/22 20:41:57 $ klist -kte mssql.keytab Keytab name: FILE:mssql.keytab KVNO Timestamp Principal ---- ----------------- -------------------------------------------------------- 2 01/13/22 13:19:47 MSSQLSvc/sqllinux@CONTOSO.COM (aes256-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux@CONTOSO.COM (aes128-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux.contoso.com@CONTOSO.COM (aes256-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux.contoso.com@CONTOSO.COM (aes128-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux:1433@CONTOSO.COM (aes256-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux:1433@CONTOSO.COM (aes128-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux.contoso.com:5533@CONTOSO.COM (aes256-cts-hmac-sha1-96) 2 01/13/22 13:19:47 MSSQLSvc/sqllinux.contoso.com:5533@CONTOSO.COM (aes128-cts-hmac-sha1-96) 2 01/13/22 13:19:55 sqluser@CONTOSO.COM (aes256-cts-hmac-sha1-96) 2 01/13/22 13:19:55 sqluser@CONTOSO.COM (aes128-cts-hmac-sha1-96)
Validate realm information in krb5.conf
In
krb5.conf(located at/etc/krb5.conf), check that you have provided values for the default realm, realm information, and domain to realm mapping. The following example is a samplekrb5.conffile. For more information, see Understanding Active Directory authentication for SQL Server on Linux and containers.[libdefaults] default_realm = CONTOSO.COM [realms] CONTOSO.COM = { kdc = adVM.contoso.com admin_server = adVM.contoso.com default_domain= contoso.com } [domain_realm] .contoso.com = CONTOSO.COM contoso.com = CONTOSO.COMYou can restrict SQL Server to contact a subset of domain controllers, which is useful if your DNS configuration returns more domain controllers than SQL Server needs to contact. SQL Server on Linux allows you to specify a list of domain controllers that SQL Server contacts in a round-robin fashion when performing an LDAP lookup.
There are two steps you need to complete. First, modify
krb5.confby adding any number of domain controllers that you need, prefixed withkdc =.[realms] CONTOSO.COM = { kdc = kdc1.contoso.com kdc = kdc2.contoso.com .. .. }Keep in mind that
krb5.confis a common Kerberos client configuration file, so any changes you make in this file will affect other services in addition to SQL Server. Before making any changes, consult with your domain administrator.You can then enable the network.enablekdcfromkrb5conf setting using mssql-conf, and then restart SQL Server:
sudo /opt/mssql/bin/mssql-conf set network.enablekdcfromkrb5conf true sudo systemctl restart mssql-server
Troubleshoot Kerberos
See the following details to assist you in troubleshooting Active Directory authentication issues and identifying specific error messages.
Trace Kerberos
After you create the user, SPNs, and keytabs, and configure mssql-conf to see that the Active Directory configuration for SQL Server on Linux is correct, you can display the Kerberos trace messages to the console (stdout) when attempting to obtain or renew the Kerberos TGT with the privileged account, using this command:
root@sqllinux mssql# KRB5_TRACE=/dev/stdout kinit -kt /var/opt/mssql/secrets/mssql.keytab sqluser
If there aren't any problems, you should see output similar to the following sample. If not, the trace provides context about which steps you should review.
3791545 1640722276.100275: Getting initial credentials for sqluser@CONTOSO.COM
3791545 1640722276.100276: Looked up etypes in keytab: aes256-cts, aes128-cts
3791545 1640722276.100278: Sending unauthenticated request
3791545 1640722276.100279: Sending request (202 bytes) to CONTOSO.COM
3791545 1640722276.100280: Initiating TCP connection to stream 10.0.0.4:88
3791545 1640722276.100281: Sending TCP request to stream 10.0.0.4:88
3791545 1640722276.100282: Received answer (185 bytes) from stream 10.0.0.4:88
3791545 1640722276.100283: Terminating TCP connection to stream 10.0.0.4:88
3791545 1640722276.100284: Response was from master KDC
3791545 1640722276.100285: Received error from KDC: -1765328359/Additional pre-authentication required
3791545 1640722276.100288: Preauthenticating using KDC method data
3791545 1640722276.100289: Processing preauth types: PA-PK-AS-REQ (16), PA-PK-AS-REP_OLD (15), PA-ETYPE-INFO2 (19), PA-ENC-TIMESTAMP (2)
3791545 1640722276.100290: Selected etype info: etype aes256-cts, salt "CONTOSO.COMsqluser", params ""
3791545 1640722276.100291: Retrieving sqluser@CONTOSO.COM from /var/opt/mssql/secrets/mssql.keytab (vno 0, enctype aes256-cts) with result: 0/Success
3791545 1640722276.100292: AS key obtained for encrypted timestamp: aes256-cts/E84B
3791545 1640722276.100294: Encrypted timestamp (for 1640722276.700930): plain 301AA011180F32303231313XXXXXXXXXXXXXXXXXXXXXXXXXXXXX, encrypted 333109B95898D1B4FC1837DAE3E4CBD33AF8XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
3791545 1640722276.100295: Preauth module encrypted_timestamp (2) (real) returned: 0/Success
3791545 1640722276.100296: Produced preauth for next request: PA-ENC-TIMESTAMP (2)
3791545 1640722276.100297: Sending request (282 bytes) to CONTOSO.COM
3791545 1640722276.100298: Initiating TCP connection to stream 10.0.0.4:88
3791545 1640722276.100299: Sending TCP request to stream 10.0.0.4:88
3791545 1640722276.100300: Received answer (1604 bytes) from stream 10.0.0.4:88
3791545 1640722276.100301: Terminating TCP connection to stream 10.0.0.4:88
3791545 1640722276.100302: Response was from master KDC
3791545 1640722276.100303: Processing preauth types: PA-ETYPE-INFO2 (19)
3791545 1640722276.100304: Selected etype info: etype aes256-cts, salt "CONTOSO.COMsqluser", params ""
3791545 1640722276.100305: Produced preauth for next request: (empty)
3791545 1640722276.100306: AS key determined by preauth: aes256-cts/E84B
3791545 1640722276.100307: Decrypted AS reply; session key is: aes256-cts/05C0
3791545 1640722276.100308: FAST negotiation: unavailable
3791545 1640722276.100309: Initializing KCM:0:37337 with default princ sqluser@CONTOSO.COM
3791545 1640722276.100310: Storing sqluser@CONTOSO.COM -> krbtgt/CONTOSO.COM@CONTOSO.COM in KCM:0:37337
3791545 1640722276.100311: Storing config in KCM:0:37337 for krbtgt/CONTOSO.COM@CONTOSO.COM: pa_type: 2
3791545 1640722276.100312: Storing sqluser@CONTOSO.COM -> krb5_ccache_conf_data/pa_type/krbtgt/CONTOSO.COM@CONTOSO.COM@X-CACHECONF: in KCM:0:37337
$ sudo klist
Ticket cache: KCM:0:37337
Default principal: sqluser@CONTOSO.COM
Valid starting Expires Service principal
12/28/2021 20:11:16 12/29/2021 06:11:16 krbtgt/CONTOSO.COM@CONTOSO.COM
renew until 01/04/2022 20:11:16
Enable Kerberos and security-based PAL logging
You can enable security.kerberos and security.ldap logging to identify specific error messages in the PAL (Platform Abstraction Layer). Create a logger.ini file with the following content at /var/opt/mssql/, restart SQL Server, and then reproduce the failure. The PAL's Active Directory error and debug messages will be logged to /var/opt/mssql/log/security.log.
[Output:security]
Type = File
Filename = /var/opt/mssql/log/security.log
[Logger]
Level = Silent
[Logger:security.kerberos]
Level = Debug
Outputs = security
[Logger:security.ldap]
Level = debug
Outputs = security
You don't need to restart SQL Server for the logger changes to be picked up from logger.ini, but failures can occur during Active Directory service initialization on during SQL Server startup that would otherwise go unnoticed. Restarting SQL Server ensures all the error messages are captured.
The security log continues to write to the drive until you remove the changes in logger.ini. Remember to disable security.kerberos and security.ldap logging once you've identified and resolved the problem to prevent running out of space on the drive.
The PAL logger generates log files in the following format:
<DATETIME> <Log level> [<logger>] <<process/thread identifier>> <message>
For example, a sample line from the log follows:
12/28/2021 13:56:31.609453055 Error [security.kerberos] <0003753757/0x00000324> Request ticket server MSSQLSvc/sql.contoso.com:1433@CONTOSO.COM kvno 3 enctype aes256-cts found in keytab but cannot decrypt ticket
Once you have PAL logging enabled and you reproduce the issue, look for the first message with a log-level of Error, then use the following table to find the error and follow the guidance and recommendation to troubleshoot and resolve the issue.
Common error messages
Error message: "Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication."
Possible cause
This error is encountered when you try logging in using an Active Directory account, once you have configured Active Directory authentication.
Guidance
This is a generic error message, and you must enable PAL logging to identify the specific error message.
You can refer to this list of common errors to identify the possible cause for each error, then follow the troubleshooting guidance to resolve the issue.
Error message: Windows NT user or group 'CONTOSO\user' not found
Possible cause
You might encounter this error when trying to create the Windows login, or during group refresh.
Guidance
To validate the issue, please follow the guidance as documented for "Login failed. The login is from an untrusted domain and can't be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)" enable PAL logging to identify the specific error, and troubleshoot accordingly.
Error message: "Could not look up short domain name due to error"
Possible cause
The Transact-SQL syntax to create an Active Directory login is:
CREATE LOGIN [CONTOSO\user] FROM WINDOWS;
The NetBIOS name (CONTOSO) is required in the command, but in the backend when performing an LDAP connection, the FQDN of the domain (contoso.com) must be provided. To do this conversion, a DNS lookup is performed on CONTOSO to resolve to the IP of a domain controller, which can then be bound to for LDAP queries.
Guidance
The error message "Could not look up short domain name due to error" suggests that nslookup for contoso doesn't resolve to IP address of the domain controller. You should review DNS and reverse DNS lookups to confirm that nslookup for both the NetBIOS and domain name should match.
Error messages: "Could not perform rDNS lookup for host <hostname> due to error" or "FQDN not returned by rDNS lookup"
Possible cause
This error message mostly indicates that the reverse DNS records (PTR records) don't exist for all domain controllers.
Guidance
Check the DNS and reverse DNS lookups. Once the domain controllers that don't have rDNS entries are identified, there are two options:
Add rDNS entries for all domain controllers
This isn't a SQL Server setting, and must be configured at the domain level. You might have to work with your domain administration team to create the required PTR records for all the domain controllers returned when running
nslookupon the domain name.Restrict SQL Server to a subset of domain controllers
If adding PTR records isn't possible for all returned domain controllers, you can limit SQL Server to a subset of domain controllers.
Error message: "Failed to bind to LDAP server ldap://CONTOSO.COM:3268: Local Error"
Possible cause
This is a generic error from OpenLDAP, but normally means one of two things:
- No credentials
- rDNS problems
Here is one such example of the error message:
12/09/2021 14:32:11.319933684 Error [security.ldap] <0000000142/0x000001c0> Failed to bind to LDAP server ldap://[CONTOSO.COM:3268]: Local error
Guidance
No credentials
Other error messages are thrown first if credentials won't load for LDAP connections. You should enable PAL logging and check the error log for error messages before this one. If there aren't any other errors, it's most likely not a credentials issue. If one is found, then work on fixing the error message that you see. In most cases, it will be one of the error messages covered in this article.
rDNS problems
Check the DNS and reverse DNS lookups.
When the OpenLDAP library connects to a domain controller, either the domain FQDN (
contoso.com) or the DC's FQDN (kdc1.contoso.com) is provided. Once the connection is made (but before returning success to the caller), the OpenLDAP library checks the IP of the server it connected to. It will then perform a reverse DNS lookup and check that the name of the server connected to (kdc1.contoso.com) matches the domain that the connection was requested for (contoso.com). If this doesn't match, the OpenLDAP library fails the connection as a security feature. This is part of why the rDNS settings are so important for SQL Server on Linux and have been in focus for this documentation.
Error message: "Key table entry not found"
Possible cause
This error indicates access issues with the keytab file, or not having all the required entries in the keytab.
Guidance
Make sure the keytab file has the correct access level and permissions. The default location and the name for the keytab file is /var/opt/mssql/secrets/mssql.keytab. To view the current permissions on all files under the secrets folder, you can run this command:
sudo ls -lrt /var/opt/mssql/secrets
You can use these commands to set the permissions and access level on the keytab file:
sudo chown mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 440 /var/opt/mssql/secrets/mssql.keytab
For more details on listing the keytab entries and setting the correct permissions, see the previous Check keytab file and permissions section. If any of the conditions in that section aren't met, you'll see this or equivalent error: "Key table entry not found".
Error message: "No key table entry found for <principal>"
Possible cause
When attempting to retrieve the credentials of <principal> from the keytab, no applicable entries were found.
Guidance
Follow the Check keytab file and permissions section of this document to list all entries in the keytab. Make sure that <principal> is present. In this case, the principal account is usually the network.privilegedadaccount to which the SPNs are registered. If it isn't, then add it using the adutil command. For more information, see Use adutil to configure Active Directory authentication with SQL Server on Linux.
Error message: "Request ticket server <principal> not found in keytab (ticket kvno <KVNO>)"
Possible cause
This error indicates that SQL Server couldn't find a keytab entry for the requested ticket with the specified KVNO (Key Version Number).
Guidance
Follow the Check keytab file and permissions section of this document to list all entries in the keytab. If you can't find an error message that matches the <principal> and KVNO, add this entry by updating the keytab file using the steps mentioned in that section.
You can also run the following command to get the latest KVNO from the DC. Before you run this command, you need to obtain or renew the Kerberos TGT using the kinit command. For more information, see Use adutil to create an Active Directory user for SQL Server and set the Service Principal Name (SPN).
kvno MSSQLSvc/<hostname>
Error message: "Request ticket server <principal> kvno <KVNO> found in keytab but not with enctype <encryption type>"
Possible cause
This error means that the encryption type requested by the client wasn't present in SQL Server's keytab.
Guidance
To validate, follow the Check keytab file and permissions section of this document to list all entries in the keytab. If you can't find an error message that matches the principal, KVNO, and encryption type, add this entry by updating the keytab file using the steps mentioned in that section.
Error message: "Request ticket server <principal> kvno <KVNO> enctype <encryption type> found in keytab but can't decrypt ticket"
Possible cause
This error indicates that SQL Server couldn't use a credential from the keytab file to decrypt the incoming authentication request. The error is often cased by an incorrect password.
Guidance
Recreate the keytab using the correct password. If you use adutil, follow the steps in Use adutil to configure Active Directory authentication with SQL Server on Linux to create the keytab with the right password.
Common ports
This table shows the common ports used by SQL Server on Linux for configuring and administering Active Directory authentication.
| Active Directory Service | Port |
|---|---|
| DNS | 53 |
| LDAP | 389 |
| LDAPS | 636 |
| Kerberos | 88 |
Related content
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for