pyodbc and unixODBC for MSSQL as a lambda layer

pyodbc-unixODBC-lambda-layer

# use https://github.com/lambci/docker-lambda to simulate a lambda environment
docker run -it --rm --entrypoint bash -e ODBCINI=/opt/odbc.ini -e ODBCSYSINI=/opt/ lambci/lambda:build-python3.7

# download and install unixODBC
# http://www.unixodbc.org/download.html
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.7.tar.gz -O
tar xzvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7

./configure --sysconfdir=/opt --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/opt
make
make install

cd ..
rm -rf unixODBC-2.3.7 unixODBC-2.3.7.tar.gz

# download and install ODBC driver for MSSQL 17
# https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
yum install e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 fuse-libs.x86_64 0:2.9.4-1.18.amzn1 libss.x86_64 0:1.43.5-2.43.amzn1
ACCEPT_EULA=Y yum install msodbcsql17 --disablerepo=amzn*
export CFLAGS="-I/opt/include"
export LDFLAGS="-L/opt/lib"

cd /opt
cp -r /opt/microsoft/msodbcsql17/ .
rm -rf /opt/microsoft/

# install pyodbc for use with python.
# Notice the folder structure to support python 3.7 runtime 
# https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path
mkdir /opt/python/
cd /opt/python/
pip install pyodbc -t .

cd /opt
cat <<EOF > odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1
EOF

cat <<EOF > odbc.ini
[ODBC Driver 17 for SQL Server]
Driver = ODBC Driver 17 for SQL Server
Description = My ODBC Driver 17 for SQL Server
Trace = No
EOF

# package the content in a zip file to use as a lambda layer
cd /opt
zip -r9 ~/pyodbc-layer.zip .

# to test it locally:
# unzip the content of your layer to your local environment, to /var/opt/ for example:
unzip pyodbc-layer.zip -d /var/opt/

# In your local environment, have your lambda function handy at /var/task/lambda_function.py

import pyodbc
server = 'myserver'
database =  'mydb'
username = 'myuser'
password = 'mypwd'

def lambda_handler(event, context):
    # TODO implement
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
    cursor.execute("select @@version;")
    row = cursor.fetchone()
    while row:
        print(row[0])
        row = cursor.fetchone()

# Time to test
docker run --rm -v /var/task:/var/task -v /var/opt:/opt lambci/lambda:python3.7 lambda_function.lambda_handler '{"some": "event"}'

# useful links
https://medium.com/devopslinks/aws-lambda-microsoft-sql-server-how-to-66c5f9d275ed
https://stackoverflow.com/questions/47682991/aws-lambda-function-to-connect-to-sql-server-with-python
https://gist.github.com/carlochess/658a98589709f46dbb3d20502e48556b

nevermind. all good. If anyone else has the issue, i added
RUN yum -y install libtool-ltdl
before
RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*

Hey guys, I created a layer as a .zip with pyodbc, unix and mssql odbc drivers that work on python 3.11 runtime. Uses mssqlodbc17 drivers.

https://github.com/andersonguse/lambda-layers

thanks @alexanderluiscampino , saved my 1 day time

Here's a github repo with automated builds of the the ODBC 18 drivers for Python 3.11 and 3.12.
Prebuilt packages and Dockerfile for x86_64 and ARM64

https://github.com/davidcomerford/pyodbc-mssql-lambda-layer

@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.

@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.

You're very welcome

@davidcomerford - great thanks for this. I can use this as a lambda layer and it works fine. Using your https://github.com/davidcomerford/pyodbc-mssql-lambda-layer for Python 3.11.

But for some reason - I can't get the same drivers to work in a container lambda.

[ERROR] OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Ive also tried copying then (trying to mimic layer behaviour) but this has also failed:

FROM public.ecr.aws/lambda/python:3.11
COPY --from=builder /opt/ /opt/

COPY requirements.txt ./
RUN pip3 install -r requirements.txt -t /var/task

Really appreciate any ideas on how to get this to work in a lambda container :)

@mohaque123, the drivers loaded fine, that's how your getting that error message from them.

Your problem looks specific to the SQL Server it's failing to connect to. Check your server's ports are accessible from where ever the Lambda function is running from inside the VPC e.g. security groups, routing, NACLs, etc.

@davidcomerford - This has been a huge help. I've been able to use your zip and build it using the Dockerfile to create a lambda layer https://github.com/davidcomerford/pyodbc-mssql-lambda-layer/blob/main/Dockerfile.python.3.12.x86_64 for Python 3.12.

I am able to fully query the database using docker locally, but I am only able to query the version as in your example from the lambda function. A minimal query that should return the Top 1 from a column containing char(6) instead results in this error:

('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed (22) (SQLGetData)')

I've seen recommendations to install glibc-gconv-extra but I've been unable to successfully edit your Dockerfile to accomplish this.

Any help would be greatly appreciated!