UPDATE 2016-10-21 : You can now get the ODBC 13 driver for Linux with a much smoother install process than below. Get all the relevant information on the announcement from the Microsoft SQLNCli team blog.

Did you know you can now get SQL Server ODBC drivers for Ubuntu? Yes, no, maybe? It’s ok even if you haven’t since it’s pretty new! Anyway, this presents me with an ideal opportunity to standardise my SQL Server ODBC connections across the operating systems I use R on i.e. Windows and Ubuntu. My first trial was to get it working on Travis-CI since that’s where all my training magic happens and if it can’t work on a clean build like Travis, then where can it work? Alas, the ODBC 13 driver doesn’t work Ubuntu 14.04 so this set of instructions has been modified to provide code for Ubuntu 15.04 only.

TL;DR

It works, but it’s really hacky right now. Definitely looking forward to the next iterations of this driver.

Disclaimer

  • This will work for Ubuntu 15.04 but 14.04 has a different set of C compilers
  • This is currently hacky, and Microsoft are on the case for improving it so this post could quickly become out of date.
  • Be very careful installing the driver on an existing machine. Due to the overwriting of unixODBC if already installed and potential compatibility issues with other driver managers you may have installed.

Line by line

wget https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz -P ..

Download the compressed file containing all the relevant stuff. This URL is important – the website does not provide a URL like this and this one is likely to be unstable. Microsoft are aware of this as a problem for users who like to script everything and will hopefully be addressing it in the short to medium term.

The -P .. tells the wget command to dump the file in the parent directory so that it won’t set off warnings when I build my R package.

tar xvzf ../msodbcsql-13.0.0.0.tar.gz -C ..

This little line unzips the file we just downloaded to the parent directory.

sed -i '14d' ../msodbcsql-13.0.0.0/build_dm.sh
sed -i '/tmp=/c\tmp=/tmp/odbcbuilds' ../msodbcsql-13.0.0.0/build_dm.sh

Unfortunately the default script that should be executed next generates a random directory for the unixODBC driver manager. The random directory is present in the output text and not easy to pipe into the next command. Consequently, with much help from Vin from MSFT we have this current hack to change the directory to a fixed directory.

../msodbcsql-13.0.0.0/build_dm.sh --accept-warning

This line runs a shell script that builds the unixODBC driver manager. Note – you can’t rely on the unixODBC driver available via apt-get at this time due to the SQL Server ODBC driver not being compatible (currently) with the latest versions. Also, it wasn’t noted in the manual but I had to add the --accept-warning to suppress some sort of notification that wanted to be triggered. I suspect I just sold my soul and that I’m encouraging you to do the same.

cd /tmp/odbcbuilds/unixODBC-2.3.1
sudo make install

These lines shunts us over to the directory for the unixODBC build and installs it. The sudo is necessary for the installation to the usr/ directory.

cd ~

This gets you back to the your starting package directory for continuing on to package install.

sudo apt-get install libgss3 -y

This dependency was needed by the ODBC driver

../msodbcsql-13.0.0.0/install.sh verify

Verify the driver can be installed. This line wasn’t so great since it doesn’t check for a bug/feature – that you’re in the right directory – otherwise, a series of file copies in the install process won’t work.

cd ../msodbcsql-13.0.0.0/
sudo ./install.sh install --accept-license

Proceed to install the driver in the right directory

odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

Test the driver is usable

The final file

wget https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz -P ..
tar xvzf ../msodbcsql-13.0.0.0.tar.gz -C ..
sed -i '14d' ../msodbcsql-13.0.0.0/build_dm.sh
sed -i '/tmp=/ctmp=/tmp/odbcbuilds' ../msodbcsql-13.0.0.0/build_dm.sh
../msodbcsql-13.0.0.0/build_dm.sh --accept-warning
cd /tmp/odbcbuilds/unixODBC-2.3.1
sudo make install
cd ~
sudo apt-get install libgss3 -y
../msodbcsql-13.0.0.0/install.sh verify
cd ../msodbcsql-13.0.0.0/
sudo ./install.sh install --accept-license
odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

The manuals (for reading)

Installing SQL Server ODBC drivers on Ubuntu 15.04
Tagged on:             

6 thoughts on “Installing SQL Server ODBC drivers on Ubuntu 15.04

    • 30/09/2016 at 22:30
      Permalink

      it looks like the theme rendered away the asterisks in my glob command: cd /tmp/unixODBC.**/unixODBC-**; make install

      Reply
      • 30/09/2016 at 22:31
        Permalink

        jeez, just one star. I wish these things had a preview 🙂

        cd /tmp/unixODBC.*/unixODBC-*; make install

        Reply
        • 01/10/2016 at 15:42
          Permalink

          Sorry – I <3 markdown so I have it enabled, thus causing problems in code sometimes.

          Reply
    • 01/10/2016 at 15:41
      Permalink

      Ah, I see what you did there!

      Thanks for teaching me I can cd to a wildcarded directory – that’s super awesome! 😀

      Reply
      • 21/10/2016 at 23:30
        Permalink

        Hey Jeff, the v13 is now available with a much better install process;. See the update at the top of the blog post for the relevant link.

        Reply

What do you think?