Skip to content

Instantly share code, notes, and snippets.

@ca0abinary
Last active December 2, 2020 06:10
Show Gist options
  • Save ca0abinary/e4825841d47d987ffc78ed62e5619055 to your computer and use it in GitHub Desktop.
Save ca0abinary/e4825841d47d987ffc78ed62e5619055 to your computer and use it in GitHub Desktop.
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 .
@Ranjith072
Copy link

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
Copy link
Author

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
Copy link

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
Copy link
Author

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
Copy link

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
Copy link

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
Copy link
Author

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
Copy link
Author

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
Copy link

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
Copy link
Author

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
Copy link

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

@Ranjith072
Copy link

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
Copy link
Author

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

@lkoivu-lsq
Copy link

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 .

@keyur-cp
Copy link

keyur-cp commented Nov 7, 2020

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment