MSSQL ODBC Client and Server on Ubuntu

Many years ago some coworkers and I collaborated on a document that would describe how to install the ODBC drivers from Microsoft on Debian, instead of RedHat as they were intended. Recently Microsoft has made this a much simpler task, so I decided to write a new version.

In the past couple of years Microsoft has made great strides in an effort to be more friendly to the open source community. I continue to be surprised and impressed at their results. A recent example is their support of their ODBC driver for not just RedHat but Ubuntu also.

This guide is meant to smooth over some of the rough spots in [the official guide](https://msdn.microsoft.com/en-us/library/hh568454(v=sql.110).aspx). Although the official guide includes commands to run, they seem to be insufficient, especially if you are using docker (implying a stripped down image.)

(Super short version: check out this git repo and play with it.)

🔗 Running MSSQL in Docker

If you are following this guide presumably you already have a Linux machine, but you may not have a Windows machine. If that’s the case, you probably need a server. That’s actually the easiest part, assuming you already have docker installed:

docker run \
   -e 'ACCEPT_EULA=Y' \
   -e 'SA_PASSWORD=password1!' \
   --name mssql \
   --rm \
   microsoft/mssql-server-linux

Note that the password must be “strong” for some value of strong. It’s annoying, but I doubt many SQL Server users will face extortion by leaving off a password because of this, unlike MongoDB users, so it’s probably a good thing.

And that’s it. You have a running SQL Server instance!

🔗 Setting up the client libraries

All but the final client step (to install Perl libraries) can be likely taken care of by following the up-to-date instructions from Microsoft

The following was tested on Ubuntu 16.04. Some things would surely need to be tweaked for a different version.

Install the basic tooling you need to get up and going:

apt-get update && DEBIAN_FRONTEND=noninteractive apt-get -y install apt-utils apt-transport-https wget

Add the (bizarrely named) Microsoft apt repo:

echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-xenial-release/ xenial main" > /etc/apt/sources.list.d/mssqlpreview.list'

Add the key for the new repo:

apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893

Load data from the new repo:

apt-get -y update

Install the ODBC driver and stuff we’ll need to build DBD::ODBC:

env ACCEPT_EULA=Y DEBIAN_FRONTEND=noninteractive apt-get install -y --no-install-recommends \
    build-essential \
    cpanminus \
    msodbcsql \
    unixodbc-dev-utf16

Microsoft claims you need to install this again; can’t hurt, but I doubt it’s important:

apt-get install unixodbc-dev-utf16

This will install the Perl DBD::ODBC driver:

DBD_ODBC_UNICODE=1 cpanm DBD::ODBC

Finally, if you are in a Docker container, you’ll need to generate a locale or the ODBC driver will error out super early:

locale-gen en_US en_US.UTF-8

🔗 Kicking the Tires

I made a stupid Perl script to be able to kick the tires a little bit:

!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use DBI;
use Getopt::Long;

my $show_output;

GetOptions(
   'show-output' => \$show_output,
);

my $dbh = DBI->connect(
   'dbi:ODBC:driver=ODBC Driver 13 for SQL Server;' .
   "server=tcp:$ENV{MSSQL_PORT_1433_TCP_ADDR};" .
   'database=msdb;' .
   'MARS_Connection=yes;',
   'sa',
   $ENV{MSSQL_ENV_SA_PASSWORD},
);

my $sql = shift;

if ($show_output) {
   print Dumper($dbh->selectall_arrayref($sql, undef, @ARGV))
} else {
   $dbh->do($sql, undef, @ARGV)
}

Pretend you are in docker:

export MSSQL_PORT_1433_TCP_ADDR=some_sql_server_ip
export MSSQL_ENV_SA_PASSWORD='password1!'

(If the above environment variables look bizarre to you, you can read a little bit more about them here.)

Then I created a fresh database:

psqlcli 'CREATE DATABASE MyDB'

…and a table:

psqlcli 'USE MyDB;
CREATE TABLE "Foo" (
   "id" int NOT NULL IDENTITY (1 ,1),
   "name" varchar(255) NOT NULL,
   CONSTRAINT "PK_Foo" PRIMARY KEY CLUSTERED ("id")
)'

…and inserted some rows:

psqlcli 'USE MyDB; INSERT INTO "Foo" (name) VALUES (?)' catherine
psqlcli 'USE MyDB; INSERT INTO "Foo" (name) VALUES (?)' roman
psqlcli 'USE MyDB; INSERT INTO "Foo" (name) VALUES (?)' axel
psqlcli 'USE MyDB; INSERT INTO "Foo" (name) VALUES (?)' frew

…and selected some:

$ psqlcli --show-output 'USE MyDB; SELECT * FROM Foo WHERE name = ?' fREW
$VAR1 = [
          [
            1,
            'frew'
          ]
        ];

Works just as expected!

---

I’m glad that this is so much less weird than before. The previous version of this document had some pretty sketchy hacks. This should be much more reliable.

---

I don’t know of any books that are directly relevant to ODBC on Linux, but I can recommend a couple of database books.

(The following includes affiliate links.)

Database Design for Mere Mortals is an excellent book for getting started on good RDBMS design. I read an older edition (the 3rd edition wasn’t out at the time) but I cannot imagine it changed much, other than newer data types that are relevant these days.

If you need something more basic, check out SQL in 10 Minutes. I started with this book and it was a lot of fun for me at the time, though that was more than a decade ago at this point.

Posted Mon, Mar 6, 2017

---

If you're interested in being notified when new posts are published, you can subscribe here; you'll get an email once a week at the most.