5

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.

TomBombadil
  • 351
  • 1
  • 4
  • 15
  • That tutorial is for Ubuntu, double check where odbc.ini is. It could be /etc/unixODBC/odbc.ini. – LMC Feb 15 '18 at 17:50
  • Finding the odbc.ini was not the problem, i corrected the path. – TomBombadil Feb 15 '18 at 18:06
  • 1
    How about [this tutorial](http://www.bictor.com/2014/05/13/configure-unixodbc-for-ubuntu-14-04-using-freetds/), does /etc/freetds/freetds.conf exist on your case? – LMC Feb 15 '18 at 18:12
  • Is editing the freetds.conf really necessary when using python which overwrites all the info i would provide with this file? – TomBombadil Feb 15 '18 at 20:01
  • 1
    It could give you an independent test, once that is working you can go back to python. – LMC Feb 15 '18 at 20:11

2 Answers2

3

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')
Phonolog
  • 6,321
  • 3
  • 36
  • 64
  • 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. – TomBombadil Feb 22 '18 at 08:03
  • Hmm maybe take a look at this [question](https://stackoverflow.com/q/9723656/5730444) then. – Phonolog Feb 22 '18 at 08:32
  • 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. – TomBombadil Feb 22 '18 at 09:04
  • 1
    Awesome. Yeah the whole thing is quite tricky and sometimes even mysterious :D – Phonolog Feb 22 '18 at 11:49
  • 1
    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](https://github.com/protolab-rosenheim/python-freetds/blob/master/Dockerfile) on how to do this. – Phonolog Feb 22 '18 at 11:54
  • I think for my purpose the driver is working just fine, I am only calling a pretty simple stored procedure :D Anyway thank you! – TomBombadil Feb 22 '18 at 13:49
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

Eugene D. Gubenkov
  • 5,127
  • 6
  • 39
  • 71