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.
It works, but it’s really hacky right now. Definitely looking forward to the next iterations of this driver.
- 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-126.96.36.199.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.
-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-188.8.131.52.tar.gz -C ..
This little line unzips the file we just downloaded to the parent directory.
sed -i '14d' ../msodbcsql-184.108.40.206/build_dm.sh sed -i '/tmp=/c\tmp=/tmp/odbcbuilds' ../msodbcsql-220.127.116.11/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.
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
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
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-18.104.22.168/ 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-22.214.171.124.tar.gz -P .. tar xvzf ../msodbcsql-126.96.36.199.tar.gz -C .. sed -i '14d' ../msodbcsql-188.8.131.52/build_dm.sh sed -i '/tmp=/ctmp=/tmp/odbcbuilds' ../msodbcsql-184.108.40.206/build_dm.sh ../msodbcsql-220.127.116.11/build_dm.sh --accept-warning cd /tmp/odbcbuilds/unixODBC-2.3.1 sudo make install cd ~ sudo apt-get install libgss3 -y ../msodbcsql-18.104.22.168/install.sh verify cd ../msodbcsql-22.214.171.124/ sudo ./install.sh install --accept-license odbcinst -q -d -n "ODBC Driver 13 for SQL Server"
The manuals (for reading)
- Microsoft ODBC Driver for SQL Server on Linux
- Installing the Driver Manager
- Installing the Microsoft ODBC Driver for SQL Server on Linux