Securing access to SQL Server from Linux with Kerberos

How to access SQL Server as a Kerberos service from Linux.

Contents

Kerberos overview

The Kerberos network authentication protocol is used for securing access to network services. Kerberos is based on the use security tickets to manage the authentication of users and applications on a network.

A key requirement on which Kerberos was based was providing single sign-on, which allows users to access a variety of systems and services without needing to enter their user name and password repeatedly (or without needing to remember and enter different user names and passwords for the various systems and services they use).

The Kerberos architecture is designed around messages exchanged between three different entities:

Kerberos and SQL Server

SQL Server 2000, 2005, and 2008 support Kerberos indirectly through the Windows Security Support Provider Interface (SSPI) interface when using Windows authentication.

SQL Server allows SSPI to negotiate the authentication protocol to use; if Kerberos cannot be used, Windows will fall back to NT LAN Manager (NTLM) authentication.

Note that if you specify a SQL Server user name and password when connecting to SQL Server (SQL Server authentication), SSPI is not used, and therefore Kerberos cannot be used for authentication.

To access a SQL Server instance as a Kerberos service, a Service Principal Name (SPN) for the instance must be registered with Active Directory on a domain controller, which assumes the role of the KDC in a Windows domain.

Active Directory consists of both a database of network resources (such as users and computer) and a service that makes this information available to users and applications.

Assumptions

This article explains how to use the SQL Server ODBC driver to access a SQL Server instance as a Kerberos service from Linux. The article does not provide any instructions on how to configure SQL Server to run as a Kerberos service in a Windows environment. The article assumes that:

  1. An Service Principal Name (SPN) for the SQL Server instance has been registered with Active Directory on a Windows KDC.
  2. You have verified that you can access the SQL Server instance as a Kerberos service from a Windows machine.

    Because Windows may transparently fall back to an alternative authentication mechanism if Kerberos authentication fails, it is necessary to verify this by:

    1. In SQL Server Management Studio, connect to the SQL Server instance. Do this from a different machine to the Windows KDC.
    2. Do one of the following:
      • For SQL Server 2005 or later, run the following query: 
        SELECT auth_scheme FROM sys.dm_exec_connections
WHERE session_id = @@spid;

        which needs to return KERBEROS (rather than NTLM).

      • For SQL Server 2000, on the Windows KDC, run Event Viewer. Look for a Security log entry for the logon that corresponds with the SQL Server access. The authentication method should be KERBEROS (rather than NTLM).
  3. You know the SPN for the SQL Server instance. You need this to configure the SQL Server ODBC driver data source on your Linux machine.

Prerequisites

The prerequisite Linux software for this article is:

Accessing SQL Server as a Kerberos service from Linux

Kerberos configuration on Linux

You need to edit the Kerberos configuration file (/etc/krb5.conf) to enable Kerberos on the SQL Server ODBC driver machine to access the Windows KDC.

  1. As root, open /etc/krb5.conf in a text editor.
  2. Change the [libdefaults] section to include the following line: 
    default_realm = WINDOWSKDCDOMAIN

    where WINDOWSKDCDOMAIN is the KDC's domain (as displayed in My Computer > System Properties > Computer Name on the KDC machine) and is in upper case.

    The Kerberos application, kinit, that will be used to obtain a ticket-granting ticket (TGT) will automatically connect to the realm specified by default_realm.

  3. Find the [realms] section.

    The [realms] section tells Kerberos where to find the KDC for a particular realm.

  4. Add an entry for your KDC to the [realms] section: 
    WINDOWSKDCDOMAIN = {
        kdc = windowskdcmachine:88
        }

    where:

    • WINDOWSKDCDOMAIN is the KDC's domain (this will match the default_realm value).
    • windowskdcmachine is the fully qualified domain name (FQDN) or IP address of the KDC.

      The FQDN must be resolvable to an IP address on the SQL Server ODBC driver machine. Otherwise, you will get "Cannot resolve network address for KDC in requested realm while getting initial credentials" when attempting to access the KDC.

Clock synchronisation

To prevent the continued use of an expired ticket by resetting the system clock, Kerberos rejects ticket requests from any machine whose system time is significantly different to that of the KDC.

If the time on the SQL Server ODBC driver machine and the KDC differ significantly, you will get the following errors when attempting to access the KDC:

kinit(v5): Preauthentication failed while getting initial credentials

–Or–

kinit(v5): Clock skew too great while getting initial credentials

The Maximum tolerance for computer clock synchronization Kerberos policy setting on the KDC defines the maximum allowable time difference.

You can synchronise the system clocks by running ntpdate windowskdcmachine on the SQL Server ODBC driver machine.

Obtaining a Kerberos ticket from the Windows KDC

