Connect AWS Lambda to RDS SQL Server with pyodbc

Brian Fitzgerald

7 years ago

By Brian Fitzgerald

Introduction

We want to connect Lambda to Microsoft SQL Server RDS using python ODBC connnector pyodbc. pyodbc calls the Microsoft SQL Server driver, which sits on top of linuxODBC. Installing ODBC drivers into AWS Lambda has frustrated some users in the past. This blog outlines a simple approach.

Staging on EC2

We’re going to create a complete set of files for uploading to Lambda. We’ll stage those files on EC2, zip them, and upload the zip to Lambda.

Create RDS

For this article, I created Microsoft SQL Server RDS instance, as described in this table.

Parameter	Value
Instance name	odbcblog
Engine	SQL Server Express Edition
Engine version	14.00.3049.1.v1
Class	db.t2.micro
security group	sg-04ed8240
Endpoint IP address	odbcblog.p0p3rwmlj3hf.us-east-1.rds.amazonaws.com
Endpoint Port	1433
Master user	odbcuser
Master password	odbcuser

connectivity

Testing from EC2, I get:

[ec2-user@ip-172-251-80-17 ~]$ nc -v odbcblog.p0p3rwmlj3hf.us-east-1.rds.amazonaws.com 1433
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connection timed out.

RDS instance odbcblog is in security group sg-04ed8240. After associating security group sg-04ed8240 to our EC2, we are good to go:

[ec2-user@ip-172-251-80-17 ~]$ nc -v odbcblog.p0p3rwmlj3hf.us-east-1.rds.amazonaws.com 1433
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 172.251.58.192:1433.

install SQL Server ODBC

[ec2-user@ip-172-251-80-17 ~]$ sudo bash

[root@ip-172-251-80-17 download]# curl packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
[root@ip-172-251-80-17 download]# yum -y install msodbcsql17

Review the output and notice that dependent package unixODBC also gets installed.

 Installing : unixODBC-2.3.1-11.amzn2.0.1.x86_64

We’ll use that fact later.

Notice the /etc/odbcinst.ini entry:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

install pyodbc

[root@ip-172-251-80-17 download]# yum -y install gcc-c++
[root@ip-172-251-80-17 download]# yum -y install python3-devel
[root@ip-172-251-80-17 download]# yum -y install unixODBC-devel
[root@ip-172-251-80-17 download]# pip3 install pyodbc
WARNING: Running pip install with root privileges is generally not a good idea. 
 Try `pip3 install --user` instead.

test pyodbc from EC2

testodbc.py:

import pyodbc

con = pyodbc.connect(
    driver = 'ODBC Driver 17 for SQL Server',
    server = 'odbcblog.p0p3rwmlj3hf.us-east-1.rds.amazonaws.com',
    port = 1433,
    user = 'odbcuser',
    password = 'odbcuser',
    timeout = 5
)
sql = 'select @@version'
crsr = con.cursor()
crsr.execute(sql)
row = crsr.fetchone()
print (row[0])

Execute:

[ec2-user@ip-172-251-80-17 test]$ python3 testodbc.py

Output:

Microsoft SQL Server 2017 (RTM-CU13-OD) (KB4483666) - 14.0.3049.1 (X64)
Dec 15 2018 11:16:42
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0  
  (Build 14393: ) (Hypervisor)

cool.

Stage Lambda code on EC2

Download packages

Let’s start over and download the packages

[ec2-user@ip-172-251-80-17 download]$ yumdownloader unixODBC.x86_64
[ec2-user@ip-172-251-80-17 download]$ yumdownloader msodbcsql17
[ec2-user@ip-172-251-80-17 download]$ pip3 download pyodbc
[ec2-user@ip-172-251-80-17 download]$ ls -1
msodbcsql17-17.3.1.1-1.x86_64.rpm
pyodbc-4.0.26.tar.gz
unixODBC-2.3.1-11.amzn2.0.1.x86_64.rpm

Identify a Lambda staging directory on EC2

