How to Connect AWS Lambda to Microsoft SQL Server Using PyODBC

How to Connect AWS Lambda to Microsoft SQL Server Using PyODBC

by

This guide shows you how to connect AWS Lambda to Microsoft SQL Server using PyODBC. Lambda doesn’t include ODBC drivers or the pyodbc library by default, so you need to package them yourself. We’ll cover two approaches: a Docker container image (recommended) and a Lambda layer.

Prerequisites

  • Docker installed locally
  • AWS CLI configured — see How to Install AWS CLI v2 on Ubuntu 22.04
  • An Amazon ECR repository (for the container image approach)
  • A SQL Server instance reachable from your Lambda’s VPC
  • SQL Server credentials (stored in environment variables or AWS Secrets Manager)

What Gets Installed and Why

Connecting Lambda to SQL Server requires three components that aren’t included in the Lambda runtime:

  • unixODBC — the ODBC driver manager for Linux. Compiled from source to ensure compatibility with Lambda’s environment.
  • Microsoft ODBC Driver 18 for SQL Server (msodbcsql18) — the actual driver that speaks the SQL Server protocol.
  • pyodbc — the Python library that connects your code to the ODBC driver manager.

Method 1: Container Image (Recommended)

Container images are the simplest way to deploy Lambda functions with native dependencies. Everything is packaged in the Docker image — no layers to manage separately.

Dockerfile

This uses a multi-stage build to keep the final image small. The builder stage compiles unixODBC and installs the ODBC driver, then only the runtime files are copied to the final image.

Create a Dockerfile in your project root:

FROM public.ecr.aws/lambda/python:3.12 AS builder

ENV ODBCINI=/opt/odbc.ini
ENV ODBCSYSINI=/opt/
ARG UNIXODBC_VERSION=2.3.12

# Install build tools
RUN dnf install -y gzip tar openssl-devel gcc gcc-c++ make automake kernel-devel

# Compile unixODBC from source
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-${UNIXODBC_VERSION}.tar.gz -O \
    && tar xzvf unixODBC-${UNIXODBC_VERSION}.tar.gz \
    && cd unixODBC-${UNIXODBC_VERSION} \
    && ./configure --sysconfdir=/opt --disable-gui --disable-drivers \
       --enable-iconv --with-iconv-char-enc=UTF8 \
       --with-iconv-ucode-enc=UTF16LE --prefix=/opt \
    && make \
    && make install

# Install Microsoft ODBC Driver 18
RUN curl https://packages.microsoft.com/config/rhel/9/prod.repo \
    > /etc/yum.repos.d/mssql-release.repo
RUN dnf install -y e2fsprogs fuse-libs libss
RUN ACCEPT_EULA=Y dnf install -y msodbcsql18

# Install pyodbc
ENV CFLAGS="-I/opt/include"
ENV LDFLAGS="-L/opt/lib"
RUN pip install pyodbc -t /opt/python/

# --- Runtime stage ---
FROM public.ecr.aws/lambda/python:3.12

RUN dnf install -y openssl

COPY --from=builder /opt/python /opt/python
COPY --from=builder /opt/microsoft /opt/microsoft
COPY --from=builder /opt/lib /opt/lib

ENV PYTHONPATH=/opt/python
ENV LD_LIBRARY_PATH=/opt/lib

COPY app.py ${LAMBDA_TASK_ROOT}/

CMD ["app.handler"]

The base image public.ecr.aws/lambda/python:3.12 uses Amazon Linux 2023, which uses dnf as its package manager. If you’re using Python 3.11 or earlier, the base image uses Amazon Linux 2 with yum instead — see Solve ‘yum: command not found’ in AWS Lambda Python 3.12 Base Image for details on this change.

Build and Deploy

Build the image, push it to ECR, and create or update the Lambda function:

docker build -t lambda-pyodbc .

aws ecr get-login-password --region us-east-1 | \
  docker login --username AWS --password-stdin 123456789012.dkr.ecr.us-east-1.amazonaws.com

docker tag lambda-pyodbc:latest 123456789012.dkr.ecr.us-east-1.amazonaws.com/lambda-pyodbc:latest

docker push 123456789012.dkr.ecr.us-east-1.amazonaws.com/lambda-pyodbc:latest

Replace 123456789012 with your AWS account ID and us-east-1 with your region.

Method 2: Lambda Layer

If you can’t use container images, package the dependencies as a Lambda layer. This approach builds the layer artifacts inside Docker and outputs a zip file you can upload to Lambda.

Create a file called build-layer.sh:

#!/bin/bash
set -e

PYTHON_VERSION=3.12
UNIXODBC_VERSION=2.3.12

