Docker - Build pyOdbc Microsoft SQL AWS Lambda Layer

Dockerfile

FROM lambci/lambda:build-python3.7
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O && \
    tar xvzf unixODBC-2.3.5.tar.gz && \
    cd unixODBC-2.3.5 && \
    ./configure  --sysconfdir=/opt/python  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home && \
    make install && \
    cd .. && \
    mv /home/* . && \
    mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo && \
    yum -y install freetds e2fsprogs && \
    ACCEPT_EULA=Y yum -y install msodbcsql --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" && \
    export LDFLAGS="-L/root/lib" && \
    pip install pyodbc requests "pymssql<3.0" adodbapi --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Driver      = ODBC Driver 13 for SQL Server\n\
Description = My ODBC Driver 13 for SQL Server\n\
Trace       = No'  > /root/odbc.ini
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Description = Microsoft ODBC Driver 13 for SQL Server\n\
Driver      = /opt/python/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2\n\
UsageCount  = 1'  > /root/odbcinst.ini
RUN mkdir -p /opt/python && \
    cp /usr/lib64/libsybdb.so.5 /root/lib/libsybdb-89a09a88.so.5.1.0 && \
    cp -r /root/* /opt/python && \
    mv /opt/python/lib /opt && \
    mv /opt/python/bin /opt && \
    cd /opt && \
    rm -fr microsoft && \
    zip -r /python-odbc.zip .

Build failed:

Step 8/10 : ADD odbc.ini /root
ADD failed: stat /var/lib/docker/tmp/docker-builder209335974/odbc.ini: no such file or directory

Original repo's code with the cat invocations worked fine for .ini files. I'll post a workaround if anyone else is having this problem.

@jconway4 can you please post your workaround which works. I am figuring out how to install pyodbc for AWS Lambda and not successful. Maybe I will be successful with your workaround.

@thanuj11 Try using AWS SAM, put pyodbc in the requirements.txt and build with sam build --use-container. That should work fine for using pyodbc in a Lambda function, even testing locally should work.

@thanuj11 Try using AWS SAM, put pyodbc in the requirements.txt and build with sam build --use-container. That should work fine for using pyodbc in a Lambda function, even testing locally should work.

@ca0abinary Thanks for the reply, I tried using AWS SAM but still it fails with the error below
Build Failed
Error: PythonPipBuilder:ResolveDependencies - {pyodbc==4.0.25(sdist)}
Running PythonPipBuilder:ResolveDependencies

Details
python_verison:3.6
SAM template version:3.6

Any comments on how to make this work for AWS lambda will be a great help

@thanuj11 - these are the commands I use to build and create a lambda layer zip file. Simply including the uploaded lambda layer in your lambda function should enable you to use pyodbc without a requirements.txt.

docker build -t pyodbc .
docker run -d --name pyodbc pyodbc true
docker cp pyodbc:/python-odbc.zip ./python-odbc.zip
docker rm -f pyodbc

@ca0abinary I am facing a similar problem trying to run MSSQL from a Lambda using pyodbc. The instructions you mentioned above doesn' t seem to be building the container. I am trying to do it on my Mac

@rags1357 @thanuj11 @jconway4 Looks like the build image was updated which broke this script so I took the opportunity to fix and refactor:

Changes:

• .ini files are built within in the Dockerfile instead of being copied from the source directory
• Added adodbapi which is a python-only library to remind myself that it exists
• Updated the pymssql reference so it works

How to build:
This is my workflow, sorry if it is overly complicated for your needs:

docker build -t pyodbc .
docker run -d --name pyodbc pyodbc true
docker cp pyodbc:/python-odbc.zip .
docker rm -f pyodbc &> /dev/null

This should produce a python-odbc.zip in the current directory suitable for upload as a lambda layer.

@ca0abinary Thanks! Will give it a shot and let you know how it goes

Hello! @ca0abinary.,

So here is the steps i am following -
To the generated python-odbc.zip i add the lambda_function.py where i try to connect to the mssql server.
I zip the content with including the python file and upload it to lambda as zip.

When i try to run the test. It still throws me error as cannot import module "pyodbc" and also the driver not found.

Snippet of my connection code in "lambda_function.py" is as follows -

'''
try:
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL
Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

except pyodbc.DatabaseError as e:
print("A MSSQLDatabaseException has been caught.",e.message)
'''

Please let me know if I am missing something?

To be precise, the error is
{
"errorMessage": "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")",
"errorType": "Error",
"stackTrace": [
" File "/var/task/lambda_function.py", line 33, in lambda_handler\n cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)\n"
]
}

@rags1357 I've spent some time crafting a test scenario which can be run locally.

1. Create the zip file
2. Extract the zip file to PythonOdbc (this is important because AWS SAM doesn't handle .zip files)
3. Put the following template.yaml in the base directory such that it is at teh same level as the PythonOdbc directory:

   AWSTemplateFormatVersion: 2010-09-09
   
   Resources:
     Function:
       Type: AWS::Lambda::Function
       Properties:
         FunctionName: GcAuthUserMigration
         Timeout: 30
         InlineCode: |
           import pyodbc
   
           from os import environ
           server = environ.get('SERVER')
           username = environ.get('USERNAME')
           password = environ.get('PASSWORD')
   
           def handler(event, context):
               connectionString = f"Driver={{ODBC Driver 13 for SQL Server}};Server={server};Database=master;UID={username};PWD={password};"
               dbConnection = pyodbc.connect(connectionString)
               cursor = dbConnection.cursor()
               cursor.execute("select * from sys.databases")
               query_results = [dict(zip([column[0] for column in cursor.description], row)) for row in cursor.fetchall()]
               print(repr(query_results))
         Layers: [!Ref Layer]
         Handler: lambda.handler
         Runtime: python3.7
         Environment:
           Variables:
             SERVER: <<SQL Server Goes Here>>
             USERNAME: <<SQL Username Goes Here>>
             PASSWORD: <<SQL Password Goes Here>>
     Layer:
       Type: AWS::Serverless::LayerVersion
       Properties:
         ContentUri: PythonOdbc

4. Replace the <<SQL _ Goes Here>> environment variable with your SQL server location
5. Run sam local invoke in the directory with the template.yaml file

Has anyone tried to build this docker image with python3.8 base image of lambda execution environment ?
FROM lambci/lambda:build-python3.8, when i try the build it is failing with error :
---> Package unixODBC.x86_64 0:2.3.7-1.rh will be installed
--> Finished Dependency Resolution
Error: Package: msodbcsql-13.1.9.2-1.x86_64 (packages-microsoft-com-prod)
Requires: openssl

@Ranjith072 I just tested compiling with Python 3.8, the following changes need to be made:

• Line 1 should be changed to FROM lambci/lambda:build-python3.8
• Line 13 should be changed to yum -y install freetds e2fsprogs openssl && \
• Line 17 should be changed to pip install pyodbc requests adodbapi pyDes ptvsd --upgrade -t .
  • This removes support for pymsssql, which is no longer supported AFAIK
• Line 28 should be removed

I'm not going to update the gist yet because the pymssql package is removed. When support for Python 3.7 is removed from AWS Lamdba then I will update the gist.

Hi @ca0abinary , i have tried with the latest changes(though it compiles and creates a zipfile ) ,getting this error when running the lambda{
"errorMessage": "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/python/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2' : file not found (0) (SQLDriverConnect)")",
"errorType": "Error",
"stackTrace": [
" File "/var/task/lambda_function.py", line 6, in lambda_handler\n conn = pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server};"\n"
]
}

Hi @Ranjith072 , try replacing line 25 with:

Driver      = /msodbcsql/lib64/libmsodbcsql-13.1.so.9.2\n\

It looks like the location of the libmsodbc 13.1 dirver has changed.

Hi @ca0abinary the issue is still same but it is now pointing to the new driver location.
{
"errorMessage": "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2' : file not found (0) (SQLDriverConnect)")",
"errorType": "Error",
"stackTrace": [
" File "/var/task/lambda_function.py", line 6, in lambda_handler\n conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};'\n"
]
}

Hi @Ranjith072 can you run this command and verify your docker image has the file?

docker run --rm pyodbc:3.8 sh -c "find . -name libmsodbcsql-*"

My image name is pyodbc:3.8 but yours may be different. The result I got was:

./msodbcsql/lib64/libmsodbcsql-13.1.so.9.2

If you get a different value it would be good to use that in /root/odbc.ini instead. If you get no result that would indicate a build failure.

Hi @ca0abinary , i got the same ./msodbcsql/lib64/libmsodbcsql-13.1.so.9.2, not sure whats wronf , did u try to run it on a lambda ?

this is how the docker file looks after i made the changes u suggested:
FROM lambci/lambda:build-python3.8
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O &&
tar xvzf unixODBC-2.3.5.tar.gz &&
cd unixODBC-2.3.5 &&
./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home &&
make install &&
cd .. &&
mv /home/* . &&
mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo &&
yum -y install freetds e2fsprogs openssl &&
ACCEPT_EULA=Y yum -y install msodbcsql --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" &&
export LDFLAGS="-L/root/lib" &&
pip install pyodbc requests adodbapi pyDes ptvsd --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 13 for SQL Server]\n
Driver = ODBC Driver 13 for SQL Server\n
Description = My ODBC Driver 13 for SQL Server\n
Trace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 13 for SQL Server]\n
Description = Microsoft ODBC Driver 13 for SQL Server\n
Driver = /msodbcsql/lib64/libmsodbcsql-13.1.so.9.2\n
UsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python &&
cp -r /root/* /opt/python &&
mv /opt/python/lib /opt &&
mv /opt/python/bin /opt &&
cd /opt &&
rm -fr microsoft &&
zip -r /python-odbc.zip .

Hey @Ranjith072 the following Dockerfile works for me when using the Microsoft Example

FROM lambci/lambda:build-python3.8
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O && \
    tar xvzf unixODBC-2.3.5.tar.gz && \
    cd unixODBC-2.3.5 && \
    ./configure  --sysconfdir=/opt/python  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home && \
    make install && \
    cd .. && \
    mv /home/* . && \
    mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo && \
    yum -y install freetds e2fsprogs openssl && \
    ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" && \
    export LDFLAGS="-L/root/lib" && \
    pip install pyodbc requests adodbapi pyDes ptvsd --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Driver      = ODBC Driver 13 for SQL Server\n\
Description = My ODBC Driver 13 for SQL Server\n\
Trace       = No'  > /root/odbc.ini
RUN echo $'[ODBC Driver 13 for SQL Server]\n\
Description = Microsoft ODBC Driver 13 for SQL Server\n\
Driver      = /opt/python/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2\n\
UsageCount  = 1'  > /root/odbcinst.ini
RUN mkdir -p /opt/python && \
    cp -r /root/* /opt/python && \
    mv /opt/python/lib /opt && \
    mv /opt/python/bin /opt && \
    cd /opt && \
    zip -r /python-odbc.zip .

After building the image I ran this command successfully:

docker run --rm -it pyodbc:3.8 python -c 'import pyodbc; conn =pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server};SERVER=[SERVER_NAME];DATABASE=master;UID=[USERNAME];PWD=[PASSWORD]"); cursor = conn.cursor(); cursor.execute("SELECT @@version;"); row = cursor.fetchone(); print(row[0])'

The result (in my case) was:

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)
        Jul 12 2019 17:43:08
        Copyright (C) 2017 Microsoft Corporation
        Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Additionally at least in the 3.8 file ODBC driver 17 is also available with:

connection = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server}; ... ");

Hi @ca0abinary , it works for me as well when i run the docker image directly like you suggested above ,it works for both driver version 13 and 17 but when i create a zip file and use it in lambda as a layer its not working. i get the following error in both driver versions on python 3.8 . not sure if AWS has changed anything for python 3.8 execution environment.

i have even tried by setting:
Driver = /microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1\n
i always get the error:
"errorMessage": "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1' : file not found (0) (SQLDriverConnect)")",

Hey @Ranjith072 I've run out of time to look into this (work is really spinning up right now). Would you be willing to examine the contents of the zip file produced and recommend a better mv statement than what I have? I tried to look into the issue today but it looks like aws sam doesn't yet support InlineCode for python 3.8 making local testing very difficult.

Hi @ca0abinary sorry for the late response , i will try though i am not really good at docker.

Hi @ca0abinary finally this Dockerfile works for me with sqlserver version 17,

FROM lambci/lambda:build-python3.8
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O &&
tar xvzf unixODBC-2.3.5.tar.gz &&
cd unixODBC-2.3.5 &&
./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home &&
make install &&
cd .. &&
mv /home/* . &&
mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo &&
yum -y install freetds e2fsprogs openssl &&
ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" &&
export LDFLAGS="-L/root/lib" &&
pip install pyodbc requests adodbapi pyDes ptvsd --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 17 for SQL Server]\n
Driver = ODBC Driver 17 for SQL Server\n
Description = My ODBC Driver 17 for SQL Server\n
Trace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\n
Description = Microsoft ODBC Driver 17 for SQL Server\n
Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1\n
UsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python &&
cp -r /root/* /opt/python &&
mv /opt/python/lib /opt &&
mv /opt/python/bin /opt &&
cd /opt &&
zip -r /python-odbc.zip .

all needed to change is this line: Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1\n
because the way lambda accesses the lambda layer content.

Thanks for your work on figuring out the issue @Ranjith072!

Thanks @Ranjith072 and @ca0abinary !
FYI: I used this Dockerfile above to build a lambda layer (I'm on Catalina) to communicate with SQL Server, and it solved my issue. Below is a formatted version of the Dockerfile for anyone else who runs into this issue.

FROM lambci/lambda:build-python3.8
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O \
    && tar xvzf unixODBC-2.3.5.tar.gz \ 
    && cd unixODBC-2.3.5 \ 
    && ./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home \
    && make install \
    && cd .. \
    && mv /home/* . \
    && mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo \
    && yum -y install freetds e2fsprogs openssl \
    && ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" \
    && export LDFLAGS="-L/root/lib" \
    && pip install pyodbc requests adodbapi pyDes ptvsd --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1\nUsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python \
    && cp -r /root/* /opt/python \
    && mv /opt/python/lib /opt \
    && mv /opt/python/bin /opt \
    && cd /opt \
    && zip -r /python-odbc.zip .

@lkoivu-lsq Still not working

failed to solve with frontend dockerfile.v0: failed to build LLB: executor failed running [/bin/sh -c curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo && yum -y install freetds e2fsprogs openssl && ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*]: runc did not terminate sucessfully