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"

Answer 1

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.

Answer 2

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.

Answer 3

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;'
)

Answer 4

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

Answer 5

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

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

Answer 6

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 []

Answer 7

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)

Answer 8

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'

Answer 9

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>')

Answer 10

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

Answer 11

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!

Answer 12

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.

Answer 13

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.