Export (0) Print
Expand All

Using Integrated Authentication

SQL Server 2012

The Microsoft ODBC Driver for SQL Server on Linux supports connections that use Kerberos integrated authentication. The ODBC driver on Linux supports MIT Kerberos Key Distribution Center (KDC), and works with Generic Security Services Application Program Interface (GSSAPI) and Kerberos libraries.

You can enable Kerberos Windows integrated authentication by specifying Trusted_Connection=yes in the connection string of SQLDriverConnect or SQLConnect. For example:

Driver='ODBC Driver 11 for SQL Server';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.

A system administrator can deploy an application to run as a service that uses Kerberos Authentication to connect 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.

Ensure that you use kinit or PAM (Pluggable Authentication Module) to obtain and cache the TGT for the principal that the connection uses:

  • Run kinit, passing in a principal name and password.

  • Run kinit, passing in a principal name and a location of a keytab file that contains the principal’s key, that ktutil created.

  • Ensure that the login to the system was done using PAM (Pluggable Authentication Module).

Because an application runs as a service and Kerberos credentials, by design, expire, renew the credentials to ensure continued service availability. The ODBC driver on Linux does not provide the renewal of the credentials. 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 ways to Kerberize services on Linux.

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, more is required to determine the user.

To audit activities in SQL Server on behalf of users other than system account, the application must use 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 permits different users to reuse pooled connections. For more information, see ODBC Connection Pooling.

Before reuse, an application must reset pooled connections by executing sp_reset_connection.

An application system administrator does not have 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.

Developers can deploy an application that uses a linked server or distributed queries without a database administrator who maintains separate sets of SQL credentials. In that situation, a developer must configure an application to use integrated authentication:

  • 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.

To access SQL Server using integrated authentication, use the -E sqlcmd option. Ensure that the account that runs sqlcmd is associated with the default Kerberos principal server.

To access SQL Server using integrated authentication, use the -T bcp option. Ensure that the account that runs bcp is associated with the default Kerberos principal server.

It is an error to use -T with the -U or -P option.

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.

(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 in /etc/ 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 use Kerberos to sign in to your domain, as follows: kinit alias@YYYY.CORP.CONTOSO.COM. Parameters passed to kinit are 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 ODBC driver for SQL Server on 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, the ODBC driver on Linux does not use NTLM authentication.

Show:
© 2014 Microsoft