Python Sql Server connection with AWS Lambda

The connection requires the SQL server drivers and unixobdc drivers to create a connection. However these must be compiled for the platform they will be deployed on and are not included in AWS Lambda instances by default.

#Fire up an EC2 instance and connect to it with ssh using your key file
ssh -i {path to your key file} ec2-user@{your server address}
Sudo yum install docker
sudo service docker start
sudo docker run -it --rm --entrypoint bash -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task lambci/lambda:build-python3.6

# Then, download ODBC source code, compile and take the output
cd unixODBC-2.3.5
./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/* .

# Install MSsql odbc driver
### IMPORTANT ####
# youtube doesnt allow angle brackets in descriptions
# replace % with right angle backet

ACCEPT_EULA=Y yum -y install msodbcsql
export CFLAGS="-I/var/task/include"
export LDFLAGS="-L/var/task/lib"

pip install pyodbc -t .

cp -r /opt/microsoft/msodbcsql .

### IMPORTANT ####
# youtube doesnt allow angle brackets in descriptions
# replace $ with left angle bracket
# replace % with right angle backet

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
UsageCount=1
EOF

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

# Test if it works

cd ..

#start new terminal
sudo docker ps -a

#Thanks to Carloches for the orginial version of these instructions

[chrisp]

I haven't even finished the video but I know I just struck gold! Thank you so much! I have no idea why there is such little information about how to do this online. Appreciate you 🙏🏻

[arunkumarn]

Hello @MOAM Industries,
While performing this step ACCEPT_EULA=Y yum -y install msodbcsql facing the following issue.


Could you please assist us in resolving it?


---> Package e2fsprogs-libs.x86_64 0:1.43.5-2.43.amzn1 will be installed
---> Package fuse-libs.x86_64 0:2.9.4-1.18.amzn1 will be installed
---> Package libss.x86_64 0:1.43.5-2.43.amzn1 will be installed
---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
--> Finished Dependency Resolution
Error: Package: msodbcsql-13.1.9.2-1.x86_64
Requires: unixODBC >= 2.3.1
Available: (amzn-main)
unixODBC = 2.2.14-14.7.amzn1
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

[Mat_]

Hi MOAM,

Many thanks for the post. I have faced a copule of issues following the instructions

The curl command fror the mssql-release.repo entry in the instructions is not correct and causes an error (the link is not complete).

Also come across an issue where unixODBC-2.3.1 is being looked for as a dependency on the yum install msodbcsql instruction.

Has something changed since you recorded these instructions?

[Shreyas]

Followed exactly as shown in video but struct at lambda layer
This is the error i get. I verified the connection within the container, everything works there. where is that i am missing, is there any extra step
{
"errorMessage": "Unable to import module 'main': /var/task/lib/libodbc.so.2: file too short",
"errorType": "Runtime.ImportModuleError"
}

[butterhalves]

I get below error on running : ACCEPT_EULA=Y yum -y install msodbcsql. Using amazon linux ami.

Error: Package: msodbcsql-13.1.9.2-1.x86_64 (packages-microsoft-com-prod
)
Requires: unixODBC >= 2.3.1
Available: (amzn-main)
unixODBC = 2.2.14-14.7.amzn1
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

[wywarren]

I got it to work following your tutorial! Are there extra steps involved to get it working in a lambda layer? I tried deploying it the standard way zipping it up in and it doesn't seem to work.

[funbox]

Thanks, this video is really helpful.I have installed all the package successfully.But the error I was getting while calling the .py file from lamda function.I have given the correct format filename.handler name, but I am getting the error handler is not present in the module.Any body faced this issue?

[Walruz]

I followed similar instructions and it appeared all worked but when I tried to use pyodb the module has no functions or properties