Site navigation
Page navigation
How to access SQL Server as a Kerberos service from Linux.
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:
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.
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:
Because Windows may transparently fall back to an alternative authentication mechanism if Kerberos authentication fails, it is necessary to verify this by:
SELECT
auth_scheme
FROM
sys.dm_exec_connections
WHERE
session_id = @@spid;which needs to return KERBEROS (rather than NTLM).
KERBEROS (rather than NTLM).The prerequisite Linux software for this article is:
For installation instructions, refer to the SQL Server ODBC driver documentation. Refer to the documentation to see which environment variables you need to set.
kinit.
On the Ubuntu machine used when developing this article, kinit was included in the krb-user package.
On the Ubuntu machine used when developing this article, the GSS-API libraries were included in the libgssapi2 package.
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.
/etc/krb5.conf in a text editor.[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.
[realms] section.
The [realms] section tells Kerberos where to find the KDC for a particular realm.
[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.
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.
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:
kinit
kinit principal
where principal is the Windows user name.
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.
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.
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
kinit automaticallyIt is possible for kinit to be run automatically when a user logs onto a Linux machine. To do this:
kinit is used) is successful.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:
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.
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 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
/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:
windowsmachine is the name or IP address of the machine on which the SQL Server instance is running.spn is the SPN for the SQL Server instance.
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.
$ 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 | +-----------------------------------------+
The SQL Server driver supports addition data source attributes related to Kerberos authentication, which are described in this section.
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
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.
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:
–Or–
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.
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