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)?
(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.