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 .

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