Skip to content

Instantly share code, notes, and snippets.

Last active April 28, 2024 19:35
Show Gist options
  • Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
pyodbc and unixODBC for MSSQL as a lambda layer
# use 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
curl -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 install
cd ..
rm -rf unixODBC-2.3.7 unixODBC-2.3.7.tar.gz
# download and install ODBC driver for MSSQL 17
curl > /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
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
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
# package the content in a zip file to use as a lambda layer
cd /opt
zip -r9 ~/ .
# to test it locally:
# unzip the content of your layer to your local environment, to /var/opt/ for example:
unzip -d /var/opt/
# In your local environment, have your lambda function handy at /var/task/
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:
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
Copy link

mohaque123 commented Apr 28, 2024

@davidcomerford - great thanks for this. I can use this as a lambda layer and it works fine. Using your 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:

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 :)

Copy link

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

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