What's causing 'unable to connect to data

问题:

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"

回答1:

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

回答2:

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.

回答3:

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

回答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.

回答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'

回答6:

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!

回答7:

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

回答8:

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

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

回答9:

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

回答10:

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)