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:
- Start a Container Environment.
- Download and Compile unixODBC.
- Download and Install the Microsoft SQL Server ODBC Driver.
- Install PyODBC.
- Copy the MS SQL Driver for unixODBC.
- Create the odbcinst.ini and odbc.ini Files.
- Test the Connection.
- Package the Project into a Zip File.
- Upload to Lambda Layer Using Serverless Framework.
- 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:
- Database Availability: Verify that your database is operational.
- Lambda VPC Attachment: Ensure the Lambda function is properly attached to the VPC and private subnets.
- Correct Routing: Confirm that the Transit Gateway or VPN are correctly routed within the VPC AWS account.
- Security Group Configuration: Check that the security group of the database server has its inbound port open to the Lambda function, allowing private access.
- 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.