Introducing adutil - A tool to ease configuration of AD authentication for SQL on Linux/Containers

[amvin87]

We are excited to introduce adutil in public preview, this is a CLI based utility developed to ease the AD authentication configuration for both SQL Server on Linux and SQL Server Linux containers. AD authentication enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol.

Until today, when configuring the AD authentication for SQL on Linux, for creation of "AD user for SQL Server and setting SPNs" you needed to switch from Linux to Windows machine and then switch back to Linux machine to continue with the rest of steps. With adutil released we aim to make this experience seamless, where from the Linux machine itself you can interact and manage the Active Directory domains through the CLI.

Overall adutil is a utility for interacting with and managing Active Directory domains through the CLI. adutil is designed as a series of commands and subcommands, with additional flags that can be specified for further input. Each top-level command represents a category of administrative functions. Each subcommand is an operation within that category. Using adutil you can manage  with users, SPNs, keytabs, groups etc.

To start using adutil please see adutil installation for the install steps

For details on how you can configure AD authentication with adutil for SQL on Linux and containers please refer below:

Configure Active Directory authentication for SQL Server on Linux using adutil

Configure Active Directory authentication for SQL Server on Linux containers using adutil

Thanks,

Engineering lead: Mike Habben

Engineering: Dylan Gray; Dyllon (Owen) Gagnier; Ethan Moffat; Madeline MacDonald

Amit Khandelwal Senior Program Manager

[nicorac]

When running create user command I receive an error:

# adutil user create --distname CN=user,OU=Users,DC=DOMAIN,DC=ORG --password 'password' --debug

adutil Configuration:
        KeytabDir: '/var/run/secrets/keytabs/adutil'
        UseLdaps: 'false'
        IsWindowsDomain: 'true'
        OUDistinguishedName: ''
        UseDefaultSystemCCache: 'true'
Using configuration file from /root/.adutil/adutil.json
Detected parameters:
            Debug: true
             Name: mssql2019
     NameDistName: CN=mssql2019,DC=DOMAIN,DC=ORG
Checking for valid kerberos ticket
UserHasKrb5Credentials: Enumerating credentials in default system CCache
UserHasKrb5Credentials: Found local TGT for realm DOMAIN.ORG
UserHasKrb5Credentials: Found valid credentials
Running command: create user
Attempting to get password from --password flag
Creating user account mssql2019 (CN=mssql2019,DC=DOMAIN,DC=ORG) in DOMAIN.ORG
Failed interactive bind to 'DOMAIN.ORG' in CreateLdapConnection: Local error
Failed to connect to 'DOMAIN.ORG' when adding object 'CN=mssql2019,DC=DOMAIN,DC=ORG'

Failed to connect to 'DOMAIN.ORG' when adding object 'CN=mssql2019,DC=DOMAIN,DC=ORG'

"kinit" runs correctly and also does "id" and "getent".

What's wrong?

I actually can't install SQLServer 2019 since I can't use the "setspn" commands (no Windows servers here, AD is on Samba4...).

[nicorac]

Additional info:

both directory

'/var/run/secrets/keytabs/adutil'

and file

'/root/.adutil/adutil.json'

are missing

[takb1]

@nicorac I had this exact same issue. 

I went back through and followed the instructions to the letter. 

The key difference was following the parts on "Domain Machine Preparation" and "Join the Linux Host machine to your AD domain".

In particular I had to add a reverse DNS zone, setup a PTR record with FQDN of the linux sql host and before joining the realm, ensuring that the hostname of the linux server had the FQDN. 

I haven't determined which of the steps was the key missing piece, but following and verifying all of the steps moved past this issue.

I'm not 100% done yet though. I'm now stuck on creating the keytab and getting an error about No credentials found for supported encryption types. Working through that one at the moment. 

[Aaron2210]

@amvin87 This is a great tool! I worked through the instructions and successfully used it. Can you tell me what advantages it has over adcli? http://manpages.ubuntu.com/manpages/trusty/man8/adcli.8.html I have used adcli and I am wondering if adutil was developed for something that adcli did not provide.

It's great to see the Microsoft team develop tools like this that help integrate Linux. Thanks for your work in accomplishing this!

[LouisPTC]

As with realmd on RHEL-derived systems,  adcli doesn't solve the problem of setting up a service principal name (SPN).  Without adutil, you'll need to go to a Windows system to run setspn and generate your krb.conf file which you can't do on the Linux side unless you're using RC4 encryption (which is terribly insecure these days).  If you really want to manage everything from Linux, RHEL Identity Manager (freeIPA) does get around this, but it's a more complex infrastructure to setup.

[Aaron2210]

@LouisPTC Thank you for the reply, it is very helpful!

As you probably know, adcli does allow setting up SPN with --service-name and generates a keytab, for example:

$sudo adcli join --domain CONTOSO.COM --service-name=cifs --computer-name smbserver --host-fqdn smbserver.CONTOSO.COM --host-keytab /etc/smbserver.keytab -v

