Cannot find ODBC driver in AWS Lambda when using unixODBC

Question

Objective: Connect to a MS SQL Server in AWS Lambda

Error From AWS Lambda:

START RequestId: 37951004-404b-11e7-98fd-5177b3a46ec6 Version: $LATEST
module initialization error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")
END RequestId: 37951004-404b-11e7-98fd-5177b3a46ec6

My Approach:

refering to Tesseract OCR on AWS Lambda via virtualenv

1. Installed unixODBC and ODBC Driver 13 for SQL Server in an aws ec2 instance
2. Created a deployment package (i.e. pip install -t pyodbc /home/ec2-user/lambda and copied the relevant files to zip root)
3. mkdir lib and copied all shared libraries by looking up ldd pyodbc.so * ldd libmsodbcsql-13.0.so.1.0
4. change LD_LIBRARY_PATH to lib in lambda.py and upload the zip to Lambda

Zip File Structure:

.
+-- lambda.py
+-- pyodbc.so
+-- pyodbc-4.0.16-py2.7.egg-info
+-- lib
|   +-- libodbc*.so etc.

My Guess:

From the response it looks like everything is running OK except for the unixODBC manager cannot find the driver on AWS Lambda Instance

Question:

How do I bundle the driver with my package for AWS Lambda to use? Or Is there a way to tell ODBC driver manager to look for libmsodbcsql-13.0.so.1.0?

I have tried offline installation of unixODBC and set ./configure --prefix=/home/ec2-user/lambda --libdir=.. and manually changing odbcinst.ini but no hope.

---

(sorry I am not so familiar with anything outside python environment so I am not sure about all the shared libraries stuff and couldn't solve my problem by just googling for a few days)

Answer 1

Compilation Approach

By default unixODBC looks for odbc.ini inside /etc/. In a Lambda function, you can't modify that. (Well, perhaps you can from the python script at runtime, but that's not ideal.)

If you're compiling unixODBC yourself, you can try adding an extra argument to ./configure, --sysconfdir, as well as --prefix. See here for more detail.

Personally I was putting odbc inside a lambda layer, which goes to /opt. I think the lambda itself is unzipped to /var/task. This means that when you compile on EC2, you should point to /var/task. (You might have to manually create this directory if it doesn't yet exist.)

./configure --sysconfdir=/var/task --prefix=/var/task

Simple Yum Approach

Personally I found that it's easier to just yum install unixODBC, and then /usr/lib64/libodbc* to /lib/ inside the zip, and /opt to / inside the zip, and /usr/bin/odbc* to /bin/ inside the zip. Then the only extra trick was to set environment variables in the lambda.

ODBCINI=/var/task/odbc.ini
ODBCSYSINI=/var/task/

Answer 2

Is there a way to tell ODBC driver manager to look for libmsodbcsql-13.0.so.1.0?

You can run odbcinst -j and check where the driver ini file is located (odbcinst.ini) so you can set the path there. Otherwise create the new one at ~/.odbcinst.ini which can define the path to SQL ODBC driver library.

Example INI config file:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/path/to/libmsodbcsql-13.0.so.1.0

Here is the example command to create the config file (~/.odbcinst.ini):

printf "[ODBC Driver 13 for SQL Server]\nDescription=Microsoft ODBC Driver 13 for SQL Server\nDriver=/path/to/libmsodbcsql-13.0.so.1.0\n" >> ~/.odbcinst.ini

Anaconda

If the driver file exists, but still you got file not found, run: conda update libgcc.

See: PyODBC : can't open the driver even if it exists.