Skip to main content

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

nslookup Default Server: yourdcserver.your_userdnsdomain Address:

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

docker run ubuntu cat /etc/resolv.conf nameserver search your_userdnsdomain

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)
klist ... #2> Client: tfh @ YOUR_USERDNSDOMAIN Server: MSSQLSvc/yourserver.your_userdnsdomain:1433 @ YOUR_USERDNSDOMAIN KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96 Ticket Flags 0x40a50000 -> forwardable renewable pre_authent ok_as_delegate name_canonicalize Start Time: 1/17/2021 14:30:16 (local) End Time: 1/18/2021 0:20:09 (local) Renew Time: 1/24/2021 14:20:09 (local) Session Key Type: AES-256-CTS-HMAC-SHA1-96 Cache Flags: 0 Kdc Called: yourdcserver.your_userdnsdomain

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.

docker build -t myapp .

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


You can then try a query against you server.

sqlcmd -q "select 1" -E -s yourserver.USERDNSDOMAIN ----------- 1 (1 rows affected)
Wed, 02/15/2023 - 17:04 -
Tanks for this. However, I wish you provided total example with python code and everything. For myself, I cannot get this to work as I seem to be doing something incorrect in python. Further, I also cannot get the crontab to work as it fails straight away.

Get articles directly in your inbox.

Sign up for the Transpose newsletter and stay up to date with our latest posts.

Transpose Blog Learnings from the Danish data industry Deep dives and quick insights into everyday data challenges. About us