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.
File: /etc/freetds/freetds.conf [<servername>] host = xxx.xxx.xxx.xxx port = 1433 tds version 7.0 File: /etc/odbcinst.ini [FreeTDS] Description=FreeTDS Driver Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so File: /etc/odbc.ini [<datasource>] 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 GO
If that works, then try accessing from python:
import pyodbc 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!