Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication

A while ago, the Windows-world and the Linux-world were not the best friends in communicating with each other. Especially not when it came to proprietary stuff like Microsoft SQL server. in contrast to everybody’s expectations, somewhere end 2011, beginning 2012, Microsoft released an ODBC driver for SQL server for Linux. This driver allows executing queries from a Linux machine to a Microsoft SQL Server database. The driver can be used in combination with Kerberos tickets and AD authentication to execute queries.

The reasons why you would need this driver could be very different but an example could be to export or import data from a database running on Linux to an MS SQL server or just to get some information that is only available on an MS SQL server and use that information in a bash-script.

While the instructions on how to install the driver are not that difficult, a step by step guide in how to install and use the driver isn’t redundant. Especially regarding the Kerberos part, things are usually made more complicated than they are.

The driver is advertised as compatible with RHEL verison 5 and 6 and it’s derivatives like CentOS and Scientific Linux. The driver seems to work fine on CentOS 7 and probably you can use the instructions on other Linux distributions without issues. The example is executed on a minimal CentOS 7.

Installing the Microsoft SQL Server ODBC driver for Linux

The first step is to install some of the requirements to be able to build the driver. This can be done with Yum:

Next, download the archive containing the software from Microsoft. You can find it here: http://www.microsoft.com/en-us/download/details.aspx?id=28160. Click the download link and choose one of the packages that are offered. The 5 or 6 in the name correspondents to the RHEL-release that is supported. I chose to download the file for el6:

sqlserver_2

When the download completes, transfer the file to your Linux machine and unpack it, optionally remove the tar file after extraction and cd to the directory containing the extracted files:

The archive contains a script to verify if your system meets the necessary requirements to build the driver. Before executing the installation, it’s a good idea to run the script to check if we have all components that are necessary:

As you can see in the above output, the script failed on the unixODBC DriverManager installation. This issue can be solved by running the build_dm.sh script which is also supplied in the archive. The script is downloading and installing all what’s needed. Answer YES when prompted to agree with the disclaimer (completely MS style).

build_dm.sh is building the unixODBC DriverManager but doesn’t install it. To do so, we need to follow the instructions given in the end of the above output:

Now that the unixODBC DriverManager is built and installed, we can verify again if we have all necessary components to build the ODBC driver for MS SQL Server.

This looks already better and we can continue with the installation. As with the installation of the DriverManager, we’ll have to accept the terms with YES before we can continue.

That’s it for the installation, now we should be able to use the driver.

Using the Microsoft SQL Server ODBC driver for Linux

After the installation, we can start using the driver for whatever purpose we installed it. I’ll provide a few small examples in order to get you going but everything depends on the use case.

A first way to use sqlcmd is to just start it and do everything interactive. When starting sqlcmd, you need to provide the server/instance-name and the user/password that are required to connect to the SQL Server. This can be done with sqlcmd -S <server-hostame>/<instance> -U <username> -P <password>

Example of interactive execution:

Besides working interactive, we can also launch a query in batch: the command can be used like this: sqlcmd -Q “<sql-query>” -S <server-hostame>/<instance> -U <username> -P <password>

Example of a batch execution:

Use the driver with Kerberos / AD-authentication.

Next to providing a username and password to authenticate when executing a query, you can also authenticate using a Kerberos ticket. In a Windows-minded environment, there is a big chance that authentication is done based on Active Directory. We can use AD-authentication using Kerberos-tickets on our Linux environment. This way, you do not need to provide credentials to execute a query and in some cases, the only way to authenticate at the SQL Server is by using AD. When querying from a script or batch, this is also useful since you do not need to expose your password in the script.

First thing that we need to do is install the necessary packages required for Kerberos:

When Kerberos is installed, there should be a configuration file in /etc/krb5.conf. Edit it as follows:

In the above example, we assume that the domain name is jensd.local and the Active Directory server is adserver.jensd.local. Make sure that your hosts-file or DNS allows you machine to resolve adserver.jensd.local (easiest way to test is to ping it).

Now that the configuration is ok, we can obtain a kerberos ticket from the AD-server:

In the above example, the user jensd exists in the Active Directory and will be the user that is authenticated at the database-level in SQL Server (so that user needs sufficient access rights on the database that you want to query).

After obtaining a Kerberos ticket, we can check the status:

Now, we can query the database or use it interactive as we did before, without providing credentials.

To destroy your Kerberos ticket in case something went wrong or it’s no longer needed, you can simply execute kdestroy:

Hopefully this helps you to install the ODBC driver for Linux for Microsoft SQL Server and integrate with your AD-environment.

8 thoughts on “Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication

  1. This tutorial is amazing! Congrats! One question … Where sql backups are saved by default ?

  2. On my freshly installed El Capitan, I installed latest version of jdk (from Oracle) this evening without any problem. Not really sure what this article about!!

  3. sqlcmd: error while loading shared libraries: libcrypto.so.6: cannot open shared object file: No such file or directory

    Im getting above error..please help me!

    • Hi, I believe libcrypto.so.6 is part of openssl, so you will have to install that. Best way is to do a “yum provides */libcrypto.so.6” and it will give you what you need to install.

  4. i cant connect:
    SqlState HYT00, Login timeout expired
    A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
    TCP Provider: Error code 0x2726

  5. Pingback: MsSQL on Linux – Installation – —Blog By FuFu—

Leave a Reply

Your email address will not be published. Required fields are marked *