Ubuntu – Connecting to MS SQL Server from Ubuntu Server

freetdssql-server-2008Ubuntuunixodbc

Using Ubuntu 10.04 LTS, I'm trying to connect to an MS SQL Server we have on another box from within a Python script. What are the steps I need to follow to do so? Right now, I'm trying to use the pyodbc module, but whenever I try to connect I get the following error:

Data source name not found, and no default driver specified (0)(SQLDriverConnectW)

So I looked at the odbvcinst.ini file which was empty, added an entry, and now when I use one of the items I've defined there I get the following error:

Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0)(SQLDriverConnectW)

I've installed: unixodb unixodbc-dev freetds-dev freetds-bin. I've put this on SF and not SO because I'm thinking this is a server configuration issue. Which driver should I have and how do I reference it in which file, etc? (I'm pretty new to Ubuntu)

Best Answer

Have you set up the data source in your /etc/freetds.conf file?

The entry should look something like:

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

(Though I tend to use tds version = 8.0 when I'm working with MS SQL Server, and it works fine)

Edit: (Going to put my responses here so I can code-format them)

Your /etc/odbcinst.ini should look like:

[MSSQL]
Description = FreeTDS
Driver = /usr/lib/libtdsodbc.so 
Setup = /usr/lib/libtdsS.so
CPTimeout =
CPReuse =

(Make sure the driver paths are right. Sometimes it's /usr/lib/odbc/)

Then the python string should look like:

import pyodbc
connection = pyodbc.connect("DRIVER={MSSQL};SERVER=yourserver.yourdomain.com;UID=username;PWD=password;DATABASE=databasename")
Related Topic