What's causing 'unable to connect to data source' for pyodbc?

Question

I'm trying to connect to an MSSQL database from python on Linux (SLES).

I have installed pyodbc and Free TDS. From the command line:

tsql -H server -p 1433 -U username -P password

Connects to the server without a problem, however, from Python:

import pyodbc
pyodbc.connect(driver='{FreeTDS}', server='server', database='database', uid='username', pwd='password')

Yields an error:

pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I'm finding this error unhelpfully vague. Even a suggestion to narrow down the issue would be helpful right now.

Edit: Looking at the TDS log dump it looks like this is where the whole thing falls apart:

token.c:328:tds_process_login_tokens()
util.c:331:tdserror(0x87bbeb8, 0x8861820, 20017, 115)
odbc.c:2270:msgno 20017 20003
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
util.c:156:Changed query state from IDLE to DEAD
token.c:337:looking for login token, got  0()
token.c:122:tds_process_default_tokens() marker is 0()
token.c:125:leaving tds_process_default_tokens() connection dead
login.c:466:login packet accepted
util.c:331:tdserror(0x87bbeb8, 0x8861820, 20002, 0)
odbc.c:2270:msgno 20002 20003
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
mem.c:615:tds_free_all_results()
error.c:412:odbc_errs_add: "Unable to connect to data source"

score 22 · Answer 1 · answered Aug 08 '12 at 10:56

I try with:

MS SQL 2008 Datacenter
Ubuntu 12.04 TLS (amd64)
Python 2.7

And this works for me:

Test connection:

tsql -H 10.19.4.42 -p 1433 -U DAVIDG -P 123456

