MSSQL Server connection using pyodbc 3.0.7 on Ubuntu 12.04

1. Install pyodbc from your Virtualenv pip

python_2.7.5_venv/bin/pip install pyodbc

2. Install mssql odbc drivers and other dependencies

sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

3. open /etc/freetds/freetds.conf and add the following at the end:

# mssql server
[sqlserver]
    host = <host-adddress>
    port = <port-number>
    tds version = 7.0

4. Test server connection using the following command

tsql -S sqlserver -U <user-name> -P <password>

5. Check configuration files using –

odbcinst -j

6. Configure /etc/odbcinst.ini and insert the following:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

7. Open /etc/odbc.ini and insert the following:

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = <database-name>
TDS_Version = 8.0

8. Check connection using the following command:

isql -v sqlserverdtasource <username> <password>

9. Use the following code to check pyodbc :

import pyodbc
 
dsn = 'sqlserverdatasource'
user = '<username>'
password = '<password>'
database = '<database-name>'
 
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)