How to configure pymssql with SSL support on Ubunt

Question

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

Answer 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.

Answer 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