OGR2OGR with MS SQL on Ubuntu 16.04

Question

I need to access a Microsoft SQL Server with OGR2OGR from an Ubuntu Server 16.04. It is working on Microsoft, so the basic setup is fine.

My problem is that when I run my OGR2OGR command (after installing GDAL with sudo apt-get install gdal-bin):

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=tcp:<DATABASE_SERVER>,<PORT>;database=<DATABASE_NAME>;uid=<USER>;Pwd=<PASSWORD>;" "<IMPORT PATH FILE>" -nln "<TABLE NAME>" -progress

I get the error:

ERROR 1: Unable to initialize connection to the server for MSSQL:"DATABASE_SERVER"; [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found Try specifying the driver in the connection string from the list of available drivers:

I tried to install Microsoft ODBC Driver for SQL Server based on this instruction, but it cannot be installed when gdal is installed. The error is:

Some packages could not be installed. This may mean that you have requested an impossible situation or if you are using the unstable distribution that some required packages have not yet been created or been moved out of Incoming. The following information may help to resolve the situation:

The following packages have unmet dependencies: msodbcsql : Depends: unixodbc-utf16 (>= 2.3.1-1) but it is not going to be installed E: Unable to correct problems, you have held broken packages.

I managed to install the Microsoft ODBC driver after a while but it meant removing GDAL. So I can either have GDAL or the Microsoft ODBC driver. Is there a way to solve this problem?

Thanks for your help!

UPDATE (2017.01.03): I found the following solution and I also commented on the according article on the MSDN Blog (so far without reply) here

Install UnixODBC if not already there

sudo apt-get install unixodbc unixodbc-dev

Download from somewhere: msodbcsql-11.0.2270.0.tar.gz (it could be better to use msodbcsql-13.x.x.x.tar.gz but I was not able to find it) and

tar xvfz msodbcsql-11.0.2270.0.tar.gz
cd msodbcsql-11.0.2270.0
ldd lib64/libmsodbcsql-11.0.so.2270.0

If there are missing dependencies install them, in my case it could be done like this: (everything except the apt-get install is to fix the naming of the file by creating a link:)

sudo apt-get install libssl1.0.0 libssl-dev
cd /lib/x86_64-linux-gnu
sudo ln -s libssl.so.1.0.0 libssl.so.10
sudo ln -s libcrypto.so.1.0.0 libcrypto.so.10

Installing and cleaning up:

sudo bash ./install.sh install --force --accept-license

rm -rf /tmp/msodbcubuntu

If the Driver is not found by the tool using it, (e.g. ogr2ogr or pyodbc) edit /etc/odbcinst.ini and create a connector for [SQL Server]

[SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=2

Answer 1

Shamelessly from : https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17

Verify with

ogr2ogr --formats

Which should then list:

MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database