Connecting Raspberry Pi 3 to MSSQL Server using pyodbc

Question

I am currently trying to get my raspberry pi 3 with Raspbian Stretch Lite (November 2017) connecting to an MSSQL Server. I was following this guide and replaced the Driver and the Setup fields with

Driver=/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup=/usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so

to match the paths on my pi, as someone proposed in the comment section. When im trying to connect via a python script with

conn = pyodbc.connect('DRIVER=FreeTDS;SERVER<IP_OR_HOSTNAME>;PORT=1433;DATABASE<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;')

where <> is filled with the correct strings, my script gets stuck on this line without printing anything until i do a keyboard interrupt.

I was also trying to get the official MS Drivers to work, using the Debian 9 Versions, but I can't manage to install the packages since msodbcsql still cant be located after the curl commands and apt-get update.

Am I missing something to get FreeTDS working or does the script getting stuck mean the pi cant connect to the server? Is there any other possibility to get the pi connected to MSSQL?

Thank you in advance.

Answer 1

I'm using following dockerfile to connect my Raspberry Pi 3 to a remote SQL Express database. It should document all steps needed. My Pi is running HypriotOS which is based on Raspian.

FROM arm32v7/python:3

RUN apt-get update

#1. Install dependencies for PyODBC and tds
RUN apt-get install -y tdsodbc unixodbc-dev
RUN apt install unixodbc-bin -y
RUN apt-get clean -y

#2. Edit /etc/odbcinst.ini
RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/arm-linux-gnueabi/odbc/libtdsodbc.so\n\
Setup = /usr/lib/arm-linux-gnueabi/odbc/libtdsS.so" >> /etc/odbcinst.ini

#3. Install requirements (contains pyodbc)
COPY ./requirements.txt /usr/src/app/requirements.txt
RUN pip install --no-cache-dir -r requirements.txt

#Copy and run my app
COPY . .
CMD [ "python", "app.py"]

So it's basically three steps:

1. Install dependencies for PyODBC and tds
2. Edit /etc/odbcinst.ini
3. Install PyODBC, eg.: pip install pyodbc

In my code I'm able to connect to the db like this:

connection = pyodbc.connect(driver='{FreeTDS}',
                            server='111.66.111.66\SQLEXPRESS',
                            uid='sa', pwd='notmyactualpw')