Skip to content

Instantly share code, notes, and snippets.

@ca0abinary

ca0abinary/Dockerfile

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.
Download ZIP
Docker - Build pyOdbc Microsoft SQL AWS Lambda Layer
Raw
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 .
@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