Problems w pyodbc and pymssql on Python 2.7

I am writing this in Feb 2021. Due to circumstances beyond my control, I am forced to use Python 2.7.

For many years I have been using pyodbc to connect to a remote SQL Server over an SSH tunnel. I have been using something like this: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX.

Last night my Mac auto-updated to 11.2. Suddenly I could no longer connect. I fiddled with stuff and was able to get tsql and isql working. But pyodbc would not run. Each time I ran it, I either got this error:

pyodbc.OperationalError: ('08001', u'[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Or some sort of seg fault:

Process finished with exit code 134 (interrupted by signal 6: SIGABRT)

I got similar errors using pymssql.

Just for fun, I tried the same code in Python 3.8. Everything worked!

Debugging PyODBC Install

I had some problems installing ODCB, FreeTDS and PyODBC. I was connecting to a remote MS SQL server using an SSH tunnel. After roughly following these notes, things were not working. These commands worked:

TDSVER=8.0 tsql -H 127.0.0.1 -p 1433 -U my_username -P my_password
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

If I replace TDSVER=7.0 things stop working. Turns out that was the version in

/etc/freetds/freetds.conf

Installing Pyodbc on Ubuntu 12.04 64-bit

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.

VPN

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:

sudo vpnc

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.

Pyodbc

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>

Testing

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!