The Microsoft SQL Server ODBC Driver for Linux supports connections using Kerberos integrated authentication. SQL Server ODBC Driver for Linux supports MIT Kerberos Key Distribution Center (KDC) on RedHat Linux version 5 and 6, and works with Generic Security Services Application Program Interface (GSSAPI) and Kerberos libraries on Red Hat Enterprise versions 5 and 6.
Using Integrated Authentication to Connect to SQL Server from an ODBC Application
You can enable Kerberos Windows integrated authentication by specifying Trusted_Connection=yes in the connection string of SQLDriverConnect or SQLConnect. For example:
![]() | |
---|---|
Driver='SQL Server Native Client 11.0';Server=your_server;Trusted_Connection=yes |
You can also add Trusted_Connection=yes in the DSN entry of the ODBC.ini.
You can also use the -E option in sqlcmd; see Connecting with sqlcmd for more information.
Ensure that the Linux principal server that is going to connect to SQL Server is already authenticated with the Kerberos KDC.
ServerSPN and FailoverPartnerSPN are not supported.
Deploying an Application Designed to Run as a Service
A system administrator can deploy an application that uses Linux ODBC to run as a service using Kerberos Authentication when connecting to SQL Server.
You first need to configure Kerberos on the Linux computer and then ensure that the application can use the Kerberos credential of the default principal.
You must ensure that kinit or PAM (Pluggable Authentication Module) was used to obtain and cache the TGT for the principal that will be used for the connection:
Run kinit, passing in a principal name and password.
Run kinit, passing in a principal name and a location of a keytab file containing the principal’s key, previously created by ktutil.
Ensure that the log in to the system was done using PAM (Pluggable Authentication Module).
Because an application runs as a service and Kerberos credentials, by design, expire, it may be necessary to renew the credentials to ensure continued availability of the service. The Linux ODBC driver does not provide the renewal of the credentials. You need to ensure that there is a cron job or script that periodically runs to renew the credentials before the expiration. To avoid requiring the password for each renewal in this case you can use the keytab file, created earlier.
Kerberos Configuration and Use provides details on additional ways to Kerberize services on Linux.
Tracking Access to a Database
A database administrator can create an audit trail of access to a database when using system accounts to access SQL Server using Integrated Authentication.
Logging in to SQL Server uses the system account and there is no functionality on Linux to impersonate security context. Therefore, additional steps are required to determine the user.
In order to be able to audit activities in SQL Server on behalf of users other than system account, the application will need to use the Transact-SQL EXECUTE AS.
To improve application performance, an application can use connection pooling with Integrated Authentication and auditing. However, combining connection pooling, Integrated Authentication, and auditing creates a security risk because the Unix ODBC Driver manager does not prevent pooled connections from being reused by different users. For more information, see ODBC Connection Pooling.
Before reuse, an application must reset pooled connections by executing sp_reset_connection.
Using Active Directory to Manage User Identities
An application system administrator may not want to manage separate sets of login credentials for SQL Server. It is possible to configure Active Directory as a key distribution center (KDC) for Integrated Authentication.
Using Linked Server and Distributed Queries
A developer can deploy an application that uses linked server or distributed queries without a database administrator maintaining separate sets of SQL credentials by configuring the application to use integrated authentication in one of the following ways:
User logs in to a client machine and authenticates to the application server.
The application server authenticates as a different database and connects to SQL Server.
SQL Server authenticates as a database user to another database (SQL Server.
After integrated authentication is configured, credentials will be passed to the linked server.
Integrated Authentication and sqlcmd
Use the -E sqlcmd option to access a SQL Server database using integrated authentication. Ensure that the account under which sqlcmd will run is associated with the default Kerberos principal server.
Integrated Authentication and bcp
Use the -T bcp option to access a SQL Server database using integrated authentication. Ensure that the account under which the bcp script will run is associated with the default Kerberos principal server.
It is an error to use -T with the -U or -P option.
Supported Syntax for SPN’s Registered by SQL Server
The syntax that SPNs use in connection string or connection attributes is as follows:
Syntax | Description |
---|---|
MSSQLSvc/fqdn:port | The provider-generated, default SPN when TCP is used. port is a TCP port number. fqdn is a fully-qualified domain name. |
Authenticating a Linux Computer with Active Directory
(For more information about authenticating your Linux computer with Active Directory, see Authenticate Linux Clients with Active Directory.)
Enter data into the krb5.conf file. krb5.conf is located at /etc/krb5.conf but you can refer to another file (export KRB5_CONFIG=/home/dbapp/etc/krb5.conf). The following is an example krb5.conf file:
![]() | |
---|---|
[libdefaults] default_realm = YYYY.CORP.CONTOSO.COM dns_lookup_realm = false dns_lookup_kdc = true ticket_lifetime = 24h forwardable = yes [domain_realm] .yyyy.corp.contoso.com = YYYY.CORP.CONTOSO.COM .zzzz.corp.contoso.com = ZZZZ.CORP.CONTOSO.COM |
If your Linux computer uses Dynamic Host Configuration Protocol (DHCP) and the Windows DHCP server provides the DNS servers to use, you can use dns_lookup_kdc=true. Now, you can sign in to your domain using Kerberos, as follows: kinit alias@YYYY.CORP.CONTOSO.COM. The parameter passed to kinit is case sensitive and the SQL Server computer configured to be in the domain must have that user alias@YYYY.CORP.CONTOSO.COM added for login. The SQL Server ODBC Driver for Linux does not support generating Kerberos credentials via a keytab file. Now, you can use trusted connections (Trusted_Connection=YES in a connection string or sqlcmd -E).
The time on the Linux computer and the time on the Kerberos Domain Controller (KDC) must be close. Ensure that your system time is set correctly and equivalent to the Network Time Protocol (NTP).
If Kerberos authentication fails, there will be no attempt by the SQL Server ODBC Driver for Linux to use NTLM authentication.