Connect to MSSQL using FreeTDS / ODBC in Python.

README.md

Goal: Connect to MSSQL using FreeTDS / ODBC in Python.

Host: Ubuntu 11.10 x86_64

Install:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip install pyodbc sqlalchemy

In /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so

hello_pyodbc.py

"Proof connection at pyodbc level."
# Test pyodbc connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import pyodbc


conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
cursor = conn.cursor()
for row in cursor.execute('select 6 * 7 as [Result];'):
    print row.Result

hello_sqlalchemy.py

"Proof connection at SQLAlchemy level, on top of pyodbc."
# Test SQLAlchemy connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import urllib

from sqlalchemy import create_engine


engine = create_engine('mssql+pyodbc:///?odbc_connect=' +
    urllib.quote_plus('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
)
for row in engine.execute('select 6 * 7 as [Result];'):
    print row.Result

on the pi3, has anyone had to edit the odbc.ini file for this to work as well? getting an error that seems to suggest this is the case, but not sure how to edit that file and the dpkg-reconfigure doesn't do it for me.

My sql server is 2014 and using TDS version as 7.4 does not work .. but it works if i use 7.2 .. any reason why it will not ? i get this error -
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I am having this error: [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect) any advise? I tried different TDS_Version values, nothing seems to work.

@apryiomka I had same issue, just don't supply any TDS_Version argument

This path worked for me on Ubuntu 17.10
[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

This error comes up for me : Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found (0) (SQLDriverConnect)")

Any advice on how to solve this?

I am also having Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found (0) (SQLDriverConnect)")

@garciapar1015 did you ever figure out answer to your isssue?

@garciapar1015 @weiyiyin0321
You may try to check if /etc/odbcinst.ini or /usr/local/etc/odbcinst.ini exists and contain FreeTDS information
it should look like this

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

Using docker image python:3. I guess it is Debian.
The following worked for me.

RUN odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini

Any help on the connection string here? Was able to successfully build and run container with the following dockerfile.

FROM python:3.6 RUN apt-get update COPY requirements.txt requirements.txt RUN pip install --no-cache -r requirements.txt EXPOSE 8888 RUN apt-get update && apt-get install -y tdsodbc unixodbc-dev \ && apt install unixodbc-bin -y \ && apt-get clean -y RUN odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini RUN pip install pyodbc CMD ["jupyter","notebook","--no-browser","--ip=0.0.0.0","--allow-root","--NotebookApp.token='mynotebook'"]

Python Connection String:
import pyodbc import pandas as pd cnxn = pyodbc.connect('DSN=unixodbc;Server=<server>;DATABASE=<database>;UID=<username>;PWD=<password>

ERROR:
InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Since your error message is coming from unixODBC, the upstream docs are meaningful:

• http://www.unixodbc.org/doc/FreeTDS.html#Configuration

... as are various articles:

• https://support.microfocus.com/kb/doc.php?id=7021177

The upstream docs and most articles I'm finding are preferring connection details in the .ini over the connection string. Regardless, the keyword/value pairs are meaningful, and you might prefer to put the detail in the .ini over the connection string.

Just in case: be sure to replace parameters marked <PARAMETER> with their actual values from the original example. For example, use DATABASE=mydatabase (replacing "mydatabase" with the actual name) and not DATABASE=<DATABASE_NAME>.

Also, hello everyone from the past 8 years! Looks like GitHub recently added notifications for gists.

Thinking back to the original gist, my intent was to have the .ini provide the underlying installation and put all connection details in the Python connection string. That way, the application owns/controls the database connection configuration (while the .ini is more or less a driver detail).

I was able to fix it. I will edit and post once I am back on computer.

The issues were:
(1) use of “DNS” rather than “Driver” as well as it should have said “FreeTDS”. I was able to iterate through the drivers installed on the container from Python to locate the correct one. I image this name is set somewhere in the ini file.
(2) Linux not liking the special characters in the temporary password I had setup to hit the SQL server

Greatly appreciate the help. The docs above are also very helpful in general.

Thanks!

I'm a arch linux python pyodbc user,

a few days ago my pydobc connection stop working
pyodbc.connect('DRIVER=FreeTDS;SERVER=192.168.0.108;PORT=1433;DATABASE=ina;UID=sa;PWD=...')
with message
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I keep my arch updated almost daily, and run pipenv update
using python 3.8.1
pyodbc 4.0.28

I have open connection to the server I can "telnet 192.168.0.108 1433" and get response fom mssql.

what is happening?
what changed?

I'm a arch linux python pyodbc user,

a few days ago my pydobc connection stop working
pyodbc.connect('DRIVER=FreeTDS;SERVER=192.168.0.108;PORT=1433;DATABASE=ina;UID=sa;PWD=...')
with message
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I keep my arch updated almost daily, and run pipenv update
using python 3.8.1
pyodbc 4.0.28

I have open connection to the server I can "telnet 192.168.0.108 1433" and get response fom mssql.

what is happening?
what changed?

Did you find what went wrong here? I have the same issue with unixodbc .. was working now magically it's not

I found something, but not able to get it right.

I get to use tsql command line to connect to mssql and get it to log everything,
I can see it gets a first response
in the login encrypted step sqlserver somehow kills the connection and the error pops up.
ii read along and it may have to do with ssl mismatch
not able to get it right,

I found something, but not able to get it right.

I get to use tsql command line to connect to mssql and get it to log everything,
I can see it gets a first response
in the login encrypted step sqlserver somehow kills the connection and the error pops up.
ii read along and it may have to do with ssl mismatch
not able to get it right,

I got it working using the official MSFT drivers on debian 10, here's my docker file portion that sets it up:

RUN apt-get update \
    && apt-get install -y curl apt-transport-https gnupg2 \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools \
    && apt-get install unixodbc-dev -y

RUN sed -i 's/TLSv1.2/TLSv1.0/g' /etc/ssl/openssl.cnf
RUN sed -i 's/SECLEVEL=2/SECLEVEL=1/g' /etc/ssl/openssl.cnf

The big thing here is resetting the TLS version from 1.2 to 1.0

Hi, tanks a lot for your post, makes sense to me as I told earlier.
but my
/etc/ssl/openssl.cnf
does not have entries or values TLSv1.2or SECLEVEL=2
can you place here the part or parts ot the .cnf file including group header where these values should reside.

Thanks

Hi, tanks a lot for your post, makes sense to me as I told earlier.
but my
/etc/ssl/openssl.cnf
does not have entries or values TLSv1.2or SECLEVEL=2
can you place here the part or parts ot the .cnf file including group header where these values should reside.

Thanks

What OS are you using? Mine is Debian 10. That will determine where this config lives.

Hi, tanks a lot for your post, makes sense to me as I told earlier.
but my
/etc/ssl/openssl.cnf
does not have entries or values TLSv1.2or SECLEVEL=2
can you place here the part or parts ot the .cnf file including group header where these values should reside.
Thanks

What OS are you using? Mine is Debian 10. That will determine where this config lives.

Arch linux, and I do have /etc/ssl/openssl.cnf

what i don't have is the text portions your are replacing in your sed commands.

Ok - I would look into downgrading the openssl package to 1.0.x (what ever is highest) as a first step. Looks like Arch vs Debian has different openssl packages\configs openssl version should return something like OpenSSL 1.1.1d 10 Sep 2019

You can now use pymssql library for python 3.4+ . Just pip install pymssql
https://pymssql.readthedocs.io/en/latest/intro.html

I have an issue with Python3, pyodbc and FreeTDS sending Latin_1 to MSSQL. Something that has been working perfectly with Python2. Maybe some of you have faced the same problem. Instead of posting my issue twice I post a link to the other post. mkleehammer/pyodbc#749
Thanks for your attention.

Thanks, man. you saved my day :)

To confirm what was said above:

Using:

1. Container: Docker image: https://hub.docker.com/r/ifnazar/sybase_15_7
2. My Os: Debian Buster

conda install -c conda-forge pyodbc
conda install -c conda-forge pymssql
sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
sudo dpkg-reconfigure tdsodbc

This worked for me! (access Sybase database using python 3.8.6)

Thank you op all and all commenters this was a huge help to me :)

Hello, this helped me a lot and worked! But for another SQL Server I got this error:

('08S01', '[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

What can i do?

This is my conection string from the program:

cnxn = pyodbc.connect(f'DRIVER=FreeTDS;SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password};TDS_Version=8.0;')
cursor = cnxn.cursor()

For one server is perfect but fot the other one no. Can this be some missing configuration in the MSSQL?

Thanks!

I need such setup for AWS' Lambda. Could someone help on how to create zip package with rduplain's code?

@YuriTAngelico I am facing same problem as you, any solutions you came across?

Good day.

I'm trying to connect my raspberry pi 3 to my Azure Sql db.
I came across FreeTDS and it looks promising. I tried the steps above with no luck.

I'm running Raspberry Pi Os(32 - bit) with Debian 11.

Error im currently at is: conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=cmpg-sql-db.database.windows.net;PORT=1433;DATABASE=CMPG_323;UID=Beertjie007;PWD=Beertjie@@7;TDS_Version=8.0;')
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I'm also trying to change the .ini file as prescribed with no luck maybe that is my problem but i cant due to access denied.

Is there any ways to approach this differently or use another way to connect from Raspberry pi to azure.

Sorry I'm new to this and could use all the help and detail.

Please help........!!!!