SQL Server on Linux: ADAuth issues 3: Could not obtain information about Windows NT group/user ‘CONTOSO\user’
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 after a successful AD login and running a couple queries, some users may see the error “Could not obtain information about Windows NT group/user ‘CONTOSO\user’. ” This is due to a failure when searching for group memberships for the logged in user and can be easily fixed.
When a user logs in, their group memberships are looked up and used to determine if they have the privileges to login. Once the user is connected SQL Server must validate their group memberships in many scenarios, to make sure their effective access permissions have not changed. For example, if user CONTOSO\user1 was a member of CONTOSO\group1, and CONTOSO\group1 has login permission for the SQL Server instance, then CONTOSO\user1 can login successfully. However, if after CONTOSO\user1 logs in, they are removed from CONTOSO\group1 by a domain admin, then their access to SQL Server should be revoked.
To check a user’s group memberships, the service principal name (SPN) in mssql.keytab (e.g. “MSSQLSvc/host.contoso.com:1433”) is not enough. It needs the credentials of the machine account (“**<hostname>**$”) in mssql.keytab. Use “klist -kte /var/opt/mssql/secrets/mssql.keytab” to check if the machine’s credentials are present. The KVNOs should also match the value returned by “kvno **<hostname>**$ ”.
If the values are not present, check if they are in /etc/krb5.keytab with the correct KVNO. If not, run “realm leave” then “realm join contoso.com -U ‘user@CONTOSO.COM” and the KVNOs will be updated. Once the “**<hostname>**$” entries are in /etc/krb5.keytab with the correct KVNO, then you can copy them to /var/opt/mssql/secrets/mssql.keytab using ktutil :
$ sudo ktutil
> rkt /etc/krb5.keytab
> list # this will show all entries you just read into memory
> delent **<slot #>** # delete all entries which aren’t “**<hostname>**$” with the right kvno
# Note: after deleting a slot, all entries below slide up one slot, so be careful when deleting
> wkt /var/opt/mssql/secrets/mssql.ketyab # append the new entries to mssql.keytab once other entries have been removed.
NOTE: these steps were recently updated in the online documentation. Check following link. /en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-2017#configurekeytab (3rd step.)
Once finished with ktutil, check mssql.keytab has the credentials: “klist -kte /var/opt/mssql/secrets/mssql.keytab”.
While not the only reason, group lookup failures for users are generally the cause of runtime login error mentioned earlier. Once the UPN is configured correctly and available for reference in the mssql.keytab file, it should help you avoid the group lookup failures for users in most circumstance.
Dylan Gray | Senior Software Engineer
Tejas Shah | Senior Program Manager