Configuration for Connecting to MSSQL Server 2008 on Virtualbox GuestOS from Ubuntu 12.04 HostOS using PyODBC 3.0.8

1. VirtualBox Configuration

– Adding a host-only network adapter
If you don’t have a host-only adapter setup then goto topmost menu:
File->Preferences->Network->Host-only network->Add ‘vboxnet0’ by clicking on the ‘+’ sign at
the right-hand corner. Go to edit and take note of the IP address assigned (lets assume its, so that is the virtual network adapter that has been created at host OS)
Now go to:
Settings->Network->Adapter 2 Tab (considering you’ve NAT set on Adapter 1 for internet) ->Attach to host-only adapter> Name select ‘vboxnet0’
[Note, if you already have a network adapter with NAT selected for accessing internet from
guest os, leave it and just add another one.]
-Add GuestOS to the network
Go to Stat->Settings->Network Connections->Select ‘Local Area Connection 2’ (‘Local Area Connection 1’ should be allocated to NAT Adapter 1 , which is used for Internet)
Right click to get properties ->Internet Protocol (TCP/IP)->Use the following IP address->Assign an IP in the same network as host-only adapter (In this example, that can be

2. MSSQL Server 2008 R2 Configuration (GuestOS)

– Enable TCP/IP and Piping

Open SQL Server Configuration Manager (SSCM) go to ‘SQL Server Network Configuration’->Double Click on ‘TCP/IP’->Select Protocol Tab -> Select Enabled as ‘yes’
Goto IP Address Tab -> Change IP1 (or anyother) IP address to ‘’ (In same network as the host OS ip)->Select Active to yes, Enabled to yes
Now go back to main menu, change ‘Named Pipes’ to ‘Enabled’

– Set SQL Server Authentication

If you’re using ‘Windows Authentication’ to login to your Database. You have to change it to ‘SQL Server Authentication’ and set username and password for it , so that it can be accessed using PyODBC.

To change security authentication mode

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login, (‘sa’ will be your username)

In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the login.
On the Status page, in the Login section, click Enabled, and then click OK.

3. Disble Windows Firewall serivce
Simply turning off the firewall won’t do, you’ve to stop the service, do:
Run->Services.msc->Windows Firewall->Stop

4. Test connection
Using telnet:
From hostOS ( do: telnet 1433
You can also use a DB client such as, dbeaver.

5. PyODBC configurations

– freetds.conf

# local sql server
    host =
    port = 1433  
    tds version = 7.0

– odbcinst.ini

Driver = /usr/lib/i386-linux-gnu/odbc/
Description = ODBC connection via FreeTDS
Server =
Port = 1433 
Database = Test
TDS_Version = 8.0

– code

import pyodbc

dsn = 'sqlserverdatasource1'
user = 'sa'
password = '******'
database = 'TestDB' 

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)
cursor = cnxn.cursor()
print cursor

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
    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:

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

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

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)