Unable to connect to MSSQL from Python

Question

my Python code:

import pyodbc
cnxn = pyodbc.connect('DRIVER=FreeTDS;DSN=S29;UID=test;PWD=test;TDS_Version=8.0;ClientCharset=UTF8')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
  print row.user_id, row.user_name

Get error:

Traceback (most recent call last):
File ".../test_mssql_connect.py", line 4, in <module>
cnxn = pyodbc.connect('DRIVER=FreeTDS;DSN=S29;UID=test;PWD=test;TDS_Version=8.0;ClientCharset=UTF8')
pyodbc.Error: ('HY000', '[]  (20013) (SQLDriverConnect)')

I can not find the information, what is this error ('HY000', '[] (20013) (SQLDriverConnect)') and how to fix it.

At the same time through tsql, osql and isql I successfully connected to the base

my odbc.ini

[S29]
Driver=FreeTDS
Description=S29
ServerName=192.168.0.29
Database=test
UID=test
PWD=test
TDS_Version=8.0

my odbcinst.ini

[FreeTDS]
Description=FreeTDS
Driver=/usr/local/Cellar/freetds/0.95.1/lib/libtdsodbc.so
Setup=/usr/local/Cellar/freetds/0.95.1/lib/libtdsodbc.so
UsageCount=2
CPTimeout =
CPReuse =
TDS Version = 8.0
client charset = utf-8

my freetds.conf

[global]
tds version = 8.0
[192.168.0.29]
host = 192.168.0.29
port = 1433
tds version = 8.0
[S29]
host = 192.168.0.29
port = 1433
tds version = 8.0

Answer 1

What is your freetds.conf set up? Note, you'll most likely want to use TDS Version 7.3, though I don't think that's the problem, though it may be, since it defaults to TDS Version 7.1. Documentation here for TDS Version numbers supported by FreeTDS:

http://www.freetds.org/userguide/choosingtdsprotocol.htm

You may have to provide the port number, but without seeing your freetds.conf DSN set up, it is hard to tell. I've had better luck without DSNs from Python. Since 8.0 isn't a valid TDS version, I'll provide you with a full example that works for me:

freetds.conf:

[examplesql]
        host = examplesql.server.com
        port = 1433
        tds version = 7.3

odbc.ini:

[examplesql]
Driver = FreeTDS
Server = examplesql.server.com
Port = 1433
TDS_Version = 7.3

odbcinst.ini:

[FreeTDS]
Description = FreeTDS with Protocol up to 7.3
Driver = /usr/lib64/libtdsodbc.so.0

pyodbc connect, DSN free:

DRIVER={FreeTDS};SERVER=examplesql.server.com;PORT=1433;DATABASE=dbname;UID=dbuser;PWD=dbpassword;TDS_Version=7.3;

You'll have to modify the odbcinst.ini to point to where your driver lives. A few notes:

• You'll have to update the TDS version to match the version of SQL Server you are running and the Free TDS version you are running. Version 0.95 supports TDS Version 7.3.
• TDS Version 7.3 will work with MS SQL Server 2008 and above.
• Use TDS Version 7.2 for MS SQL Server 2005.

See here for more:

https://msdn.microsoft.com/en-us/library/dd339982.aspx

Good luck.

Answer 2

I reinstall pyodbc, now works. I download it here https://github.com/juztin/pyodbc

 python setup.py build install --macports