A ticket-granting ticket (TGT) from the Windows KDC must be present on the SQL Server ODBC driver machine. (The Windows KDC is the domain controller on which an SPN for the SQL Server instance was registered.) The SQL Server ODBC driver uses the TGT to obtain a service ticket, which enables the driver to access the SQL Server instance.

To obtain (or renew) a TGT, you can use the Kerberos application kinit. On the Ubuntu machine used when developing this article, kinit was included in the krb-user package.

kinit contacts the KDC to authenticate a user, and, if successful caches the returned TGT.

If you attempt to use the SQL Server ODBC driver to access SQL Server as a Kerberos service from a machine where a TGT is not present, the connection will fail with the error:

'KRB5_FCC_NOFILE: No credentials cache file found'

To add a TGT to the credentials cache on the SQL Server ODBC driver machine, follow these instructions:

  1. Log into the SQL Server ODBC driver machine.
  2. Do one of the following:

    Note that because a default Kerberos realm was specified in /etc/krb5.conf, it is not necessary to specify the realm in the kinit command.

  3. When prompted, enter the Windows password for this user.

    If kinit fails with the error:

    kinit(v5): KDC reply did not match expectations
while getting initial credentials

    check that the Windows KDC realm you specified in /etc/krb.conf (in the default_realm value) exactly matches the KDC's domain (as displayed in My Computer > System Properties > Computer Name) and is in upper case.

  4. Use klist to view the tickets in the credential cache (/tmp/krb5cc_uid_xxxx), which should now contain the TGT (identified by the string krbtgt) from the Windows KDC: 
    $ klist
Ticket cache: FILE:/tmp/krb5cc_1002_0s20Ab
Default principal: mywindowsuser@MYWINDOWSKDCDOMAIN

Valid starting     Expires            
02/02/11 15:27:24  02/03/11 01:27:33

Service principal
krbtgt/MYWINDOWSKDCDOMAIN@MYWINDOWSKDCDOMAIN

Running kinit automatically

It is possible for kinit to be run automatically when a user logs onto a Linux machine. To do this:

This process is described fully in the PAM Configuration section in the article Debian GNU: Setting up MIT Kerberos 5. Note in particular the warning about opening a terminal as root before editing the PAM configuration files.

For the scenario around which this article is based (Linux client, Windows KDC), when the user supplies a Windows password when logging on to the SQL Server ODBC driver machine, PAM will use kinit to authenticate the user. If authentication is successful, kinit will populate the credentials cache and PAM will allow the user to log in. Note that this process assumes that:

  1. The user's Linux and Windows user name are the same.
  2. The user's Linux and Windows password are different (local authentication must fail).

    The Debian GNU: Setting up MIT Kerberos 5 article recommends that the Linux password be set to *K*, which, by convention, indicates that the actual password is stored in Kerberos.

Configuring the SQL Server ODBC driver to use Kerberos authentication

Generic Security Services Application Programming Interface (GSS-API)

The SQL Server ODBC driver uses the Generic Security Services Application Programming Interface (GSS-API) to access Kerberos. The GSS-API provides a common interface that enables calling applications to access different security services, including Kerberos. The GSS-API does not provide the security services itself, and so the Kerberos runtime libraries need to be present on the machine where the GSS-API is installed.

If your client application is 64-bit, you need to use a 64-bit GSS-API library and Kerberos runtime (and a 64-bit SQL Server ODBC driver). Otherwise, you need a 32-bit GSS-API library and Kerberos runtime, even if your operating system is 64-bit.

The Kerberos GSS-API library

The SQL Server ODBC driver uses libgssapi_krb5.so, the Kerberos GSS-API library, to request service tickets for accessing SQL Server instances. If the SQL Server ODBC driver is unable to open this library, the connection will fail with the error:

Krb5: failed to open gss lib (libgssapi_krb5.so)

If the Kerberos GSS-API library is not called libgssapi_krb5.so in your GSS-API distribution, use the GSSLIB attribute in your data source to specify the alternative GSS-API library. For example:

GSSLIB = /opt/extension/lib/libgssapi.so

Configuring an SQL Server ODBC data source for Kerberos authentication

  1. Create an SQL Server ODBC driver data source in /etc/odbc.ini that connects to the SQL Server instance that has been registered as a Kerberos service. For example: 
    [mssql-kerberos-dsn]
Driver                  = Easysoft ODBC-SQL Server
Server                  = windowsmachine
ServerSPN               = spn
User                    = 
Password                =

    where:

    Note If you do not specify a ServerSpn value and include the line Kerberos = Yes in your data source, the SQL Server driver will create a default SPN with the following format:

    MSSQLSvc/server:port

    where server is the Server attribute value and port is the Port attibute value.

    Ensure that the User and Password attribute values are blank. kinit must have already been used for authentication before the driver can gain access to SQL Server as a Kerberos service. If a user and password are specified in the data source, the driver will attempt to authenticate the specified user by using NTLM or SQL Server authentication.

  2. Use isql to connect to the new data source. For example: 
    $ cd /usr/local/easysoft/unixODBC/bin
$ ./isql.sh -v mssql-kerberos-dsn
SQL> SELECT auth_scheme FROM sys.dm_exec_connections
     WHERE session_id = @@spid;
+-----------------------------------------+
| auth_scheme                             |
+-----------------------------------------+
| KERBEROS                                |
+-----------------------------------------+

Additional ODBC data source attributes for Kerberos authentication

The SQL Server driver supports addition data source attributes related to Kerberos authentication, which are described in this section.

  1. The SQL Server ODBC driver allows you to pass req_flags to the gss_init_sec_context() function, which is used to initiate a security context for the driver. The KDC uses this security context to verify the identity of the client. To pass req_flags to gss_init_sec_context(), use the GSSFLAG attribute: 
    GSSFLAG = req_flags

    where req_flags is a bitmask specifying the requested GSS services. To look up the available bitmask values, refer to the gssapi.h header file for the GSS-API distribution on your SQL Server ODBC driver machine. The driver default GSSFLAG value is 4, which sets the GSS_C_REPLAY_FLAG flag.

    As an example, to request credential delegation, set the GSS_C_DELEG_FLAG flag by including this line in your data source:

    GSSFLAG = 1
  2. To allow the use of GSS_C_NT_HOSTBASED_SERVICE as the target principal name type, add this line to your data source: 
    GSSHOST = 1

    By default, the SQL Server ODBC driver uses GSS_C_NT_USER_NAME.

Kerberos and linked servers

A linked server enables a query to be executed on one SQL Server instance, which can be fully or partially redirected and processed on another SQL Server instance; the results of the query are sent back to the original SQL Server instance and returned to the client machine.

During a linked server connection, the linked server needs the credentials of the user that were used to authenticate the connection to the original SQL Server instance. There are two ways to achieve this:

To use credential delegation, you need to use Kerberos authentication. You cannot use NTLM authentication, because NTLM does not permit multiple "hops" where credentials are passed multiple times i.e. from the client machine to the SQL Server machine (hop 1) and the SQL Server machine to the linked server machine (hop 2).

You need to configure SQL Server ODBC driver to request credential delegation by setting the GSS_C_DELEG_FLAG flag. To do this, add 1 to the value of the SQL Server ODBC driver attribute GSSFLAG. The driver default value for GSSFLAG is 4, so to preserve the default value and request credential delegation, add this line to your SQL Server ODBC driver data source:

GSSFLAG = 5

If you do not configure the SQL Server ODBC driver to request credential delegation, querying a linked server will fail. For example:

SQL> select * from MY_LINKED_SERVER.master.dbo.sysdatabases
[28000][Easysoft][SQL Server Driver 10.0][SQL Server]
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The query fails because SQL Server and Windows cannot forward the Windows credentials of the connected user to the linked server machine. (You would also get this error the if initial connection used NTLM rather than Kerberos authentication, for the same reason.)

You also need to configure your SQL Server instance and linked server for delegation.

Kerberos and database mirroring

Database mirroring is a feature introduced in SQL Server 2005 that increases data availability by creating a standby copy of a database. In database mirroring, all updates to a database (the principal database) are automatically copied to a standby database (the mirror database). If the principal server fails, the mirror server takes over the role of principal server and brings its copy of the database online as the principal database.

To enable the SQL Server ODBC driver to establish the initial connection to a mirrored database, a data source needs to supply the current principal server instance. Optionally, the data source can also supply the current mirror server instance. This setting is used to connect to the mirror server if the initial connection to the principal server fails. The SQL Server ODBC driver will not attempt to failover to the mirror server if this setting is missing from the data source.

If you want to use Kerberos to authenticate the connection to the principal server instance, an SPN for that instance must be registered with Active Directory on a Windows KDC. In your data source, you need to specify both the instance and the SPN by using the Server and ServerSPN attributes. For example:

Server = machine_a\my_instance
ServerSPN = MSSQLSvc/machine_a:my_instance

If you want to use Kerberos to authenticate the connection to the mirror server instance, an SPN for that instance must be registered with Active Directory on a Windows KDC. In your data source, you need to specify both the instance and the SPN by using the Failover_Partner and FailoverServerSPN attributes. For example:

Failover_Partner = machine_b\my_instance
FailoverServerSPN = MSSQLSvc/machine_b:my_instance

In addition, the data source must also supply the name of the mirrored database. For example:

Database = AdventureWorks

Resources