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:
4. Test connection
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
# local sql server [localsqlserver] host = 192.168.56.2 port = 1433 tds version = 7.0
[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