-
-
Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
# 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 |
@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!
@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.