In Windows, if you want to connect to SQL Server don't need to install anything. But Linux has to install unixODBC first. Unluckily AWS Lambda is more difficult than both because lambda's environment is provided by AWS, and you can't control it.

Here is a somewhat painful method, but it works.

Steps

Create a ec2, install docker and launch docker (in ec2)

use AWS lambda image to launch docker and set environment variables. The reason of use AWS lambda images because is similar to the lambda environment.

[ec2-user@ip ~]$ sudo yum install docker
[ec2-user@ip ~]$ sudo service docker start
[ec2-user@ip ~]$ sudo docker run -it --rm --entrypoint bash  -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task  lambci/lambda:build-python3.7

Download unixODBC and install (in docker)

unixODBC version need bigger than 2.1

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

confirm install and copy into /var/task

bash-4.2# cd /home/bin
bash-4.2# ./odbcinst -j
unixODBC 2.3.5
DRIVERS............: /var/task/odbcinst.ini
SYSTEM DATA SOURCES: /var/task/odbc.ini
FILE DATA SOURCES..: /var/task/ODBCDataSources
USER DATA SOURCES..: /var/task
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
bash-4.2#
bash-4.2# cd /var/task
bash-4.2# mv /home/* .
bash-4.2# mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp/

Install MSSQL odbc driver and pyodbc (in docker)

when install msodbcsql will show not found unixODBC, use skip-broken skip check because our unixODBC is copy into /var/task.

bash-4.2# curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
bash-4.2# yum install e2fsprogs
bash-4.2# ACCEPT_EULA=Y yum install mssql-tools unixODBC-devel -y --disablerepo=amzn*
bash-4.2# export CFLAGS="-I/opt/include"
bash-4.2# export LDFLAGS="-L/opt/lib"

install pyodbc

bash-4.2# pip install pyodbc -t .
bash-4.2# cp -r /opt/microsoft/msodbcsql17/ .
bash-4.2# vi odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/var/task/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
UsageCount=1
bash-4.2#
bash-4.2# vi 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

test and wrap the /var/task

bash-4.2# python -c "import pyodbc; print(pyodbc.drivers());"
['ODBC Driver 17 for SQL Server']
bash-4.2# cd ..
bash-4.2# tar -zcvf pyodbcLambdaPackage.tar.gz /task

Deploy to lambda

copy from docker (in ec2) and scp to your laptop

[ec2-user@ip ~]$ sudo docker cp <container id>:/var/pyodbcLambdaPackage.tar.gz .

remove useless file and add add.py

zip file and upload zip to lambda and set handler

cd task
zip -r ../task .

finally, test the lambda

Conclusion

This is not an easy and good maintainable way, so my advice is don't use lambda. This article is reference youtube.com/watch?v=xWbU_OnkFOo and improves some content.