[ec2-user@ip-172-251-80-17 testodbc]$ mkdir -p /home/ec2-user/lambdas/testodbc
[ec2-user@ip-172-251-80-17 testodbc]$ cd /home/ec2-user/lambdas/testodbc

Install the rpms

[ec2-user@ip-172-251-80-17 testodbc]$ rpm2cpio /home/ec2-user/lambdas/download/unixODBC-2.3.1-11.amzn2.0.1.x86_64.rpm | cpio -id
2504 blocks
[ec2-user@ip-172-251-80-17 testodbc]$ rpm2cpio /home/ec2-user/lambdas/download/msodbcsql17-17.3.1.1-1.x86_64.rpm | cpio -id
4486 blocks

Install pyodbc

Create a python library directory

[ec2-user@ip-172-251-80-17 lib]$ mkdir -p /home/ec2-user/lambdas/testodbc/python/lib

Install

[ec2-user@ip-172-251-80-17 ~]$ pip3 install --target /home/ec2-user/lambdas/testodbc/python/lib /home/ec2-user/lambdas/download/pyodbc-4.0.26.tar.gz

Directory structure

Observe the directory structure so far

[ec2-user@ip-172-251-80-17 ~]$ cd /home/ec2-user/lambdas/testodbc
[ec2-user@ip-172-251-80-17 testodbc]$ ls -1F
etc/
opt/
python/
usr/
[ec2-user@ip-172-251-80-17 testodbc]$ cd usr/
[ec2-user@ip-172-251-80-17 usr]$ ls -1F
bin/
lib64/
share/

Library directory

The Lambda function is going to load pyodbc. pyodbc is going to look for libodbc.so.2, but it is not going to search usr/lib64. It will do you no good to set Lambda’s LD_LIBRARY_PATH because the Lambda’s containing runtime starts before the Lambda’s environment gets set. Lambda will search lib, so move the library directory there:

[ec2-user@ip-172-251-80-17 testodbc]$ mv usr/lib64 lib

odbcinst.ini

AWS will install our Lambda code in a virtual machine under /var/task. Edit odbcinst.ini

[ec2-user@ip-172-251-80-17 testodbc]$ vi etc/odbcinst.ini

Replace the contents:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/var/task/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

Python application code directory

To avoid clutter, we will put our own application code in a subdirectory.

[ec2-user@ip-172-251-80-17 testodbc]$ mkdir py

Summary

The directory structure at the top level is now:

[ec2-user@ip-172-251-80-17 testodbc]$ ls -1F
etc/
lib/
opt/
py/
python/
usr/

Create the lambda code

File:

[ec2-user@ip-172-251-80-17 testodbc]$ vi py/testodbc.py

Contents:

import pyodbc
from json import dumps

def lam(ev, cx):
    con = pyodbc.connect(
        driver = 'ODBC Driver 17 for SQL Server',
        server = 'odbcblog.p0p3rwmlj3hf.us-east-1.rds.amazonaws.com',
        port = 1433,
        user = 'odbcuser',
        password = 'odbcuser',
        timeout = 5
    )
    sql = 'select @@version'
    crsr = con.cursor()
    crsr.execute(sql)
    row = crsr.fetchone()
    version = row[0]
    ret = {
	'version': version
    }
    return dumps(ret)

The handler will, therefore be testodbc.lam

Fun fact: You cannot name an AWS Lambda python handler “lambda”.

Create the Lambda

Initial creation

Create a basic lambda function by any method. For example, use the Lambda console.

Configuration	Value
Name	testOdbc
Runtime	python 3.7
Timeout	5 minutes
Handler	testodbc.lam

Set two environment variables:

variable	value
ODBCSYSINI	/var/task/etc
PYTHONPATH	/var/runtime:/var/task/py:/var/task/python/lib

Code upload

Zip all libraries, configuration files, and code:

[ec2-user@ip-172-251-80-17 testodbc]$ zip -rq ../testodbc.zip *

Upload the files

[ec2-user@ip-172-251-80-17 testodbc]$ aws lambda update-function-code
   --function-name testOdbc
   --zip-file fileb://../testodbc.zip

Networking

