Using SQL Server from a Linux Docker container using Windows Authentication

When building a Python application, you often need to connect to a SQL Server to run SQL queries. A standard approach is using the Python packages PyODBC and SQLAlchemy. Microsoft supplies an official driver Microsoft® ODBC Driver 17 for SQL Server. You might want to Dockerize your application for production. Luckily, the driver also exists for the most common Linux distributions such as Alpine, Debian and Ubuntu.

If you are connecting to an Azure SQL Server, the driver alone allows you to use Azure Active Directory directly. However, if your organization have SQL Server running on-premises, there are two other ways to connect. Either as a SQL User (a user created on the SQL Server) or Active Directory (also known as Windows Authentication and not to be confused with Azure Active Directory). In case you can use a SQL User, you are ready to deploy your application. However, many organizations do not allow SQL Users for security and user-management reasons, and you therefore need to authenticate using Active Directory. Unfortunately, this method is not self-contained in Microsoft® ODBC Driver 17 for SQL Server when using a Linux-based setup unlike the two other methods. This article will guide you to achieve connection using on-prem Active Directory from a Linux Docker Container

Some of the information you need might be easier to get using Windows based computer which is able to connect to the SQL Server using Windows Authentication.

Kerberos and krb5

Active Directory is based on the authentication protocol Kerberos version 5. Hence, we can use the Kerberos protocol to connect to SQL Server using Active Directory. The first step is to find the User DNS Domain (USERDNSDOMAIN). The simplest way to do this is using the terminal on a domain-joined Windows computer:

This gives us the login we are using as USERNAME@USERDNSDOMAIN i.e. thomas@YOUR_USERDNSDOMAIN. This will later be referred to as ACTIVE_DIRECTORY_USER.

Domain Name System

We also ensure that the container uses the correct Domain Name System (DNS). If you have a domain-joined Windows computer (where you can connect to the SQL Server from) you can find the DNS server with “nslookup”.

Normally, the DNS server is configured at runtime for containers. If you want to control it you can use the following Docker command:

Service Principal Names

For the authentication to work the Service Principal Name (SPN) in the Active Directory needs to follow the syntax "MSSQLSvc/fqdn:port". You might need to contact the administrator of the Active Directory to ensure this is the case. You can see if it is correctly configured by using a domain-joined Windows computer with SQL Server Management Studio.

1. Open SQL Server Management Studio
2. Login to the server you want to verify the name for
3. Run the command “klist” in Command Prompt (see below)
4. Locate the entry with your server name ("MSSQLSvc/yourserver.your_userdnsdomain:1433" in below)

Creating the Dockerfile

We create a Dockerfile with the needed ODBC driver and with krb5-user for connecting using Active Directory. In the next section the needed scripts are introduced:

Scripts for log-on

In the Microsoft's offical guide it is stated that:

The ODBC driver does not renew credentials itself; ensure that there is a cron job [...] To avoid requiring the password for each renewal, you can use a keytab file.

Two files are needed to achieve this. The first is a script which holds the logic. It initially check if the needed environmental variables are given and then makes the initial login. This yields a ticket-granting ticket (TGT) which are used for acquiring access to the SQL Server. The script then creates a key tab and a cron job which uses this key tab to get a new TGT every 6th hour.

Build and run

We are now ready to build the image and run it. The first step is to build the image.

After the build completes you can try an run it as an interactive container.

You can then try a query against you server.