Error connecting to SQL Server from Ubuntu+PYODBC

Question

I found many similar question but none of the solutions worked for me. It seems to be a pretty simple matter but still I can't find a solution.

I'm using a headless ubuntu server to connect to a SQL Server on a windows Server 2008 using a python script that does it using pyodbc. The script runs perfectly on my local windows machines, but when I try it on ubuntu server I get the error:

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

The connection string I'm using is:

connectionString='DRIVER={dbserverdsn};SERVER=10.23.11.10;DATABASE=Market;UID=usr;PWD=pwd;TDS_VERSION=7.2;PORT=1433'

Any thoughts ? thanks in advance

EDIT : Adding the main files

odbc.ini

[dbserverdsn]
 Driver = FreeTDS
 Server = 10.23.11.10
 Port = 1433
 Database=Markets
 TDS_Version = 7.2

odbcinst.ini

[FreeTDS]
 Description = v0.91 with protocol v7.2
 Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

freetds.conf

[global]
 # TDS protocol version, use:
 # 7.3 for SQL Server 2008 or greater (tested through 2014)
 # 7.2 for SQL Server 2005
 # 7.1 for SQL Server 2000
 # 7.0 for SQL Server 7
 tds version = 7.2
 port = 1433
 text size = 64512
# A typical Microsoft server
[dbserverdsn]
 host = 10.23.11.10
 port = 1433
 tds version = 7.2

Answer 1

Microsoft comes pre-installed with a driver to speak to SQL Server, Ubuntu does not. FreeTDS provides the best driver I've used (the one provided by MS has given me problems). Here's how... on Ubuntu, install your pre-requisites:

# Install pre-requesite packages
sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

You need to configure /etc/freetds/freetds.conf to bridge unixODBC to SQL Server next:

[global]
 # TDS protocol version, use:
 # 7.3 for SQL Server 2008 or greater (tested through 2014)
 # 7.2 for SQL Server 2005
 # 7.1 for SQL Server 2000
 # 7.0 for SQL Server 7
 tds version = 7.2
 port = 1433
 text size = 64512
# A typical Microsoft server
[dbserverdsn]
 host = dbserver.domain.com
 port = 1433
 tds version = 7.2

Here's where you plug unixODBC into FreeTDS in /etc/odbcinst.ini and give the path to the FreeTDS driver:

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Last but not least, edit /etc/odbc.ini so Python / unixODBC can see the entire stack and FreeTDS data source:

[dbserverdsn]
Driver = FreeTDS
Server = dbserver.domain.com
Port = 1433
TDS_Version = 7.2

You can test from problems on Ubuntu's command line with the 'tsql' and 'isql' command line utilities and post any errors you receive.

Then, for your connection string:

connectionString='DRIVER={FreeTDS};SERVER=10.23.11.10;PORT=1433;DATABASE=Market;UID=*;PWD=*;TDS_VERSION=7.2'

Good luck!

• Pedro Braz: Thank you so much for your response ! very accurate. But, unfortunately, i could get tsql to work but still not pyodbc. getting the error [unixODBC][Driver Manager]Data source name not found, and no default driver specified. Any thoughts on that ? Thank you so much again.
• FlipperPA: Did you run a test with isql? tsql tests whether FreeTDS can talk to SQL Server. isql tests whether unixODBC can talk through FreeTDS to SQL Server, so more of the stack. If isql doesn't connect (and from the error, it looks like that is where the issue is), then you'll know there's a problem in the configuration between odbc.ini / odbcinst.ini and FreeTDS. I also made a mistake in the connectionString; I forgot to include PORT=1433; which I've amended in my answer. Try that as well?
• Pedro Braz: Thank you a lot again. using isql is a good hint. But still I wasn't able to make it work. I found a post with a similar problem but the answer doesn't help me. The HOST and SERVER in my case are local, so I tried it with IP and the server name and both didn't work. the error I get in isql is the same [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect any ideas ? thank you so much again. askubuntu.com/questions/167491/…
• FlipperPA: A few things to try: first, let's see what DSNs you have installed: odbcinst -q -s However, those DSNs are just for testing, since we're connecting directly with Python to the server. In your connection string, this: DRIVER={dbserverdsn} should still be: DRIVER={FreeTDS}
• FlipperPA: For testing isql, are you doing this: isql dbserverdsn username password -v? Of course, substitute your username and password.