How to configure pymssql with SSL support on Ubuntu?

What were the steps required to configure (the now discontinued) pymssql with SSL support on Ubuntu so I can connect to a SQL Server instance that requires an encrypted connection (e.g., Azure)?

Solution 1:

Ubuntu 16.04 LTS

(See this answer for Ubuntu 18.04 LTS.)

The following worked for me on a clean install of Xubuntu 16.04 LTS x64:

The first challenge is that the FreeTDS we get from the Ubuntu 16.04 repositories does not support SSL "out of the box", so we need to build our own. Start by installing python3-pip (which also installs build-essentials, g++, and a bunch of other stuff we'll need) and libssl-dev (the OpenSSL libraries required for building FreeTDS with SSL support)

sudo apt install python3-pip libssl-dev

Download the source code for FreeTDS by clicking the "Stable Release" link at freetds.org. Unpack the archive, switch to the directory you just created (e.g., freetds-1.00.104), and then do

./configure --with-openssl=/usr/include/openssl --enable-msdblib
make
sudo make install

Check the build with

tsql -C

and ensure that "TDS version: auto" and "OpenSSL: yes" are listed. Then use tsql to test a "raw" FreeTDS connection, e.g.,

tsql -H example.com -p 1433 -U youruserid -P yourpassword

Now to install pymssql. By default, recent versions ship as a pre-compiled "wheel" file that does not support encrypted connections so we need to install from the pymssql source. Starting with pymssql 2.1.4, the build process relies on Cython, so first do

pip3 install --user Cython

and then do

pip3 install --user --no-binary pymssql pymssql

When the build is complete, pymssql is installed.

But... it won't work (yet). When we try to do import pymssql in Python we get

ImportError: libsybdb.so.5: cannot open shared object file: No such file or directory

because apparently that file is in the "wrong" place. The fix (ref: here) is to create a symlink in the "right" place that points to the actual file

sudo ln -s /usr/local/lib/libsybdb.so.5 /usr/lib/libsybdb.so.5
sudo ldconfig

Now pymssql works with SSL connections.

For me, anyway.

Solution 2:

Ubuntu 18.04 LTS

The Ubuntu 18.04 repositories will install a version of FreeTDS that supports GnuTLS so it is not absolutely necessary to build FreeTDS from source. However, we still need to build pymssql from source because simply doing the usual

pip3 install --user pymssql

will install a pre-compiled "wheel" that does not support secure connections. Instead, we need to do

sudo apt install python3-pip freetds-dev
pip3 install --user Cython
pip3 install --user --no-binary pymssql pymssql

Solution 3:

For Ubuntu 16.04 it seems that at least the Docker containers have a FreeTDS version that already supports SSL.

Also, at least for Python 2.7, Cython is not needed:

https://github.com/tds-fdw/ci-setup/blob/master/ubuntu16.04/Dockerfile (lines 23-39)

But there's something to keep in mind!

The TDS version to connect to Azure must be forced to be at least 7.1 (or newer, depending on your needs: https://www.freetds.org/userguide/choosingtdsprotocol.htm)

Otherwise you will see the infamous:

[ERROR] (20017, 'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server\nNet-Lib error during Operation now in progress (115)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')

For some reason this was not needed for Ubuntu 14.04 and pymssql 2.1.3 without any extra configuration (https://github.com/tds-fdw/ci-setup/blob/master/ubuntu14.04/Dockerfile)

It can be done with either:

export TDSVER=7.1

Or, at the Python code, and at the connect function, adding the parameter:

 tds_version='7.1'

With that, I am able to to use pymssql 2.1.4 to connect to Azure without issues.