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.

Improve this question
5
  • That tutorial is for Ubuntu, double check where odbc.ini is. It could be /etc/unixODBC/odbc.ini. Commented Feb 15, 2018 at 17:50
  • Finding the odbc.ini was not the problem, i corrected the path. Commented Feb 15, 2018 at 18:06
  • 1
    How about this tutorial, does /etc/freetds/freetds.conf exist on your case? Commented Feb 15, 2018 at 18:12
  • Is editing the freetds.conf really necessary when using python which overwrites all the info i would provide with this file? Commented Feb 15, 2018 at 20:01
  • 1
    It could give you an independent test, once that is working you can go back to python. Commented Feb 15, 2018 at 20:11

2 Answers 2

Reset to default
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')
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
Hmm maybe take a look at this question then.
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.
Awesome. Yeah the whole thing is quite tricky and sometimes even mysterious :D
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.
|
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.