This all came about because I needed to connect to a Microsoft SQL server over VPN, which turned out to be a royal pain. These notes describe the steps I took to get it working. There may be some extraneous steps, but I do not have time to figure out the minimum.
From a terminal run:
sudo apt-get install network-manager-vpnc
If you reboot, you will be able to configure the VPN from the Ubuntu Network Connections widget. From the command line, if you type:
you should get a bunch of questions to configure the VPN manually. If you prefer using the command line, you can put those parameters in /etc/vpnc/default.conf – just google to get the file syntax.
Install pyodbc using pip:
pip install pyodbc
If that seems too easy, you are right. You need to install ODBC drivers. Here is the command I used:
sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc
If you read the messages as they fly by, you will see something to the effect that the 64-bit version is being installed. Good.
Now you need to create/edit a bunch of ini and conf files. I am not sure if all this is necessary.
host = xxx.xxx.xxx.xxx
port = 1433
tds version 7.0
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = <servername>
Database = <databasename>
At this point, you should be able to start the VPN and connect to the database using the tsql tool that was installed when you installed freetds. Something like this:
tsql -S servername -U username -P password
SELECT * FROM INFORMATION_SCHEMA.TABLES
If that works, then try accessing from python:
con_string ='DRIVER=FreeTDS;DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' \
% (<datasource>, username, password, <databasename>)
conn = pyodbc.connect(con_string)
That should do it!