Skip to content

Instantly share code, notes, and snippets.

@ca0abinary

ca0abinary/Dockerfile

Last active Sep 17, 2020
Embed
What would you like to do?
Docker - Build pyOdbc Microsoft SQL AWS Lambda Layer
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 .
@jconway4

This comment has been minimized.

Copy link

@jconway4 jconway4 commented Nov 5, 2019

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.

@thanuj11

This comment has been minimized.

Copy link

@thanuj11 thanuj11 commented Nov 13, 2019

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

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Nov 13, 2019

@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

This comment has been minimized.

Copy link

@thanuj11 thanuj11 commented Nov 15, 2019

@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

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Nov 18, 2019

@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
@rags1357

This comment has been minimized.

Copy link

@rags1357 rags1357 commented Nov 19, 2019

@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

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Nov 20, 2019

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

@rags1357

This comment has been minimized.

Copy link

@rags1357 rags1357 commented Nov 20, 2019

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

@rags1357

This comment has been minimized.

Copy link

@rags1357 rags1357 commented Nov 20, 2019

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?

@rags1357

This comment has been minimized.

Copy link

@rags1357 rags1357 commented Nov 20, 2019

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"
]
}

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Nov 21, 2019

@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
@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 17, 2020

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

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 17, 2020

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

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 17, 2020

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"
]
}

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 17, 2020

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.

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 18, 2020

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"
]
}

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 18, 2020

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.

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 18, 2020

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 ?

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 18, 2020

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 .

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 18, 2020

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)
@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 18, 2020

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}; ... ");
@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 19, 2020

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)")",

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 19, 2020

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.

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 21, 2020

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

@Ranjith072

This comment has been minimized.

Copy link

@Ranjith072 Ranjith072 commented Aug 25, 2020

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.

@ca0abinary

This comment has been minimized.

Copy link
Owner Author

@ca0abinary ca0abinary commented Aug 25, 2020

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

@lkoivu-lsq

This comment has been minimized.

Copy link

@lkoivu-lsq lkoivu-lsq commented Sep 15, 2020

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 .
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.