http://jensd.be/?p=255
Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication
Posted on 02/10/2014
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:
1
2
3
[jensd@cen ~]$ sudo yum install gcc unixODBC make wget
...
Complete!
Next, download the archive containing the software from Microsoft. You can find it here:
http://www.microsoft.com/en-us/download ... x?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:
1
2
3
4
[jensd@cen ~]$ tar -xzf sqlncli-11.0.1790.0.tar.gz
[jensd@cen ~]$ rm sqlncli-11.0.1790.0.tar.gz
[jensd@cen ~]$ cd sqlncli-11.0.1790.0/
[jensd@cen sqlncli-11.0.1790.0]$
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[jensd@cen sqlncli-11.0.1790.0]$ ./install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ......................... FAILED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
See /tmp/sqlncli.10465.24827.11611/install.log for more information about installation failures.
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).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[jensd@cen sqlncli-11.0.1790.0]$ ./build_dm.sh
Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.
In order to use the Microsoft SQL Server ODBC Driver V1.0 for Linux,
the unixODBC DriverManager must be installed on your computer. unixODBC
DriverManager is a third-party tool made available by the unixODBC Project.
To assist you in the installation process, this script will attempt to
download, properly configure, and build the unixODBC DriverManager from
http://www.unixodbc.org/ for use with
Microsoft SQL Server ODBC Driver V1.0 for Linux.
Alternatively, you can choose to download and configure unixODBC
DriverManager from
http://www.unixodbc.org/ yourself.
Note: unixODBC DriverManager is licensed to you under the terms of an
agreement between you and the unixODBC Project, not Microsoft. Microsoft
does not guarantee the unixODBC DriverManager or grant any rights to
you. Prior to downloading, you should review the license for unixODBC
DriverManager at
http://www.unixodbc.org/.
The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind. Microsoft is not liable for any issues
arising out of your use of the script.
Enter 'YES' to have this script continue: YES
Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager ................................... OK
Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
Configuring unixODBC 2.3.0 DriverManager ................................... OK
Building unixODBC 2.3.0 DriverManager ...................................... OK
Build of the unixODBC 2.3.0 DriverManager complete.
Run the command 'cd /tmp/unixODBC.8680.15148.29730/unixODBC-2.3.0; make install' to install the driver manager.
PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.
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:
1
2
3
4
5
6
[jensd@cen sqlncli-11.0.1790.0]$ cd /tmp/unixODBC.8680.15148.29730/unixODBC-2.3.0
[jensd@cen unixODBC-2.3.0]$ sudo make install
Making install in extras
...
make[2]: Leaving directory `/tmp/unixODBC.8680.15148.29730/unixODBC-2.3.0'
make[1]: Leaving directory `/tmp/unixODBC.8680.15148.29730/unixODBC-2.3.0'
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[jensd@cen sqlncli-11.0.1790.0]$ ./install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
Install log created at /tmp/sqlncli.25110.7167.14938/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[jensd@cen sqlncli-11.0.1790.0]$ sudo ./install.sh install
[sudo] password for jensd:
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
MICROSOFT SOFTWARE LICENSE TERMS
...
Enter YES to accept the license or anything else to terminate the installation: YES
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
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
Install log created at /tmp/sqlncli.2953.1731.30390/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
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:
1
2
3
4
5
6
7
[jensd@cen ~]$ sqlcmd -S sql-host -U jensd -P secretpassword
1> SELECT COUNT(*) FROM books;
2> GO
-----------
9246
(1 rows affected)
1>
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:
1
2
3
4
[jensd@cen ~]$ sqlcmd -Q "SELECT COUNT(*) FROM books;" -S sql-host -U jensd -P secretpassword
-----------
9246
(1 rows affected)
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:
1
2
3
[jensd@cen ~]$ sudo yum install krb5-workstation
...
Complete!
When Kerberos is installed, there should be a configuration file in /etc/krb5.conf. Edit it as follows:
1
2
3
4
5
6
7
8
9
10
11
[libdefaults]
default_realm = JENSD.LOCAL
[realms]
TVH.LOCAL = {
kdc = adserver.jensd.local
}
[domain_realm]
.jensd.local = JENSD.LOCAL
jensd.local = JENSD.LOCAL
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:
1
2
[jensd@cen ~]$ kinit
jensd@JENSD.LOCAL
Password for
jensd@JENSD.LOCAL:
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:
1
2
3
4
5
6
7
[jensd@cen ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_1001
Default principal:
jensd@JENSD.LOCAL
Valid starting Expires Service principal
10/02/2014 14:44:45 10/03/2014 00:44:45 krbtgt/
JENSD.LOCAL@JENSD.LOCAL
renew until 10/03/2014 14:44:42
Now, we can query the database or use it interactive as we did before, without providing credentials.
1
2
3
4
5
6
[jensd@cen ~]$ sqlcmd -Q "SELECT COUNT(*) FROM books;" -S sql-host
-----------
9246
(1 rows affected)
[jensd@cen ~]$ sqlcmd -S sql-host
1>
To destroy your Kerberos ticket in case something went wrong or it’s no longer needed, you can simply execute kdestroy:
1
2
3
4
5
6
[jensd@cen ~]$ kdestroy
[jensd@cen ~]$ klist
klist: No credentials cache found (ticket cache FILE:/tmp/krb5cc_1001)
[jensd@cen ~]$ sqlcmd -Q "SELECT COUNT(*) FROM books;" -S 172.16.100.129
Cannot generate SSPI context
SSPI Provider: No Kerberos credentials available
Hopefully this helps you to install the ODBC driver for Linux for Microsoft SQL Server and integrate with your AD-environment.