Posts Tagged ‘mssql’

Installing ODBC Driver 17 for SQL Server on Debian

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.

Dependencies

These are the base dependencies to be able to invoke the driver:

  • libltdl7
  • libodbc1
  • odbcinst
  • odbcinst1debian2
  • locales-all

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

ODBC Configuration

After installing dependencies, config the driver by appending the config details to /etc/odbcinst.ini:

[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

OpenSSL Issue

The final issue I ran into was super cryptic, when trying to connect to a MSSQL instance I ran into the following error:

SQLSTATE[08001]: [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.

A connection!

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

Spott map

Something pretty cool in MSSQL Server Management Studio: for columns with the geography data type, Management Studio will plot the points on a map. Here’s a map of all locations tagged from all dotspott users.

spott map

Specifying blank string in MSSQL Server Managment Studio

If you want a specify a blank string in the Column Properties table (instead of writing a query) for something like the Default Value or Binding property, enter (”)

MSSQL Server Management Studio

Just having will not work as that will actually insert 2 apostrophes.

h/t to Dhowe from this thread on bigresource.com