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.

Solution 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')

Solution 2

Another option for you to connect from ARM Linux boards to MS SQL Server is to use pytds -- worked like a charm for me with my ARM-based ASUS Tinker Board running on Debian 9.

pytds does not have a lot of dependencies comparing with MS ODBC driver, or "Free TDS", you just install pip package(s) and you are good to go. As far as I understand that's because TDS protocol implementation is written in Python itself.

Here are some details: https://github.com/denisenkom/pytds

pip install python-tds

Comments

[TomBombadil]

Thank you for your answer. I followed your commands but now im getting the error 'Unable to connect to datasource'. I checked the login info but it is correct. I guess the driver is working now.

[Phonolog]

Hmm maybe take a look at this question then.

[TomBombadil]

I found a workaround using the DSN I specified in odbc.ini. Still don't know what the error is because I passed the same information directly via my pyodbc.connect command. Luckily pyodbc.connect supports the usage of DSN.

[Phonolog]

Awesome. Yeah the whole thing is quite tricky and sometimes even mysterious :D

[Phonolog]

I also found that the driver version from apt is quite instable and started building a newer version from the source. If you face similar issues you can take a look at this dockerfile on how to do this.

[TomBombadil]

I think for my purpose the driver is working just fine, I am only calling a pretty simple stored procedure :D Anyway thank you!