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)

One thought on “MSSQL Server connection using pyodbc 3.0.7 on Ubuntu 12.04

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s