2

I am facing issue while trying to access ms-sql database using pyobdc.

Here is the System config: Python 2.7.11 Pyodbc 3.0.7 RedHat 5.4 (Tikanga) 32 Bit system Microsoft SQL Server 2012 (Database server) unixODBC 2.3.0

$ tsql -C output :

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 5.0
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no

$ odbcinst -j output :

unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini 
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

$ cat /usr/local/etc/odbcinst.ini output :

[ms-sql]
Description=TDS connection
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
FileUsage=1
UsageCount=1

$ cat /usr/local/etc/odbc.ini output :

[sqlserverdatasource]
Driver = ms-sql
Description = ODBC connection via ms-sql
Trace = No
Server = >IP Addresss To Database server<
Port = >Port Number<
Database = >Database name<

$ cat /etc/freetds.conf output :

[sql-server]
host        = >IP Addresss To Database server<
port        = >Port Number<
tds version = 8.0

Command which is giving me error:

connection = pyodbc.connect(r'DRIVER={FreeTDS};SERVER=>IP Addresss To Database server<; PORT=>Port Number<;DATABASE=Database name;UID=Database UID;PWD=DatabasePasswd;')

Error: 

Traceback (most recent call last):   File "<stdin>", line 1, in
<module> pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver
Manager]Data source name not found, and no default driver specified
(0) (SQLDriverConnect)')

I am trying to solve this problem for last 3 days. But no luck yet. So any help/suggestion would be very helpful. I have already gone through googling. Thanks in advance :)

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
uday bhanu
  • 65
  • 1
  • 9
  • Your DRIVER in Python doesn't match it in `odbcinst.ini` and you don't specify a TDS Version in the connection string. Solution included below. – FlipperPA Jun 20 '16 at 21:51
  • @FlipperPA: Thanks for your answer. And sorry for the typos at Driver. I modified content just to most it and forgot to modify the DRIVER=ms-sql. – uday bhanu Jun 21 '16 at 08:45

1 Answers1

1

Try something like this:

freetds.conf:

[sql-server]
    host = sql-server.host.com
    port = 1433
    tds version = 7.2

odbc.ini:

[sql-server]
    Driver = FreeTDS
    Server = sql-server.host.com
    Port = 1433
    TDS_Version = 7.2

odbcinst.ini:

[FreeTDS]
    Description = v0.91 with protocol v7.2
    Driver = /usr/local/lib/libtdsodbc.so

In Python:

connection = pyodbc.connect(r'DRIVER={FreeTDS};SERVER=sql-server.host.com;PORT=1433;DATABASE=Database name;UID=Database Username;PWD=DatabasePasswd;TDS_Version=7.2')

TDS Version 8.0 does not exist. 7.2 is the highest supported in FreeTDS 0.91. See here to explain the confusion: http://www.freetds.org/userguide/choosingtdsprotocol.htm

If you're still having issues, try testing with tsql and isql to test the FreeTDS and unixODBC layers of the connection stack respectively. Good luck!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • Thank you so much :) Because of your help i have saved another couple of days. Above answer is working with **TDS_Version=8.0**. – uday bhanu Jun 21 '16 at 08:55
  • Great! Can you mark the answer as correct with the checkbox? Also, if you check the link, you won't want to use 8.0. While it will work for now, it won't in future versions of FreeTDS. If you want to future proof, you'll want to use 7.2. :) – FlipperPA Jun 21 '16 at 09:08