Skip to content

Instantly share code, notes, and snippets.

@rduplain
Created October 17, 2011 20:04
Show Gist options
  • Save rduplain/1293636 to your computer and use it in GitHub Desktop.
Save rduplain/1293636 to your computer and use it in GitHub Desktop.
Connect to MSSQL using FreeTDS / ODBC in Python.

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
"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
"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
@mypantz0r
Copy link

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

@alexandrerua
Copy link

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

@mypantz0r
Copy link

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.

@alexandrerua
Copy link

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.

@mypantz0r
Copy link

mypantz0r commented Feb 18, 2020

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

@vnktsh
Copy link

vnktsh commented Feb 21, 2020

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

@Slasktra
Copy link

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.

@sauravpratihar
Copy link

Thanks, man. you saved my day :)

@BSCdfdff
Copy link

BSCdfdff commented Dec 30, 2020

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)

@thehappycheese
Copy link

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

@YuriTAngelico
Copy link

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!

@marcin2x4
Copy link

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

@sahil-sawhney
Copy link

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

@TiVenter
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment