Install and Configure the MS ODBC Driver on Debian

This page is only here for historical interest; the updated guide is here.

This was originally written by my coworker Wes Malone and adapted to Ubuntu by my other coworker Geoff Darling. Basically it should get you up and running with Microsoft’s official ODBC driver in Debian based Linuxes. Enjoy!

The Microsoft ODBC Driver (AKA the SQL Server Native Client) is Microsoft’s official ODBC driver for SQL Server. Since 2011 Microsoft has provided binary builds officially supported on Redhat Enterprise Linux. The Linux sqlncli supports all server features of SQL Server, including Unicode and Multiple Active Result-sets (MARS). We ran into Unicode troubles porting our Perl ODBC applications to the FreeTDS ODBC driver, not to mention that some of our apps rely on MARS, which is unsupported by FreeTDS.

This guide is meant to be a more newbie friendly version combining the official Microsoft install directions and this guide for adapting the driver install for Debian.

Get Ready

Stop your apps first. I don’t know if it would interfere with the process, but let’s just be safe.

Grab the sqlncli11 package from MS.

$ wget http://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/RedHat6/msodbcsql-11.0.2270.0.tar.gz

Note: If this link doesn’t work, download the latest version of the driver for RedHat 6 from Microsoft. It may also be worth checking just in case they release a newer version

Install unixODBC

Starting with Utopic Unicorn (14.10) you can just apt-get install unixodbc unixodbc-dev then go straight to Driver Compatibility! –frew

$ wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz

Get rid of any previous ODBC packages.

$ sudo apt-get remove libodbc1 unixodbc unixodbc-dev

This is a stupidly manual process because the Debian packagers have woefully dragged their feet for many years on updating this package. If you’d like to remove this step, please bug them here. –frew

It looks like 2.3.1 is finally in sid, so Lord willing it will trickle down into stable/ubuntu in a year or so. –frew

Unpack unixodbc:

$ tar xf unixODBC-2.3.2.tar.gz

Now we can build and install unixODBC.

$ ./configure --disable-gui --disable-drivers --enable-stats=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
$ make
$ sudo make install

unixODBC installs to /usr/local/lib by default but the Microsoft driver expects it in /usr/lib. We have to let the system know we’re using /usr/local/lib, so if necessary on your system, add the appropriate path to /etc/ld.so.conf and run ldconfig to update the linker path.

$ sudo vim /etc/ld.so.conf # add /usr/local/lib to the end
$ sudo ldconfig

Driver Compatibility

Let’s paper over some of the differences between the RHEL environment that the driver expects and Debian. Unpack it and check for dependencies of the library.

$ tar xf sqlncli*
$ cd sqlncli*
$ ldd lib64/libsqlncli*

On my system it can’t find libcrypto and libssl because of the versioning differences between RHEL and Debian.

mitsi@silver:~/sqlncli-11.0.1790.0$ ldd lib64/libsqlncli-11.0.so.1790.0
        linux-vdso.so.1 =>  (0x00007fffc971b000)
        libcrypto.so.10 => not found
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f3bc6064000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f3bc5e5b000)
        libssl.so.10 => not found
        libuuid.so.1 => /lib/x86_64-linux-gnu/libuuid.so.1 (0x00007f3bc5c56000)
        libodbcinst.so.1 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 (0x00007f3bc5a43000)
        ...

The version bump from 1 to 2 is an incredibly minimal change that’s actually included in 2.3.0 as well. We haven’t seen any breakage due to the modification. This is the reason we add the symlinks related to libodbc below. –frew

We need to add ODBC symlinks because unixODBC bumped their major version, but the native client doesn’t build against version 2. The next commands should do that:

$ cd /usr/lib
$ sudo ln -s x86_64-linux-gnu/libodbc.so.2 libodbc.so.1
$ sudo ln -s x86_64-linux-gnu/libodbccr.so.2 libodbccr.so.1
$ sudo ln -s x86_64-linux-gnu/libodbcinst.so.2 libodbcinst.so.1

Add a couple of symlinks in /usr/lib and the driver can find what it needs.

$ cd /usr/lib
$ sudo ln -s libssl.so.0.9.8 libssl.so.10
$ sudo ln -s libcrypto.so.0.9.8 libcrypto.so.10

Ubuntu is slightly different because the libs are in /usr/lib/$arch

$ cd /usr/lib
$ sudo ln -s x86_64-linux-gnu/libssl.so.0.9.8 libssl.so.10
$ sudo ln -s x86_64-linux-gnu/libcrypto.so.0.9.8 libcrypto.so.10

You can check again with ldd that all the libraries are found.

Install the Microsoft Driver

Now we can install the Microsoft driver. Run the installer with bash because the install script references /bin/sh in its shebang but expects it to be bash anyway. The force option will continue with the install even though we’re missing rpm etc.

$ sudo bash ./install.sh install --force
# type q to exit the terms
# type YES to accept and continue with install

The install output looks like this for me, note the last line confirming the install:

Checking for 64 bit Linux compatible OS ................................. FAILED
Checking required libs are installed ............................... NOT CHECKED
unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED
unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED

Now test the install with sqlcmd. Connecting to an imaginary SQL Server should time out. Any other errors about missing libraries mean you should double-check your symlinks and ld.so.conf. On one install I’d forgotten to run ldconfig.

$ sqlcmd -S localhost
SqlState HYT00, Login timeout expired
TCP Provider: Error code 0x71
A network-related or instance-specific error has occurred while establishing a connection to SQL Serve
r. Server is not found or not accessible. Check if instance name is correct and if SQL Server is confi
gured to allow remote connections. For more information see SQL Server Books Online.

Installing Perl DBD::ODBC with Unicode support

Everything is installed now, but we Perl-ers need to install DBD::ODBC with the Unicode option enabled. It’s disabled by default on Linux for now because of poor driver support. Once the default switches to Unicode on for sqlncli then this step can be skipped.

$ cpanm --look DBD::ODBC
$ perl Makefile.PL -u # enable unicode support
$ make
$ make test
$ make install

Pro Tips

ODBC driver config is in /usr/local/etc/odbcinst.ini. The odbcinst.ini in /etc is a clever ruse devised by your previous ODBC install.
Your connect strings should look something like dbi:ODBC:driver=SQL Server Native Client 11.0;server=tcp:10.1.2.3;database=DB_TOWNE;MARS_Connection=yes;
See that MARS_Connection=yes up there? That’s right, MARS is supported :D

2013-10-24 UPDATE: unixODBC 2.3.2 was released and has been incorporated into the howto. Additionally mje recommended setting --enable-stats=no for speed, especially since with the gui disabled they aren’t used anyway.

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 Fri, Jul 5, 2013

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.