SQL/R A.04.00 Release Notes

SQL/R A.04.00 LDAP support

SQLR supports authenticating users against an LDAP server. The Microsoft Active Directory server is accessible through the LDAP protocol and may be used as well.

When enabled, SQLR binds to the LDAP server upon a new connection using the credentials passed by the driver. The access groups (optionally) specified for the DSN are verified against LDAP. In addition, an LDAP search expression is supported to allow specifying additional requirements.

The Security = ldap config item enables using LDAP to authorize connections. The LdapURI config item specifies the network address and protocol to access the LDAP server. The LdapBindDn configuration item is used to translate the user id into the LDAP name space.
When LdapBase is configured any access groups specified for the DSN are also verified against LDAP. The LdapUserMatch and LdapGroupAttr config items are used to map the LDAP specifics.

The LDAP filter configuration allows to specify additional LDAP filter conditions to authorize a connection. A connection is authorized if a non-empty result is returned by the LDAP server.

Configuration directives

The LDAP configuration directives may be specified globally in the odbc.cfg (as a default) and also specified per DSN which then takes precedence.

The following config items may be specified:

Security = ldap

Setting "Security = ldap" enables the LDAP lookup.

LdapURI

This specifies the uri of the LDAP server.

Syntax: {ldap|ldaps}://server[:port] [server[:port] ...]

When the ldaps protocol is specified, an encrypted connection is used. Server specifies the name or IP address (and optional port number) of the LDAP server. The default port number is defined by the protocol. Multiple servers may be specified, separated by spaces.

For example:

LdapURI = ldap://ldap.marxmeier.com

Please refer to the HP-UX or Windows platform specifics for prerequisites to use an encrypted connection.

LdapCertsDir (HP-UX)

This is used on HP-UX to specify the directory holding the certificate database cert8.db and key3.db. These are required for an encrypted LDAP connection. The default is /etc/opt/sqlr/4.0/certs

For example:

LdapCertsDir = /etc/opt/sqlr/4.0/certs

Please refer to the HP-UX platform specifics for more information. This config item has no effect on other platforms.

LdapTimeout

Numeric value, specifying the timeout (in ms) contacting the LDAP server. A zero value specifies the platform defaults. The default is zero.

LdapBindDn

Specifies how a user name is transformed into an LDAP binddn. The %U token is replaced with the user name. The default is %U so that the binddn is equivalent to the user name.

For example:

LdapBindDn = cn=%U,cn=Users,dc=mse,dc=marxmeier,dc=com

This specifies the template to transform the user id to the LDAP dn. A user id "test" is looked up as cn=test,cn=users,dc=mse,dc=marxmeier,dc=com.

To authenticate against a Windows Active Directory server, the user name must be qualified with the Active Directory domain. In addition to the generic LDAP syntax, the Windows Active Directory server also supports specifying the domain name separated with an "@" character.

For example:

LdapBindDn = %U@mse.maxmeier.com

Where "mse.marxmeier.com" in the example is the Active Directory domain. As an alternative, the Active directory server also supports using an "NT" style domain, where the domain name is separated by a backslash (\) character.

For example:

LdapBindDn = MSE\%U

LdapBase

Specifies an optional search base. This defines the starting point in the LDAP name space to lookup additional information, such as user properties or groups.

Note: If LdapBase is not specified, a successful LDAP bind is sufficient to authenticate the user. Specifying LdapBase is required if the LDAP group membership should be checked against a group access list.

For example:

LdapBase = cn=users,dc=marxmeier,dc=com

LdapUserMatch

Specifies how a user name is matched if a search base is configured. The %U token is replaced with the user name. The default is cn=%U so that the common name must match the user name. An LdapFilter may be configured to further narrow the LDAP search result.

LdapGroupAttr

Specifies the attribute which is used to obtain the LDAP group membership to be checked against a group access list. By default the "memberOf" attribute is used.

LdapFilter

Specifies an additional condition to be verified by the LDAP server. This is optional and requires that a search base is configured. It is combined with LdapUserMatch.

For example:

LdapFilter = (memberOf=cn=ODBC,cn=users,dc=marxmeier,dc=com)

This example verifies if the user is a member of the specified group. The filter needs to follow the LDAP conventions and can query arbitrary LDAP attributes. Internally, the LdapFilter search is combined with a search for the user (LdapUserMatch).

Using LDAP with a Windows Active Directory server

The Windows Active Directory server supports the LDAP protocol and may be be used to authorize ODBC connections.

