Apr 6 2020 · Databases
For a project at work I went through a bit of struggle getting the Microsoft ODBC Driver 17 for SQL Server installed on a barebones Debian system (this was for a Docker container, no SSH, no package manager). Here’s what I discovered and did to finally get it working.
These are the base dependencies to be able to invoke the driver:
locales-all package is strictly necessary, but some locale configuration is necessary or you’ll run into the error
locale::facet::_S_create_c_locale name not valid when trying to run the
sqlcmd utility. This article provides a bit more detail.
After installing dependencies, config the driver by appending the config details to
[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1 UsageCount=1
The final issue I ran into was super cryptic, when trying to connect to a MSSQL instance I ran into the following error:
SQLSTATE: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
The issue is described GitHub issue. In short this is a compatibility issue with the driver and OpenSSL + Debian 10, which has disabled SHA1 for signatures. The fix involves editing
/etc/ssl/openssl.cnf and changing the last line from
CipherString = DEFAULT@SECLEVEL=2 to
CipherString = DEFAULT@SECLEVEL=1. Of course consider if doing this is a security risk in your environment and for your use-case.
With the above done, I was able to successfully connect to the MSSQL instance using
sqlcmd. I was hoping at this point things would “just work” with pyodbc, but that wasn’t the case (the connection would simply hang when attempting to connect.. no timeouts, no errors). So at this point, I’m considering whether it’s still worth it to try and use this driver or stick with the existing (FreeTDS).