Search code examples
pythonsql-serverdockerazure-active-directorykerberos

Connect to SQL Server db with Azure AD MFA using Python in Docker

I am trying to set up a Python Docker container from which I can connect to several SQL Server databases using Azure Active Directory MFA.

I've created the Docker file as shown below. This builds ok and I am using it in a VSCode devcontainer but not certain I have got all the sql server/odbc stuff correct as I'm unable to get that far. I can connect to the databases successfully in other applications, but not from within the container.

# Docker image with Python3, poyodbc, MS ODBC 18 driver (SQL Server)

# Use official Python image
FROM python:3.10-bullseye

# Set working directory
WORKDIR /app

# Send Python output streams to container log
ENV PYTHONUNBUFFERED 1

# Update apt-get
RUN apt-get update

# Install ggc
RUN apt-get install gcc

# pyodbc dependencies
RUN apt-get install -y tdsodbc unixodbc-dev
RUN apt install unixodbc -y
RUN apt-get clean -y
ADD odbcinst.ini /etc/odbcinst.ini

# ODBC driver dependencies
RUN apt-get install apt-transport-https 
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update

# Install ODBC driver
RUN ACCEPT_EULA=Y apt-get install msodbcsql18 --assume-yes

# Configure ENV for /bin/bash to use MSODBCSQL18
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile 
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc 

# Upgrade pip
RUN pip3 install --upgrade pip3

# Install Python libraries from requirements.txt
COPY requirements.txt requirements.txt
RUN pip3 install -r requirements.txt

# Copy project code into image
COPY . .

Within the container I am running a python script that attempts to connect to a SQL Server database using pyodbc like so:

import pandas as pd
import pyodbc 

conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};'
                      'Server=<my-server-name>;'
                      'Database=<my-db-name>;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

I have set Trusted_Connection to 'yes', which should allow use of MFA, but presumably only under Windows. Instead I get the error message:

Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_0) (851968) (SQLDriverConnect)')

I'm not at all familiar with Kerberos, but it seems I should be able to obtain a ticket using kinit. Reading the docs, it looks like I need to install kinit into the container and also create the file krb5.conf but I'm not sure how to figure out what needs to go in this file, or even if this is the right approach at all. Can anyone please point me in the right direction with this authentication step?

Solution

  • I got this working as follows:

    1. Corrected the Dockerfile to ensure it was using the right ODBC drivers for my linux distro.
    2. Installed Azure CLI into the container from devcontainer.json
    3. Used the approach described here https://stackoverflow.com/a/67692382/5522007 to obtain a token for use with pyodbc.

    That works very well for my purposes.

    My current Dockerfile:

    # Docker image with Python3, poyodbc, MS ODBC 18 driver (SQL Server)

# Use official Python base image
FROM python:3.11-bullseye

# Set working directory
WORKDIR /app

# Send Python output streams to container log
ENV PYTHONUNBUFFERED 1

# Update apt-get
RUN apt-get update

# Install ggc
RUN apt-get install gcc

# Install FreeTDS and dependencies
RUN apt-get install unixodbc -y
RUN apt-get install unixodbc-dev -y
RUN apt-get install freetds-dev -y
RUN apt-get install freetds-bin -y
RUN apt-get install tdsodbc -y
RUN apt-get install --reinstall build-essential -y

# Populate "odbcinst.ini" file
RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so\n\
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so" >> /etc/odbcinst.ini

# ODBC driver dependencies
RUN apt-get install apt-transport-https 
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list

# Install ODBC driver
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools18

# Configure ENV for /bin/bash to use MSODBCSQL18
RUN echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile 
RUN echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc 

# Install Python libraries from requirements.txt
COPY requirements.txt ./
RUN pip3 install -r requirements.txt

# Copy project code into image
COPY . .

    To add the Azure CLI via devcontainer.json:

    
    "features": {
        "ghcr.io/devcontainers/features/azure-cli:1": {}
    },