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

Increase your VirtualBox Hard Disk Size

Steps to Increase your VirtualBox Hard Disk Size

cd into the directory where your virtual drive is found (usually ~/VirtualBox VMs/{Your VirtualBox PC name})

    cd ~/VirtualBox VMs/WinXP_SP3

run the modify hd function with the new size (in MB) I choose 25GB (25 * 1024mb = 25600MB)

    VBoxManage modifyhd WinXP_SP3.vdi --resize 25600

VirtualBox will resize your hard drive and you will see the following output from the system indicating that the process is complete

    0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Now start up your Virtual Windows Machine. Right Click on ‘My Computer’ (‘Computer’ in newer versions) and choose Manage.

Click on the Disk Management option on the left panel. You will notice some black space next to your C drive (this means the space addition was successful.)

Right Click on the C drive and choose Extend Volume. Follow the wizard and you have successfully increased the size of your VirtualBox disk. Or, if that option is not available use a third party partition making software like Partition Magic or create a second primary partition.

Virtual Box Configurations for Windows XP Guest OS, Ubuntu 12.04 LTS Host OS.

– File sharing with the VirtualBox Guest OS (Windows Guest, Ubuntu Host)

1. Create shared folder /home/<user name>/shared
2. From Virtual Box settings include the folder from the shared folder option
3. Download VBoxGuestAddition.iso [It has to be the same version as the VirtualBox app] Dowload from : http://download.virtualbox.org/virtualbox/
4. Insert the iso as a virtual CD from Virtual Box settings storage section
5. Access the CD from Windows guest OS.
6. Install Guest addition in Windows
7. Reboot
8. Access the shared folder from My Network Places

– Enable USB detection in Virtual Box OS

1. Download extension pack of the same version of the Virtual Box Oracle_VM_VirtualBox_Extension_Pack-4.2.16-86992.vbox-extpack
double-click it to run by VB and install.
2. Add your user to vboxusers group and log out and log in.
# usermod -a -G <group-name> <user-name>
3. From settings add usb filter by selecting the appropriate usb