Connecting AWS Lambda to Microsoft SQL Server Using PyODBC

by

In this guide, we’ll walk you through the steps to connect AWS Lambda to Microsoft SQL Server using PyODBC. PyODBC leverages a Driver Manager called unixODBC, which allows the loading of different ODBC drivers published by Microsoft for various versions of SQL Server.

Before you start, ensure you have the following:

  • AWS Account with access to Lambda, VPC, and EC2.
  • Microsoft SQL Server instance (on-premises or AWS EC2).
  • AWS Lambda Setup with correct VPC and IAM permissions.
  • VPN/VPC Connectivity between Lambda and the SQL Server.
  • Security Groups allowing inbound traffic (port 1433) from the VPN or Lambda.
  • SQL Server Credentials (username, password, connection string).

These are the step-by-step process what you need to do:

  1. Start a Container Environment.
  2. Download and Compile unixODBC.
  3. Download and Install the Microsoft SQL Server ODBC Driver.
  4. Install PyODBC.
  5. Copy the MS SQL Driver for unixODBC.
  6. Create the odbcinst.ini and odbc.ini Files.
  7. Test the Connection.
  8. Package the Project into a Zip File.
  9. Upload to Lambda Layer Using Serverless Framework.
  10. AWS Infrastructure Deployment: Connecting Lambda to a VPC

Step 1: Start a Container Environment

First, start a container environment using Docker:

docker run -it --rm --entrypoint bash -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task public.ecr.aws/sam/build-python3:latest

Step 2: Download and Compile unixODBC

Download and compile unixODBC:

curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.12.tar.gz -O
tar xvzf unixODBC-2.3.12.tar.gz
cd unixODBC-2.3.12
./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home
make install
cd ..
mv /home/* .
mv unixODBC-2.3.12 unixODBC-2.3.12.tar.gz /tmp/

Step 3: Download and Install the Microsoft SQL Server ODBC Driver

Follow the guide for installing the Microsoft ODBC 17 driver: Microsoft ODBC 17 Full Guide

For the public.ecr.aws/sam/build-python3 image:

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17
export CFLAGS="-I/var/task/include"
export LDFLAGS="-L/var/task/lib"

Step 4: Install PyODBC

Install PyODBC in the current directory:

pip install pyodbc -t .

Step 5: Copy the MS SQL Driver for unixODBC

Copy the MS SQL driver for unixODBC:

cp -r /opt/microsoft/msodbcsql17 .

Step 6: Create the odbcinst.ini and odbc.ini Files

Create the odbcinst.ini file:

cat <<EOF > odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
UsageCount=1
EOF

Create the odbc.ini file:

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

Step 7: Test the Connection

Create a Python file named function.py:

nano function.py

Copy the following content into function.py, and remember to edit the necessary variables:

import pyodbc

driver = '{ODBC Driver 17 for SQL Server}'
sqlServer = 'your_sql_server'
sqlDatabase = 'your_database'
sqlPort = 1433
sqlUsername = 'your_username'
sqlPassword = 'your_password'

def test_pyodbc():
    print(pyodbc.drivers())
    print('Attempting Connection...')
    conn = pyodbc.connect(f"DRIVER={driver};SERVER={sqlServer};PORT={sqlPort};DATABASE={sqlDatabase};UID={sqlUsername};PWD={sqlPassword}")
    print('Connected!!!')

if __name__ == "__main__":
    test_pyodbc()

Run the script with Python:

python ./function.py

You should see the following output:

ODBC Driver 17 for SQL Server
Attempting Connection...
Connected!!!
NOTE: I ran this script locally and used an AWS VPN to connect to my private MS SQL server, which is hosted on an EC2 instance. Make sure your VPN is correctly routed to the private subnets and VPC in AWS where your EC2 instance is located. Additionally, verify that the inbound ports on your MS SQL server are configured to allow connections from the private VPN

Step 8: Package the Project into a Zip File

Once everything is tested and working, package the project into a zip file for Lambda:

First, zip the contents of the current directory:

zip -r9 ../pyodbc-layer.zip .

Next, go up a level to the zip file:

cd ..

(Optional) If you wish to add any further scripts or packages to this zip, use the following command:

zip -g pyodbc-layer.zip <path to file to add>

Step 9: Upload to Lambda Layer Using Serverless Framework

Update your serverless.yml configuration to include the new layer:

layers:
  MSSQLPyODBC:
    name: ${sls:stage}-mssqlpyodbc-layer
    description: PyODBC library requirements for DB connection
    compatibleRuntimes: python3.11
    compatibleArchitectures: x86_64
    package:
      artifact: layers/pyodbc-layer.zip
    retain: false

functions:
  yourLambdaFunction:
    handler: handler.yourHandler
    layers:
      - !Ref MSSQLPyODBC

Step 10: AWS Infrastructure Deployment: Connecting Lambda to a VPC

Below is a sample diagram illustrating the setup for AWS infrastructure deployment, showing how a Lambda function connects to another VPC. If you are using a single VPC, a VPN is not required, making the setup straightforward. You can directly connect your Lambda function to the MS SQL Server if the same VPC is used.

In case you encounter any issues, please ensure the following:

  1. Database Availability: Verify that your database is operational.
  2. Lambda VPC Attachment: Ensure the Lambda function is properly attached to the VPC and private subnets.
  3. Correct Routing: Confirm that the Transit Gateway or VPN are correctly routed within the VPC AWS account.
  4. Security Group Configuration: Check that the security group of the database server has its inbound port open to the Lambda function, allowing private access.
  5. Further Assistance: If further assistance is needed, contact the Linuxbeast team.

Conclusion

By following these steps, you can successfully connect AWS Lambda to Microsoft SQL Server using PyODBC. This guide provides a clear, step-by-step approach to setting up the necessary environment, testing the connection, and deploying the configuration using the Serverless Framework.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.