docker run --rm -v "$(pwd)/output:/output" \
  public.ecr.aws/lambda/python:${PYTHON_VERSION} bash -c "
  # Install build tools
  dnf install -y gzip tar openssl-devel gcc gcc-c++ make automake kernel-devel

  # Compile unixODBC
  curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-${UNIXODBC_VERSION}.tar.gz -O
  tar xzvf unixODBC-${UNIXODBC_VERSION}.tar.gz
  cd unixODBC-${UNIXODBC_VERSION}
  ./configure --sysconfdir=/opt --disable-gui --disable-drivers \
    --enable-iconv --with-iconv-char-enc=UTF8 \
    --with-iconv-ucode-enc=UTF16LE --prefix=/opt
  make && make install
  cd ..

  # Install Microsoft ODBC Driver 18
  curl https://packages.microsoft.com/config/rhel/9/prod.repo > /etc/yum.repos.d/mssql-release.repo
  dnf install -y e2fsprogs fuse-libs libss
  ACCEPT_EULA=Y dnf install -y msodbcsql18

  # Install pyodbc
  export CFLAGS='-I/opt/include'
  export LDFLAGS='-L/opt/lib'
  pip install pyodbc -t /opt/python/

  # Package the layer
  cd /opt
  zip -r /output/pyodbc-layer.zip python/ lib/ microsoft/
"

echo 'Layer built: output/pyodbc-layer.zip'

Run the script:

mkdir -p output
chmod +x build-layer.sh
./build-layer.sh

Upload the layer to AWS:

aws lambda publish-layer-version \
  --layer-name pyodbc-mssql \
  --zip-file fileb://output/pyodbc-layer.zip \
  --compatible-runtimes python3.12 \
  --compatible-architectures x86_64

Attach the layer to your Lambda function, and set these environment variables on the function:

LD_LIBRARY_PATH=/opt/lib
ODBCINI=/opt/odbc.ini
ODBCSYSINI=/opt/

For more on building Lambda layers, see How to Build and Deploy Python Libraries for AWS Lambda Layers.

The Lambda Function

This function works with both the container image and layer approaches. It reads connection details from environment variables and runs a test query.

Create app.py:

import os
import pyodbc


def handler(event, context):
    server = os.environ["DB_SERVER"]
    database = os.environ["DB_NAME"]
    username = os.environ["DB_USER"]
    password = os.environ["DB_PASSWORD"]

    conn_str = (
        "DRIVER={ODBC Driver 18 for SQL Server};"
        f"SERVER={server};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
        "TrustServerCertificate=yes;"
    )

    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    row = cursor.fetchone()

    conn.close()

    return {"statusCode": 200, "body": row[0]}

Set these environment variables on your Lambda function:

Variable Example Value
DB_SERVER 10.0.1.50,1433
DB_NAME mydb
DB_USER sa
DB_PASSWORD YOUR_PASSWORD

TrustServerCertificate=yes is required with ODBC Driver 18 when connecting to servers with self-signed certificates. If your SQL Server has a valid TLS certificate, you can remove this parameter.

Troubleshooting

Can’t find ODBC Driver 18

If you get Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server'"), the driver shared library isn’t in the library path. Make sure LD_LIBRARY_PATH includes /opt/lib and that /opt/microsoft/msodbcsql18/lib64/ was copied correctly.

Connection timeout

If the function times out, your Lambda likely can’t reach the SQL Server. Check that:

  • The Lambda is deployed in a VPC with a subnet that can route to the SQL Server
  • The security group allows outbound traffic on port 1433
  • The SQL Server’s security group or firewall allows inbound from the Lambda’s security group

SSL/TLS errors

ODBC Driver 18 enforces TLS encryption by default. If your SQL Server doesn’t support TLS or uses a self-signed certificate, add TrustServerCertificate=yes; and Encrypt=yes; to your connection string.

yum: command not found (Python 3.12+)

Lambda base images for Python 3.12 and later use Amazon Linux 2023, which replaced yum with dnf. Replace all yum commands with dnf. The Microsoft repo URL also changes from rhel/7 to rhel/9. See Solve ‘yum: command not found’ in AWS Lambda Python 3.12 Base Image for the full breakdown.

Conclusion

Your Lambda function can now query SQL Server using pyodbc. The container image approach is simpler to maintain since all dependencies live in the Docker image. The layer approach works when container images aren’t an option. If you’re building CI/CD pipelines to automate this, see Fixing GitLab CI/CD Hangs: Building Docker Images for Lambda Runtime with MSSQL and ODBC for common pipeline issues with this setup.