We mainly develop ODB on GNU/Linux and then regularly test it on other platforms. This posed an interesting challenge once we started working on support for Microsoft SQL Server. The recommended way to access SQL Server from native applications is using the SQL Server Native Client ODBC driver. The problem is (or rather was, as you will see shortly) that Native Client is only available on Windows. In our case this meant that while we could still build everything on Linux (using a MinGW cross-compiler), to actually run the tests we would have to copy everything over to a Windows box. And that would be a major inconvenience compared to running tests directly from Emacs, which is what I am used to.

Doing a few web searches didn’t yield anything useful. There is the ODBC driver that is part of the FreeTDS project but it has limited functionality (for example, it doesn’t support the Multiple Active Result Sets (MARS) feature). Then there are a number of commercial offerings with convoluted licensing models and restrictions. But the main problem with all these alternatives is that we are not really interested in testing any of these drivers. For now we are only interested in making sure that ODB works well with the Microsoft ODBC driver, since that’s what 99% of the users will use anyway.

So what we really need is the Microsoft Native Client ODBC driver for Linux. Now you may be thinking, yeah, dream on, Microsoft will never release anything like this. Well, you may be surprised, but Microsoft did exactly that. About a month ago they pre-announced a Linux driver and a preview version was made available as part of the SQL Server 2012 RC0 release. You can also browse the driver documentation online. We have been running some preliminary ODB tests with this driver and so far it has been working really well.

While this preview release of the driver is only officially supported on 64-bit RedHat EL 5, it is not too difficult to install it on 64-bit Debian or Ubuntu. Below are the instructions.

Installing SQL Server ODBC driver on Debian/Ubuntu

The first step in installing the driver is to make sure you have unixODBC 2.3.0 driver manager installed. At the time of writing, the latest version of the unixodbc package available from the Debian/Ubuntu repositories was 2.2.14. That meant I had to build and install the driver manager from sources. I didn’t try to use the install script that comes with the Microsoft driver, opting to use a modified version of their Manual Installation steps:

1. First make sure that any older version of the unixODBC that you may have installed is removed:

   $ apt-get remove libodbc1 unixodbc unixodbc-dev
   

2. Download and unpack unixODBC-2.3.0.tar.gz (see an update below on using unixODBC-2.3.1 instead).
3. While the Microsoft instructions show how to install unixODBC to /usr, I like to keep custom-build software in /usr/local and installing unixODBC to this directory works just as well:

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

The next step is to install the driver. But before we run the installation script that comes with the package, let’s make sure we have all the dependencies. For that, first download and unpack the driver archive. Inside, in the lib64/ directory, you will find the libsqlncli-11.0.so.1720.0 file. This is the actual driver. Let’s run ldd on it to see if there are any missing dependencies:

$ ldd libsqlncli-11.0.so.1720.0

Look for lines that have “not found” in them. They indicate missing dependencies. When I first ran this command on my Debian box, I got the following output:

ldd ./libsqlncli-11.0.so.1720.0
  libcrypto.so.6 => not found
  libodbc.so.1 => /usr/local/lib/libodbc.so.1
  libssl.so.6 => not found
  libuuid.so.1 => /lib/libuuid.so.1
  libodbcinst.so.1 => /usr/local/lib/libodbcinst.so.1
  libkrb5.so.3 => /usr/lib/libkrb5.so.3
  libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2
  libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6
  libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1
  libltdl.so.7 => /usr/lib/libltdl.so.7
  libk5crypto.so.3 => /usr/lib/libk5crypto.so.3
  libkrb5support.so.0 => /usr/lib/libkrb5support.so.0
  libkeyutils.so.1 => /lib/libkeyutils.so.1
  ...

Which indicated that I had libcrypto.so.6 and libssl.so.6 missing. As a general approach to resolving missing dependencies, you can enter the library name in the Debian package search or Ubuntu package search (use the “Search the contents of packages” section) and then install the package that contains the missing library.

