Skip to content

Instantly share code, notes, and snippets.

@rduplain

rduplain/README.md

Created October 17, 2011 20:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rduplain/1293636 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rduplain/1293636 to your computer and use it in GitHub Desktop.
Download ZIP
Connect to MSSQL using FreeTDS / ODBC in Python.
Raw
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
Raw
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
Raw
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
@runt1me
Copy link

runt1me commented Mar 23, 2017

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.
@moagarw
Copy link

moagarw commented Nov 1, 2017

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)')
@apryiomka
Copy link

apryiomka commented Nov 13, 2017

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.
@koolquark
Copy link

koolquark commented Dec 20, 2017

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

Aechee commented Feb 13, 2018

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
@garciapar1015
Copy link

garciapar1015 commented Aug 10, 2018

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?
@weiyiyin0321
Copy link

weiyiyin0321 commented Sep 11, 2018

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?
@choonkiattay
Copy link

choonkiattay commented Nov 30, 2018

@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
@sathish-kumar-subramani
Copy link

sathish-kumar-subramani commented Jan 10, 2019

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
@chasezieman
Copy link

chasezieman commented Jun 29, 2019 •
edited
Loading

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)')
@rduplain
Copy link
Author

rduplain commented Jun 29, 2019

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

... as are various articles:

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.
@rduplain
Copy link
Author

rduplain commented Jun 29, 2019

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).
@chasezieman
Copy link

chasezieman commented Jun 30, 2019

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!
@alexandrerua
Copy link

alexandrerua commented Jan 23, 2020

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

mypantz0r commented Feb 16, 2020

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
@alexandrerua
Copy link

alexandrerua commented Feb 16, 2020

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

mypantz0r commented Feb 16, 2020

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

alexandrerua commented Feb 18, 2020

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

mypantz0r commented Feb 18, 2020

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

alexandrerua commented Feb 18, 2020

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 •
edited
Loading

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 •
edited
Loading

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

Slasktra commented Apr 22, 2020

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

sauravpratihar commented May 26, 2020

Thanks, man. you saved my day :)
@BSCdfdff
Copy link

BSCdfdff commented Dec 30, 2020 •
edited
Loading

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

thehappycheese commented Oct 18, 2021

Thank you op all and all commenters this was a huge help to me :)
@YuriTAngelico
Copy link

YuriTAngelico commented Oct 20, 2021

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

marcin2x4 commented Dec 3, 2021

I need such setup for AWS' Lambda. Could someone help on how to create zip package with rduplain's code?
@sahil-sawhney
Copy link

sahil-sawhney commented Jan 26, 2022

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

TiVenter commented Feb 14, 2022

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