The example below defines a DSN with the database sample and uses an encrypted LDAP connection to authorize users. The user must be a member of the group ODBC to achieve write access. The database is opened as the user "ODBC" and requires an encrypted connection.

[LdapSampleDSN]
Security = ldap
LdapURI = ldaps://ldap.marxmeier.com
LdapBindDn = %U@mse.marxmeier.com
LdapBase = cn=Users,dc=mse,dc=marxmeier,dc=com
LdapTimeout = 1000
Database = sample
Login = ODBC
EnableWrite = full
Encryption = 1
GroupWriteList = ODBC

The LdapURI specifies the protocol and DNS name of the Active Directory server. When using an encrypted connection (ldaps) the DNS name must be specified and match the server certificate. The LdapBindDn should specify the Active Directory domain. LdapUserMatch und LdapGroupAttr need not be configured as the default is appropriate for accessing an Active Directory server.

Please note: The LdapBindDn must be configured to specify the Windows domain. Otherwise an account can only authenticate with just a user name in a simple bind if the DisplayName is the same as the user name and the user name is globally unique.

Using LDAP with a Linux or HP-UX LDAP server

The example below defines a DSN with the database sample and uses an LDAP connection to authorize users. The user must be a member of the group ODBC to achieve write access. The database is opened as the user "ODBC" and requires an encrypted connection.

[LdapSampleDSN]
Security = ldap
LdapURI = ldap://ldap.marxmeier.com:3389
LdapBindDn = uid=%U,ou=people,dc=marxmeier,dc=com
LdapBase = ou=group,dc=marxmeier,dc=com
LdapUserMatch = member=uid=%U,ou=people,dc=marxmeier,dc=com
LdapGroupAttr = dn
LdapTimeout = 1000
Database = sample
Login = ODBC
EnableWrite = full
Encryption = 1
GroupWriteList = ODBC

As the groups are located in a separate tree, the LdapBase must refer to the groups and LdapUserMatch must be configured so that the user name can be compared. LdapGroupAttr is set to "dn" as the group names are returned in the distinguished name field of the search results.

Platform dependencies

HP-UX

The HP-UX LDAP client is used to access the LDAP server. The HP-UX LdapUxClient product must be installed. This is part of the "J4269AA: LDAP-UX Client Services" bundle from the HP-UX application DVD or already installed as part of the HP-UX OE. SQLR was tested with LdapUxClient version B.04.15.

LDAP-UX Integration is also available from the HPE Software Center web site → https://myenterpriselicense.hpe.com/cwp-ui/free-software/LDAP-UX.

To use an encrypted LDAP connection (ldaps protocol) a certificate database must be present (cert8.db and key3.db). Otherwise initializing the SSL connection will fail. A certificate database may be copied from a Mozilla Firefox installation or created using the certutil tool like in the example below:

# cd /etc/opt/sqlr/4.0/certs
# /opt/ldapux/contrib/bin/certutil -d . -N
# chown sqlr cert8.db key3.db secmod.db

To use LDAP the server process dynamically loads a libldap.sl (PA-RISC) or libldap.so (Itanium) library. The SQL/R specific lib directory has precedence in locating the library. The SHLIB_PATH and LD_LIBRARY_PATH environment variables may be used to select a specific library to be loaded. Otherwise the system default is used.

Related documentation
LDAP-UX Client Services B.04.15 Administrator's Guide: HP-UX 11i v1, v2 and v3, J4269-90090 → https://support.hpe.com/hpesc/public/docDisplay?docId=c02056393

LDAP-UX Client Services B.04.15 with Microsoft Windows Active Directory Administrator's Guide: HP-UX 11i v1, v2, and v3, J4269-90084 → https://support.hpe.com/hpesc/public/docDisplay?docId=c02056400

Using the Certificate Database Tool (mozilla) → https://firefox-source-docs.mozilla.org/security/nss/legacy/tools/nss_tools_certutil/index.html

Linux

The OpenLDAP client is used to access the LDAP server. The openldap2-client package must be installed.

To use LDAP the server process dynamically loads a libldap.so library. The SQL/R specific lib directory has precedence in locating the library. The LD_LIBRARY_PATH environment variable may be used to select a specific library to be loaded. Otherwise the system default is used.

Windows

The builtin winldap functionality is used to access the LDAP server. To use an encrypted LDAP connection (ldaps protocol) on the Windows platform the server certificate needs to be imported and the host name used for connecting needs to match the certificate subject. Otherwise an encrypted connection is not possible.