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)?
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
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.
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
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:
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 servernNet-Lib error during Operation now in progress
(115)nDB-Lib error message 20002, severity 9:nAdaptive Server
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:
Or, at the Python code, and at the connect function, adding the parameter:
With that, I am able to to use pymssql 2.1.4 to connect to Azure without issues.