This still doesn't configure krb.conf or sssd but that can be done automagically with realm (which is still recommended with the instructions provided for adutil) 

So if I understand you correctly, the primary concern is that adcli is using RC4 encryption which isn't secure? What encryption is adutil utilizing?

Just want to be sure I'm not missing some additional functionality provided by adutil that isn't available with adcli - not that better encryption isn't a huge improvement! 

Thanks!

[IlsaL]

Is there somewhere we can make bug reports?  I don't know how much testing this thing got against a regular domain cause I'm running into endless strange random problems.  For example, sometimes when I run:

adutil user create --name "$AD_USER" --distname "$AD_DN" --password "$AD_PASS"

I get the below error for no explicable reason, and I end up with a disabled AD user.  

"Failed to set password for '$AD_USER': Authentication error"

If I rerun the command, it works.

Or If I do:

adutil user delete --distname "$AD_DN"
adutil user create --name "$AD_USER" --distname "$AD_DN" --password "$AD_PASS"

I get the error:

Failed to modify attributes for object '$DISTINGUISHED_NAME' in '$REALM': Server is unwilling to perform
The LDAP server may not perform the requested action due to restrictions set on the server.

I've gone through the instructions countless times but still can't get authentication to work. When I try, I get the following in the log:

SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The Local Security Authority cannot be contacted [CLIENT: $MY_DOCKER_BRIDGE_IP]
2021-11-23 19:15:47.89 Logon Error: 18452, Severity: 14, State: 1.
2021-11-23 19:15:47.89 Logon Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT:$MY_DOCKER_BRIDGE_IP]

Similarly, if I login as SA and try to add a login, it just tells me that "Windows NT user or group '$MY_DOMAIN_ACCOUNT' not found. Check the name again." I'm starting to think that something has changed with the SQL Server container Microsoft provides and nobody bothered updating the documentation, because I can't find any trace of SSSD or kerberos tools in the container image.

Edit:  I solved my authentication issue at least.  The instructions for setting up SQL Server are incorrect.  You still need to include all those --add-host entries because the DNS lookup within the container doesn't work properly.  You also MUST used arcfour-hmac encryption or authentication just doesn't work.  Also, save yourself a whole lot of grief by creating a logger.ini file inside your mssql data directory with the following contents:

[Output:sql]
type=File
filename=/var/opt/mssql/log/kerberos.log

[Logger:security.ldap]
level=debug
outputs=sql

[Logger:security.kerberos]
level=debug
outputs=sql

and the log file should hopefully tell you where things were going wrong.

[amvin87]

Thank you for all your comments and apologize for the late reply, but please find my replies below 

@IlsaL As you rightly mentioned and also documented here: Configure Active Directory authentication with SQL Server on Linux-based containers using adutil - S... you need to add --add-host options for the container if the host cannot connect to the domain and container needs to connect to domain to be able to join and hence --add-host is necessary, you would normally do it when deploying on public cloud environments like Azure or on kubernetes platforms where the host is not joined to the domain but container needs to be able to talk to the domain. 

I am working on documentation for troubleshooting configuration of AD authentication and will include SQL PAL logging for kerberos as described in this blog by Bob and Dylan : SQL Server on Linux: Kerberos Troubleshooting, Hints and Tips and Hard Code Debugging – SQL Server A...

@Aaron2210 adutil is not a replacement for any tool, the idea for the creation of this tool was to make it easy for SQL Server users/dbas to create keytabs, SPNs without the need to jump between windows and Linux machines. Starting SQL CU 14 adutil is now integrated with mssql-conf tool so you can now rotate keytabs as well using mssql-conf options ( documentation coming soon hopefully in couple of weeks) meanwhile please look at the mssql-conf help options to see the various parameters. 

As mentioned adutil is a completely supported tool and you can open ticket with Microsoft support for issues with adutil when using it for SQL Server more information is available here: Introduction to adutil - Active Directory Utility - SQL Server | Microsoft Docs

[IlsaL]

Also, you may have missed it in my post, but AD authentication does not work without arcfour-hmac.  I've tried 

aes256-cts-hmac-sha1-96 and others alone, and it doesn't work.

What I don't get is why the container is not able to talk directly to the domain.  It's on a local LAN and the host itself is joined to the domain just fine.

[amvin87]

@IlsaL To be able to make it work with aes algorithms, you need to enable aes algorithms for the user in the domain that you use with SQL Server, follow the resolution section for the user as documented here: The encryption type requested is not supported by the KDC - SharePoint | Microsoft Docs

[IlsaL]

But... we're creating the user WITH adutil.  Adutil should be doing this automatically.

Having to then switch to a windows machine to change the encryption types defeats the whole point of the automation.

[ibre5041]

Is it possible to use specific KDC server? Or does it support AD SITE? In our network we dozens of AD servers, which are beyond FW. adutil picks randomly unreachable KDC servers.