This section must be handled with care. Otherwise, you are going to get ODBC driver timeouts. For Lambda to successfully connect to RDS, two conditions must be in place.

Elastic Network Interface

Lambda needs basic execution role for basic Cloudwatch access. In addition, Your Lambda needs to be able to bind to an Elastic Network Interface.

In IAM Console, create a new role having these roles. Ex: odbcLamRole

AWSLambdaBasicExecutionRole
AWSLambdaENIManagementAccess

In Lambda Console, assign the role to the Lambda.

VPC

In Lambda console in the Network pane, if you see “No VPC”, switch to your VPC. select two or more subnets, and select your security group.

Review

You may review the configuration from the CLI.

[ec2-user@ip-172-251-80-17 ~]$ aws lambda get-function-configuration --function-name testOdbc

Output:

{
    "FunctionName": "testOdbc",
    "LastModified": "2019-05-02T20:29:51.551+0000",
    "RevisionId": "df676edb-e545-42dc-90c3-0cf5dc16ed81",
    "MemorySize": 128,
    "Environment": {
        "Variables": {
            "PYTHONPATH": "/var/runtime:/var/task/py:/var/task/python/lib",
            "ODBCSYSINI": "/var/task/etc"
        }
    },
    "Version": "$LATEST",
    "Role": "arn:aws:iam::665575760545:role/odbcLamRole",
    "Timeout": 300,
    "Runtime": "python3.7",
    "TracingConfig": {
        "Mode": "PassThrough"
    },
    "CodeSha256": "VV3g7pLL1G+y3PoEPyX+UcbwMn40KIiOUbCu5ApYowM=",
    "Description": "",
    "VpcConfig": {
        "SubnetIds": [
            "subnet-8c036bd0",
            "subnet-b7214ed0",
            "subnet-aa197384",
            "subnet-364a757c",
            "subnet-af9a2991",
            "subnet-0f476600"
        ],
        "VpcId": "vpc-0d398177",
        "SecurityGroupIds": [
            "sg-04ed8240"
        ]
    },
    "CodeSize": 2322598,
    "FunctionArn": "arn:aws:lambda:us-east-1:665575760545:function:testOdbc",
    "Handler": "testodbc.lam"
}

run the Lambda

command:

[ec2-user@ip-172-251-80-17 ~]$ aws lambda invoke --function-name testOdbc out.json

cli output:

{
    "ExecutedVersion": "$LATEST",
    "StatusCode": 200
}

Lambda return:

[ec2-user@ip-172-251-80-17 ~]$ cat out.json
"{\"version\": \"Microsoft SQL Server 2017 (RTM-CU13-OD) (KB4483666) - 14.0.3049.1 (X64) \\n\\tDec 15 2018 11:16:42 \\n\\tCopyright (C) 2017 Microsoft Corporation\\n\\tExpress Edition (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)\\n\"}"

The Lambda performed these steps

Load all application code and dependent libraries
Load Unix ODBC driver
In the handler, load the MS ODBC driver
Connect to the RDS SQL Server
Allocate a cursor
Execute a SQL statement
Retrieve the result set
Parse the result set as a version
Return the version as JSON from the lambda handler

Summary

We accomplished these items

setup EC2, RDS, and Lambda in a VPC
install pyodbc and underlying drivers in EC2
test python code by connecting from EC2 to RDS
stage all needed drivers, configuration files python libraries, and python application code on EC2
upload the code to Lambda
run the Lambda

We have therefore established connectivity from a Python Lambda to a SQL Server RDS.

Introduction

Staging on EC2

Create RDS

connectivity

install SQL Server ODBC

install pyodbc

test pyodbc from EC2

Stage Lambda code on EC2

Download packages

Identify a Lambda staging directory on EC2

Install the rpms

Install pyodbc

Create a python library directory

Install

Directory structure

Observe the directory structure so far

Library directory

odbcinst.ini

Python application code directory

Summary

Create the lambda code

Create the Lambda

Initial creation

Code upload

Networking

Elastic Network Interface

VPC

Review

Summary

Share this: