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

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........!!!!