AWS Lambda function to connect to SQL Server with Python

Question

I've been stuck trying to connect to an SQL Server using AWS Lambda functions for a long while now.

To do so i'm trying to use any library (tried with pyodbc, pypyodbc, etc), packaging everything into a zip file and uploading the code.

The code is pretty much the same for every library, but the errors are different.

The code:

import pypyodbc

def lambda_handler(event, context):
    conn = pypyodbc.connect('DRIVER={SQL Server};'
                      'SERVER=1.1.1.1;'
                      'DATABASE=dbname;'
                      'UID=user;'
                      'PWD=pwd')

    cur = conn.cursor()

    cur.execute("SELECT * FROM Table")

    item_count = 0

    for row in cur:
        item_count += 1

    print(item_count)

    cur.close()
    conn.close()

    return item_count

Common issues that i have covered: - I'm adding to the zip the project contents, not the folder. - I'm also adding to the zip file the libraries needed for the code to run.

If i try to use pyodbc, the zip i'm uploading looks like this:

.idea (dir)
pyodbc (dir)
lambda_function.py
pyodbc.pyd

The error i get:

Unable to import module 'lambda_function': No module named pyodbc

After searching for quite a while about this, i couldn't find anything that helps. Only one comment that said that pyodbc needed to be instaled on a linux enviroment for the lambda function to work. But i don't have that Enviroment available, also i don't know if that will fix this.

If i try to use pypyodbc, the zip i'm uploading looks like this:

The error i get:

module initialization error: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

For this one i tried to install multiple python packages suggested by other stackoverflow posts (python-pyodb, unixodbc), but i failed every time.

Then there was one comment around saying "Make sure to put native ODBC libraries under the lib folder in your zip deployment package"

Maybe that is some help? I don't know how to get native ODBC libraries..

Oh and one last thing. Both libraries work if i run them from my local machine. I can get access to the target server. It fails if i do it from the lambda function.

Hopefully someone can help me and, apparently, the whole internet with this.

Solution

• you need to know Lambda copy your function in local /var/task/
• create a instance using Lambda official AMI https://docs.aws.amazon.com/lambda/latest/dg/current-supported-versions.html
• start instance, login
• yum install gcc gcc-c++
• go in to /home/ec2-user
• Download the last unixodbc manager from: ftp://ftp.unixodbc.org/pub/unixODBC/
• wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz
• tar xvzf unixODBC-2.3.5.tar.gz
• cd unixODBC-2.3.5

• configure it with the correct sysconfdir value

  ./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home

• make install
• Go to /home dir and copy bin,include,lib,share directory on your computer where the Lambda project is (ex: C:\AWS\Lambda\sql_query)
• install on your EC2 instance the the Microsoft driver libmsodbcsql-13.1.so.9.1 and then copy the driver file on your PC local directory (ex: C:\AWS\Lambda\sql_query\msodbcsql\msodbcsql\lib64 )
• Take a look https://blogs.msdn.microsoft.com/sqlnativeclient/2017/02/04/odbc-driver-13-1-for-linux-released/
• On your computer, in the same root directory create file odbcinst.ini

[ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/var/task/msodbcsql/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1 UsageCount=1

• On your computer, in the same root directory create file odbc.ini

  [ODBC Driver 13 for SQL Server] Driver = ODBC Driver 13 for SQL Server Description = My ODBC Driver 13 for SQL Server Trace = No

• on your python program use pyodbc:

  import pyodbc def lambda_handler(event, context): server = "xxxxxxxxxxxxxxxxxxxx" database = "xxxxxxxxxxxxxxxxxxxx" username = "xxxxxxxxxxxxxxxxxxxx" password = "xxxxxxxxxxxxxxxxxxxx" cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() ...other things....

• and now play the game !

Comments

[Emiliano Rodriguez]

Thanks for taking the time Max, i'm not working on this anymore but there sure will be people finding this of help.

[unixeo]

@maxmonterumisi, do you have the code in some repo? I was follow the instructions but didn't work. Could you share the structure? Thanks

[Carlochess]

Hey, you can follow this instructions gist.github.com/carlochess/658a98589709f46dbb3d20502e48556b

[Aakash Basu]

Hey Max. I tried with most of it but still stuck. Can you help me in this please: stackoverflow.com/questions/58365543/…