Connecting to Microsoft SQL Server through pyODBC on Ubuntu

Question

Am having an issue connecting to a Microsoft SQL Server instance from pyODBC within an Ubuntu (12.10) machine.

The error I am getting back is:

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data Source name not found, and no default driver specified (0) (SQLDriverConnect)')

The connection string am using for pyodbc is:

self.corpus_cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=UKEDN-06880;DATABASE=db1;UID=user;PWD=pass')

This seems to work fine from pyODBC within Windows (just need to change the DRIVER to 'SQL Server' instead of 'FreeTDS'), and it work fine when I try to connect from the Ubuntu machine using the tsql Tool from the terminal, with the following command:

tsql -S UKEDN-06880 -p 1433 -U user -P pass

I can select any table with no issues, it just doesn't seem to work from within pyODBC.

Any help or advice will be much appreciated, my Linux skills are uber weak and am totally stuck, although since it works from tsql I get the feeling that am very close!

Answer 1

It looks like you have gotten freeTDS to work correctly since you can use tsql. Have you tried to connect with isql?

Look at this howto for a detailed walk through. The part I think you need is in setting up unixodbc a little ways down the page.

• John Powell: Thank you. That link saved many hours. I now have a sql server connection in Python from a console on a linux box, which somehow feels wrong :D
• friartuck: do you have to use a DSN?

Answer 2

first stape $ sudo apt-get install libmdbodbc1

edit the file /etc/odbcinst.ini like this

[Microsoft Access Driver (*.mdb)]
Description = Microsoft Access Driver (*.mdb)
Driver = /path/to/file/libmdbodbc.so
Setup = /path/to/file/libtdsS.so
CPTimeout = 
CPReuse = 

And the file /etc/odbc.ini

[Microsoft Access Driver (*.mdb)]
Description = SQL Server
Driver = Microsoft Access Driver (*.mdb)
Trace = No
TraceFile = /tmp/mssodbc.log