I have been struggling, and am unable to figure out how to connect to SQL Server using python 3.6 via pydobc and AWS Lambda.
I followed the instructions provided by AWS to create an Amazon Linux AMI on which I was able to install the Microsoft ODBC drivers (v13 and v17), upgrade the unixODBC to a supported version, and get my python code to connect to the AWS RDS SQL Server instance.
However, I have not been able to figure out how to package those changes successfully to deploy this code to AWS Lambda and have it work.
I get one of two errors, depending on how I try to reference the ODBC driver. Using this syntax
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername.account.region.rds.amazonaws.com,port;DATABASE=database;UID=user;PWD=password'
I get the error:
"errorMessage": "('01000', \"[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)\")",
I have tried using other driver aliases (ODBC Driver 17 for SQL Server, ODBC Driver 13 for SQL Server), with the same results.
Using the syntax:
cnxn = pyodbc.connect('DRIVER=lib/libmsodbcsql-13.so;SERVER=servername.account.region.rds.amazonaws.com,port;DATABASE=database;UID=user;PWD=password')
I get the error:
'IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)")
I have the following code in my .ZIP deployment file:
- simple_db.py - My code to create the connection
- pyodbc.so - Lambda version of pyodbc from https://github.com/Miserlou/lambda-packages/tree/master/lambda_packages/pyodbc)
- odbcinst.ini - Attempt to use a Linux-version of odbcinst to list the driver(s).
- lib/libmsodbcsql-13.so - Copied from the Amazon Linux install
- libodbc.so.2 - Copied from Amazon Linux install as well, attempt to deploy unixODBC version.
I've toyed around with directories, and adding more libodbc*.* files from the /usr/lib64 directory, but nothing has worked so far. As well as bringing over the entire msodbcsql directory (with /etc, /include, /lib64, and /share).
Any help would be greatly appreciated!