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.