pyodbc + unixodbc + freetds = works in ubuntu, fails in centos

[Harel]

Hi all,

I'm having a peculiar problem which I've been banging my head against
for the past couple of days.
I'm connecting to a Microsoft Sql server database using pyodbc and the
freetds driver.
My development machine runs latest ubuntu. The production server runs
centos (though as guess os on top of ubuntu server).

On my development machine I can connect to the database without any
problems, using a DSN based connection.
On production machine, same configuration, I keep getting the
following error:

[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data
source (0) (SQLDriverConnectW)

when I try to connect.
I've tried a direct connection as well and get the same thing:

import pyodbc
cn="DRIVER={MSSQLfreeTDS};SERVER=<ip-
address>;DATABASE=<db>;UID=<username>;PWD=<password>"
cnx=pyodbc.connect(cn)

and same error:
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source (0) (SQLDriverConnectW)')

The weirdest thing is that i get the following from isql:

isql -v DNSNAME username-here
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection
failed
[01000][unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
[ISQL]ERROR: Could not SQLConnect


and from tsql i get

# tsql -H 192.168.100.10 -p 1433 -U username -P password
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Msg 18456, Level 14, State 1, Server RETAILSERVER, Line 1
Login failed for user '<username>'.
Msg 20014, Level 9, State 0, Server OpenClient, Line 0
Login incorrect.
There was a problem connecting to the server

However, if i do NOT supply the password via -P, and get prompted to
enter the SAME ONE i put on all instances, it works!
# tsql -H 192.168.100.10 -p 1433 -U username
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password: <here i type the same password>
1>

Any one has any ideas? Any input will be highly appreciated.

Harel

[tobias...@gmail.com]

Hi,

I had the same problem.

Your comments regarding tsql were very useful and gave me a hint for troubleshooting this. In the end what sorted it out for me was adding "PORT=1433;" to the connection string.

HTH,

Tobias

[wgw]

(For future reference)

It might be an encoding problem. See this discussion on USC4 (http://www.easysoft.com/developer/languages/python/pyodbc.html#ucs4).  If the two servers are not configured with the same encoding (python, unixODBC), you may have connection errors.

To give the flavor of the problem, from the Easysoft page:

The SQL Server ODBC driver distribution includes both a Unicode (libessqlsrv.so) and non-Unicode (libessqlsrv_a.so) version of the driver. The Unicode driver supports the "wide" version (with postfix "W") of the ODBC functions. (For example, SQLConnectW is the wide version of SQLConnect.) pyodbc 2.0.58+ will use the wide character version of the ODBC APIs if the driver supports them. When built against a UCS4 version of Python, the connection string passed by pyodbc to SQLDriverConnectW is not in the expected format, and the connection fails with the error: 

[a.j.ill...@gmail.com]

Thanks Tobias.

I had the same issue as you.

This worked perfectly on ubuntu:

import pyodbc

cxcn = pyodbc.connect('DRIVER={FreeTDS};SERVER=<Server name>;DATABASE=<DB NAME>;UID=<USER>;PWD=<PASS>')

cursor = cxcn.cursor()

cursor.execute("<SQL QUERY>")

print cursor.fetchall()

but wouldn't work on CentOS. I added. PORT= and now it works perfectly. So the python now looks like this:

import pyodbc

cxcn = pyodbc.connect('DRIVER={FreeTDS};SERVER=<Server name>;PORT=1433;DATABASE=<DB NAME>;UID=<USER>;PWD=<PASS>')

cursor = cxcn.cursor()

cursor.execute("<SQL QUERY>")

print cursor.fetchall()

So perhaps something change between freetds versions that now requires the PORT= part, or do I just have a definition on one of my servers that doesn't exist in the other (somewhere in the /etc/freetds.conf, /etc/odbc.ini, /etc/odbcinst.ini, etc)

Thanks

Alex

[ashaner]

Wow. I hadn't tried in a year or more but I gave up trying to use a direct connection to mssql using freetds because I always got the error and DSN connections worked. I just tried again and it worked using your example. Thanks!