However, if you try to do this for libcrypto.so.6 or libssl.so.6, you won’t find any packages. The reason for this is the different versioning schemes used for these libraries in RedHat EL and Debian/Ubuntu. In Debian/Ubuntu the equivalent libraries are called libcrypto.so.0.9.8 and libssl.so.0.9.8 and are part of the libssl0.9.8 package. So to resolve these dependencies, first make sure that the libssl0.9.8 package is installed and then create the libcrypto.so.6 and libssl.so.6 symbolic links:

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

Also note that if you have “not found” next to libodbc.so.1 (the unixODBC driver manager we have just installed), then this most likely means that /usr/local/lib is not in your dynamic linker search path. If that’s the case, add it to the /etc/ld.so.conf and don’t forget to reload the cache by running ldconfig as root.

Once all the dependencies are met, we can finally run the script to install the driver. We have to use the --force option to ignore some of the compatibility tests performed by the script:

$ sudo bash ./install.sh install --force

To test the installation you can try to connect to the local host using sqlcmd:

$ sqlcmd -S localhost

Unless you are running the Linux edition of SQL Server (wink wink) you should get an error message indicating that a network connection could not be established. Any other error, such as inability to load a shared library, most likely indicates a missing dependency or a configuration error.

Update: After performing additional tests with ODB we have discovered that unixODBC-2.3.0 doesn’t work very well in multithreaded applications and applications that create more than one ODBC connection. However, the recently released unixODBC-2.3.1 appears to have addressed this issue. With this version all the ODB tests work on Linux just as well as on Windows. The following instructions explain how to make the Native Client ODBC driver for Linux work with unixODBC 2.3.1 instead of 2.3.0.

With the release of version 2.3.1 the unixODBC project changed the shared libraries version. This causes a problem when we try to use this version of unixODBC with Native Client because the driver is linked with the previous version. There are two ways to address this problem, as discussed below.

The easiest approach is to change the shared libraries version back to the old value in the unixODBC source distribution. Using the original instructions, after unpacking unixODBC-2.3.1 (instead of 2.3.0), open the configure file in a text editor and search for the LIB_VERSION= string. Then change it from reading:

LIB_VERSION="2:0:0"

To read:

LIB_VERSION="1:0:0"

Then follow the remainder of the original instructions without any further modifications.

The alternative approach is a bit more involved but it doesn’t require changing the shared libraries version. This, for example, can be preferable if you are installing unixODBC-2.3.1 from a binary package instead of building it yourself.

With this approach we install unixODBC-2.3.1 just like unixODBC-2.3.0, as described in the original instructions. Once this is done, the next step is to create a directory which will contain the “compatibility” symbolic links for the libraries. This can be any directory as long as it is not in the /etc/ld.so.conf list. The last part is important: if this directory is in ld.so.conf, things won’t work since ldconfig checks the library version embedded in the library and will ignore files that have version mismatches. This is why we cannot create the “compatibility” symlinks in, say, /usr/local/lib. However, /usr/local/lib/odbc-compat will work just fine:

$ sudo mkdir /usr/local/lib/odbc-compat

Once the directory is created, we add the following symbolic links:

$ cd /usr/local/lib/odbc-compat
$ sudo ln -s /usr/local/lib/libodbc.so.2 libodbc.so.1
$ sudo ln -s /usr/local/lib/libodbccr.so.2 libodbccr.so.1
$ sudo ln -s /usr/local/lib/libodbcinst.so.2 libodbcinst.so.1

The last step is to add the new directory to the
LD_LIBRARY_PATH environment variable (remember we cannot use the ld.so.conf mechanism):

export LD_LIBRARY_PATH=/usr/local/lib/odbc-compat:$LD_LIBRARY_PATH

If you want this path to be automatically added for your login, then you can add the above line to your ~/.bash_login file. If you want this to be system-wide, then instead add it to /etc/profile.

Once all this is done you can follow the remainder of the original instructions without any further modifications.

This entry was posted on Friday, December 2nd, 2011 at 10:34 am and is filed under ORM, C++. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.