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 192.168.56.1, 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 192.168.56.2/255.255.255.0)

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 ‘192.168.56.1’ (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 (192.168.56.1) do: telnet 192.168.56.2 1433
You can also use a DB client such as, dbeaver.

5. PyODBC configurations

– freetds.conf

# local sql server
[localsqlserver]
    host = 192.168.56.2
    port = 1433  
    tds version = 7.0

– odbcinst.ini

[sqlserverdatasource1]
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Description = ODBC connection via FreeTDS
Server = 192.168.56.2
Port = 1433 
Database = Test
TDS_Version = 8.0

– pyodbc-test.py 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
Advertisements

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

  1. Hi,
    what is not clear is where do you executes these steps:

    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 192.168.56.2/255.255.255.0)

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