SQL Server on Linux: ADAuth issues 2: Common AD Login Issues

  • Article

AD authentication is a popular mechanism for login and user authentication. It works very well in many scenarios, especially for enterprise applications. AD authentication is a supported scenario on SQL Server on Linux.

Imagine a scenario where all configuration has succeeded as described in the online documentation, but when a user tries to login they receive login failure message.

If there is a failure during an AD login, it will most likely have the message “Login failed. The login is from an untrusted domain and cannot be used with integrated authentication. ” This is a general failure message, and below, we will walk through the basic mechanism and several common causes of failures.

1. When a user performs an AD connection, internally the user connects to a service principal name (SPN). The SPNs are in the form “MSSQLSvc/host.contoso.com:1433”, and they must be registered when setting up AD logins for SQL Server on Linux. When a client app requests a connection (e.g. sqlcmd), it takes the server users wish to connect to, prepends “MSSQLSvc/” and appends “:**<port>**”, and this is the SPN which the connection attempts to authenticate with.

So, if user connects with “sqlcmd -E -S host.contoso.com”, it authenticates with the SPN “MSSQLSvc/host.contoso.com:1433”, and everything succeeds. If user connects with “sqlcmd -E -S host”, it authenticates with “MSSQLSvc/host:1433”. If the SPN the client is authenticating with does not exist, the connection will fail. So, if the SPNs in the mssql.keytab are only “MSSQLSvc/host.contoso.com:1433”, users can only connect to “host.contoso.com”, not “host” and not to the IP.  If user needs to be able to connect with variations of host name and IP address, then all appropriate SPNs should be created and configured in the mssql.keytab file. 

2. Objects in Active Directory have a key version number (KVNO) associated with them. When the password on an account is changed, the KVNO is increased, and all keytab entries with the old KVNO become invalid, thus forcing everyone to type in the new password. So, if there is a password change on the account owning the “MSSQLSvc” SPN, the KVNO of the SPN is updated, and mssql.keytab will need to be updated accordingly. If the KVNO is not updated in the mssql.keytab file, AD authentication will fail.

To check if the KVNOs are correct, run “klist -kte /var/opt/mssql/secrets/mssql.keytab”, and check the KVNO for the “MSSQLSvc” SPNs listed are the same as when you run “kvno MSSQLSvc/host.contoso.com:1433”. If the KVNOs differ, add the SPN entries to the mssql.keytab as it was done when AD authentication was initially setup: /en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017#configurekeytab.

3. For SQL Server on Linux to authenticate AD users, SQL Server needs to be able to access its keytab. First, check /var/opt/mssql/secrets/mssql.keytab is owned by mssql:mssql and mssql has read permissions.
Next, we need to ensure SQL Server knows where the keytab is stored. This is done through mssql-conf: “mssql-conf set network.kerberoskeytabfile=/var/opt/mssql/secrets/mssql.keytab” and restarting SQL Server: “systemctl restart mssql-server”.

4. To prevent attacks, Kerberos (the authentication protocol behind Active Directory) requires that machines do not have a large time difference between them. Ensure that there is no more than 5 minutes different between the client and SQL Server (after adjusting for time zone differences). If the client is in GMT -8 with a time of 1:00 PM, and the server is in GMT -7 with a time of 2:01 PM, authentication will succeed as the machines are only 1 minute apart. However, if the server is in GMT -8 with a time of 2:01 PM, authentication will fail because they are 1 hour and 1 minute apart.

 These are some examples of potential configuration issues resulting in login failures. This is definitely not most exhaustive list, but it should help you troubleshoot more commonly seen scenarios.

Dylan Gray | Senior Software Engineer

Tejas Shah | Senior Program Manager