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?

Now I can create R functionality that can reliably depend on SQL Server without having to fallback to JDBC. A definite woohoo moment!

TL;DR

It works, but it’s really hacky right now. Definitely looking forward to the next iterations of this driver. I’m also really glad I could squelch all my commits when I merged the dev branch to master for this exercise – it took a while to remember I could test my commands on an ubuntu docker container first – and even when I tested on docker I still had to test on travis line by line. The final .travis.yml file is available for folks to copy & paste from.

Disclaimer

• Each line in the travis file could be put into a generic script and used on any ubuntu system but there may be some steps missing like installing gcc that are present on the Travis infrastructure. You probably can’t take the script and expect it to work elsewhere first time though.
• 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=/ctmp=/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 $TRAVIS_BUILD_DIR

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

language: r
sudo: true
warnings_are_errors: true
cache: packages
r_github_packages:
  - rich-iannone/DiagrammeR
before_install:
  - chmod 755 ./.push_gh_pages.sh
  - 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 $TRAVIS_BUILD_DIR
  - 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
  - cd $TRAVIS_BUILD_DIR
  - odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

after_success:
  - ./.push_gh_pages.sh

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

The post Installing SQL Server ODBC drivers on Ubuntu (in Travis-CI) appeared first on It's a Locke.