on /etc/odbcinst.ini add:

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[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
UsageCount = 1

on /etc/odbc.ini add:

[SQLDemo]
Description=my dsn
Driver=FreeTDS
Database=teste3
Servername=SQLDemo

on /etc/freetds/freetds.conf add:

[SQLDemo]
        host = 10.19.4.42
        port = 1433
        tds version = 8.0

test with test.py:

#!/usr/bin/python

import pyodbc
cnx = pyodbc.connect("DSN=SQLDemo;UID=DAVIDG;PWD=123456")

cursor = cnx.cursor()
cursor.execute("select * from Company;")
for row in cursor:
  print row.Name

One of the very useful command for knowing server details is **tsql -LH hostname**. — FUD, Feb 02 '13 at 09:31
this configuration will work to connect to sql server 2000, thank you very much. — Mr. Pancho, Jun 29 '22 at 19:41

score 19 · Answer 2 · answered Sep 11 '12 at 13:22

I had the same problem and I found out that it was missing the TDS_Version parameter in the call to connect(). The following code works for me to connect to an instance of MS SQL Server 2008:

import pyodbc

driver = '/opt/local/lib/libtdsodbc.so' # Change this to where FreeTDS installed the driver libaray!

conn = pyodbc.connect(
    driver = driver,
    TDS_Version = '7.2', # Use for
    server = '<hostname or ip address>',
    port = 1433,
    database = '<database>',
    uid = '<uid>',
    pwd = '<pwd>')

Why isn't this connection keyword syntax mentioned in the wiki?! Ages of debugging a Linux environment with UnixODBC and FreeTDS and this is the thing that works. — Felix, Aug 15 '18 at 09:09

score 13 · Accepted Answer · answered Mar 18 '12 at 00:06

13

After hours of going in circles it turns out all I was missing was

TDS_Version = 8.0 in the DSN in my odbc.ini file.

I had specified it elsewhere, but it needed to be here, too, apparently.

Hope this helps some other poor soul.

answered Mar 18 '12 at 00:06

pbaehr

622
1
5
11

score 5 · Answer 4 · answered Aug 22 '15 at 00:44

5

Adding TDS_Version to the connection string worked for me:

connection_string = 'DRIVER={{FreeTDS}};SERVER={server};PORT=1433;DATABASE={database};UID={uid};PWD={pwd};TDS_VERSION=8.0'

answered Aug 22 '15 at 00:44

cadizm

896
7
4

score 4 · Answer 5 · answered Mar 16 '12 at 21:46

4

Just for an extra datapoint, odbc.ini is empty on my host, and odbcinst.ini has the following lines:

# Driver from FreeTDS
#
[FreeTDS]
Driver = /usr/lib64/libtdsodbc.so.0

last, the freetds.conf file has these lines:

[global]
    host= <hostname>
    port= <mssql port>
    tds version = 8.0

While one can certainly specify option settings in odbc.ini, doing it this way allows the configuration options to all be managed where you'd expect them -- the freetds.conf file.

answered Mar 16 '12 at 21:46

Keith Schoenefeld

128
5

1

Interesting. I did have the tds version set to 8.0 in my freetds.conf, both under global and each individual DSN. For some reason it only worked after it was added in the other location, as well. – pbaehr Mar 18 '12 at 00:05
Wow, I just went through the exact same issue. It didn't work in Python until I manually added tds version = 8.0 to the freetds.conf file. Even though the comments in that file say it's irrelevant and it's commented out by default :! – Darren Ringer Mar 02 '15 at 14:33

score 3 · Answer 6 · answered Dec 08 '16 at 19:58

3

My problem was that on my settings file I was setting HOST to the SQL Server IP, however after hours of pulling my hair off I figured out that HOST has to be set to the Data Source Name []

answered Dec 08 '16 at 19:58

Andres

41
1

score 2 · Answer 7 · answered May 18 '12 at 11:50

I was also having problems with this after upgrading my version of ubuntu to 12.04. My old freetds config /etc/freetds/freetds.conf was no being found so I had to move it to /usr/local/etc at which point it started working again.

Also my driver location is /usr/local/lib/libtdsodbc.so

Hope this helps save someone a day and a half!

score 2 · Answer 8 · answered Apr 19 '19 at 08:33

One setting is enoug, /etc/odbcinst.ini:

[FreeTDS]
Description = FreeTDS Driver to MsSQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
UsageCount = 1

And next:

connection = pyodbc.connect(
    'DRIVER=FreeTDS;'
    'SERVER=<host_name_or_ip>;'
    'PORT=1433;'
    'DATABASE=<database>;'
    'UID=<username>;'
    'PWD=<password>;'
    'TDS_VERSION=8.0;'
)

score 1 · Answer 9 · answered Jan 01 '18 at 13:18

1

You can also set an environmental variable in your python script:

os.environ['TDSVER'] = '8.0'

answered Jan 01 '18 at 13:18

Pathead

717
10
24

score 1 · Answer 10 · answered Mar 18 '20 at 14:38

1

In my case my host files are missing when i ping to that server it was not pinging. Then i noticed my host files are missing by applying sudo vi /etc/hosts command in the terminal. I added my host and ip address and worked fine for me.

answered Mar 18 '20 at 14:38

saichand

11
1

score 0 · Answer 11 · 2018-03-13T18:42:05.957

This worked for me, not sure but thought that it might help someone

run below command to find which version of odbcinst and isql you are using

 which odbcinst

 which isql

Then run $ odbcinst -j to find which odbc.ini and odbcinst.ini is getting used.

In odbcinst.ini add

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

And in odbc.ini configure your server as

[YOUR_SERVER]
Driver = FreeTDS
Servername = <YOUR_MACHINE_NAME>
Database = <Database_You_Want_To_Connect>

I found some good description at https://docs.snowflake.net/manuals/user-guide/odbc-linux.html#unixodbc

Also take a look at https://github.com/lionheart/django-pyodbc/wiki/Mac-setup-to-connect-to-a-MS-SQL-Server

score 0 · Answer 12 · answered Aug 24 '20 at 14:12

I found my way here after an Ubuntu 18.04 upgrade broke my pyodbc connections. Turns out, in my //etc/odbcinst.ini file my driver description order was switched around.

So when I called:

from pyodbc import connect,drivers
conn = connect(driver=drivers()[0], ...

I should have been calling:

conn = connect(driver=drivers()[1], ...

In other words, I was calling the wrong driver because of a simple index issue. Hope this helps someone else.

score -1 · Answer 13 · answered May 11 '16 at 09:16

-1

The follow worked for me:

Modify python2.7/site-packages/sql_server/pyodbc/base.py

def get_new_connection(self, conn_params):
...
-    cstr_parts['SERVERNAME'] = host
+    cstr_parts['SERVER'] = host
+    cstr_parts['PORT'] = str(port)

answered May 11 '16 at 09:16

Cody

4,353
4
39
42

What's causing 'unable to connect to data source' for pyodbc?

13 Answers13

Linked