Bewerken

Delen via

Troubleshoot Active Directory authentication for SQL Server on Linux and containers

  • Artikel

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.

  1. Obtain or renew the Kerberos TGT (ticket-granting ticket) using kinit:

    kinit privilegeduser@CONTOSO.COM

  2. Run 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.keytab

    For more information about the validate-ad-config command, view the help using /opt/mssql/bin/mssql-conf validate-ad-config --help command.

DNS and reverse DNS lookups

  1. 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.com

    If 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.

  2. Perform a reverse DNS (rDNS) lookup for each IP address listed in the previous results. Remember to include 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.conf on the host.

    For more information about reverse DNS, see What is reverse DNS?

Check keytab file and permissions

  1. Check that you 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 mssql user account. For more information, see Use adutil to configure Active Directory authentication with SQL Server on Linux.

  2. Make sure that you can list the contents of the keytab, and that you 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 encounter errors when attempting to sign in using Active Directory authentication.

    klist -kte /var/opt/mssql/secrets/mssql.keytab

    An 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.COM is the privileged account (which matches the network.privilegedadaccount setting in mssql-conf), and the host name for SQL Server is sqllinux.contoso.com listening 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

  1. In krb5.conf (located at /etc/krb5.conf), check that you provide values for the default realm, realm information, and domain to realm mapping. The following example is a sample krb5.conf file. For more information, see Understand Active Directory authentication for SQL Server on Linux and containers.

    [libdefaults]
default_realm = CONTOSO.COM
default_keytab_name = /var/opt/mssql/secrets/mssql.keytab
default_ccache_name = ""

[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.COM

  2. You 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 a Lightweight Directory Access Protocol (LDAP) lookup.

    There are two steps you need to complete. First, modify krb5.conf by adding any number of domain controllers that you need, prefixed with kdc =.

    [realms]
CONTOSO.COM = {
  kdc = kdc1.contoso.com
  kdc = kdc2.contoso.com
  ..
  ..
}

    Keep in mind that krb5.conf is a common Kerberos client configuration file, so any changes you make in this file affects 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 are 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 identify and resolve 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 configure Active Directory authentication.

Guidance

This generic error message requires you to enable PAL logging to identify the specific error.

Refer to the following list of common errors to identify the possible cause for each error, then follow the troubleshooting guidance to resolve the issue.

Error messages
Windows NT user or group 'CONTOSO\user' not found
Could not look up short domain name due to error
Could not perform rDNS lookup for host <hostname> due to error
FQDN not returned by rDNS lookup
Failed to bind to LDAP server
Key table entry not found
No key table entry found for <principal>
Request ticket server <principal> not found in keytab (ticket kvno <KVNO>)
Request ticket server <principal> kvno <KVNO> found in keytab but not with enctype <encryption type>
Request ticket server <principal> kvno <KVNO> enctype <encryption type> found in keytab but cannot decrypt ticket

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, follow the guidance as documented for "Login failed. The login is from an untrusted domain and cannot 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 usually 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 setting 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 nslookup on 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 generic error from OpenLDAP normally means one of two things:

  • No credentials
  • rDNS problems

Here's 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 don'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's 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 fully qualified domain name (FQDN), which in this example is 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 then performs 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 it 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 are the focus of this article.

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 information 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 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

To list all entries in the keytab, follow the Check keytab file and permissions section of this article. 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 Key Version Number (KVNO).

Guidance

To list all entries in the keytab, follow the Check keytab file and permissions section of this article. 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 cannot 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 the result of an incorrect password.

Guidance

Recreate the keytab using the correct password. If you use adutil, create the keytab with the right password, using the steps in Tutorial: Use adutil to configure Active Directory authentication with SQL Server on Linux.

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