Fixing pymssql 2.1.5 from pip to work on CentOS7

Updating the tds_fdw Continuous Integration to test CentOS7, I noticed pymsql 2.1.5 could not connect anymore to our Azure SQL Database and all I could get was:

pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (instance.database.windows.net)\n')

Enabling TDS debugging revealed that, for some reason, the Azure SQL database just rejected the logging information:

net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 02 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 c1 03 00 00            -                        |....|

packet.c:410:Received packet
0000 04 01 00 25 00 00 01 00-00 00 15 00 06 01 00 1b |...%.... ........|
0010 00 01 02 00 1c 00 01 03-00 1d 00 00 ff 0c 00 07 |........ ........|
0020 d0 00 00 03 00         -                        |.....|

login.c:1281:detected crypt flag 3
login.c:571:login packet rejected
query.c:3757:tds_disconnect() 
util.c:179:Changed query state from IDLE to DEAD
util.c:333:tdserror(0xec8f90, 0xd54d40, 20002, 0)
dblib.c:8144:dbperror(0xdc2290, 20002, 0)
dblib.c:8212:dbperror: Calling dblib_err_handler with msgno = 20002; msg->msgtext = "Adaptive Server connection failed (instance.database.windows.net)"
dblib.c:8234:dbperror: dblib_err_handler for msgno = 20002; msg->msgtext = "Adaptive Server connection failed (instance.database.windows.net)" -- returns 2 (INT_CANCEL)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1282:tdsdbopen: tds_connect_and_login failed for "instance.database.windows.net"!

So without anything else to do at this point, I decided to check the pymssql list and there was the solution.

Basically it seems the FreeTDS version provided by EPEL7 (1.1.20) does not work with pymssql 2.1.5 provided by pip.

You can use pip to compile pymssql for you.

First, make sure you have all dependencies required to build and run, as root:

yum install freetds-devel gcc python-devel

Then, you need to install a couple of pip dependencies:

   pip install setuptools_git
   pip install Cython==0.29.36

In this case you need to pin Cython to 0.26.36 because version 3.0.0 release in July 2023 breaks pymssql builds.

Finally, call pip as follows:

pip install --no-binary :all: pymssql==2.1.5

This will build pymssql from sources, instead of using the binaries the repository has available.

And that’s it! You can start using pymssql again… while you prepare the migration.