How to configure EUS + SSL authentication with OUD

During an EUS authentication, there are 2 communication channels: one between the sql client and the database, and another one between the database and the LDAP server. In a previous post, I explained that the database-to-OUD communication can be authenticated either through user/password or SSL.

The sql client-to-database connection also supports multiple authentication methods:

• user/password
• SSL
• Kerberos (described in this post)

In this post, I will explain how to configure SSL authentication. Note that SSL authentication is usually used in conjunction with DB-to-OUD SSL authentication. This post assumes that DB-to-OUD SSL authentication has already been set up as described there and that the database already has a wallet containing the DB certificate.

Using sqlplus, it is possible to connect to the DB using a client certificate instead of username/password. In order to do this, the DB must be configured with an SSL listener, and the sqlplus client must be configured to use a client certificate.

1. Configure the database to allow SSL authentication:
   The file sqlnet.ora located in $ORACLE_HOME/network/admin must be modified to look like the following:

   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
     
   ADR_BASE = <$ORACLE_BASE>
   
   #ADDED FOR SSL 
   SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ)
   SSL_CLIENT_AUTHENTICATION = TRUE
   WALLET_LOCATION = 
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY = <$ORACLE_BASE>/admin/<$ORACLE_SID>/wallet)
       )
     )

2. Configure a SSL listener on the database:
   The listeners are defined in the configuration file listener.ora, located in $ORACLE_HOME/network/admin. The content of this file defines the port and protocol used by the listener, and the location of the wallet that will store the database certificate.

   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
   #ADDED FOR SSL
         (ADDRESS = (PROTOCOL = TCPS)(HOST = <hostname>)(PORT = 1575))
       )
     )
   
   ADR_BASE_LISTENER = <$ORACLE_BASE>
   
   #ADDED FOR SSL
   WALLET_LOCATION = (SOURCE=
     (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY=<$ORACLE_BASE>/admin/<$ORACLE_SID>/wallet)
       )
     )
   

3. Restart the listener:
   the modification of listeners.ora requires to restart the database listener with the following commands

   $ $ORACLE_HOME/bin/lsnrctl stop
   $ $ORACLE_HOME/bin/lsnrctl start

4. Configure the sql client to use SSL authentication:

   The sql client reads its configuration from sqlnet.ora and tnsnames.ora. By default, those files are located in the same location as the DB configuration files ($ORACLE_HOME/network/admin), which can cause issues because the client and the server use a different wallet. In order to avoid configuration issues, it is recommended to have separate configuration files. To do this, the environment variable TNS_ADMIN is used to configure the path containing the client configuration files:

   $ setenv TNS_ADMIN /path/to/files
   

   or

   $ export TNS_ADMIN=/path/to/files
   

   Then the file tnsnames.ora will define how to reach the database using SSL:

   $ cat /path/to/files/tnsnames.ora 
   ORCL11G =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = <hostname>)(PORT = 1575))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl11g)
       )
     )
   

   And finally the file sqlnet.ora defines the SSL configuration (wallet location…):

   $ cat sqlnet.ora 
   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
   SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ)
   SSL_CLIENT_AUTHENTICATION = TRUE 
   WALLET_LOCATION =
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY = /path/to/client/wallet)
       )
     )

5. Create the client wallet and the client certificate:
   The client wallet will store the client certificate as well as the certificates trusted by the client (i.e. the server certificate).
   The client certificate must contain a DN that corresponds to a LDAP entry on OUD server. The following commands create a client wallet, then add a self-signed certificate to the wallet for the user cn=Joe, and finally export the self-signed certificate to a file.

   $ orapki wallet create -wallet /path/to/client/wallet -pwd <password> -auto_login
   

   $ orapki wallet add -wallet /path/to/client/wallet -dn cn=Joe,ou=users,dc=example,dc=com -keysize 1024 -self_signed -validity 365 -pwd <password>
   

   $ orapki wallet export -wallet /path/to/client/wallet -dn cn=Joe,ou=users,dc=example,dc=com -cert joe-cert.txt
   

6. Add the client certificate to the DB wallet:
   the database must trust the client certificate in order to accept SSL connections. As the above steps created a self-signed user certificate, the client certificate has to be imported as a trusted cert in the DB wallet.

   $ orapki wallet add -wallet <PathToDBWallet> -cert joe-cert.txt -trusted_cert -pwd <password>
   

7. Add the DB certificate to the client wallet:
   the client must trust the DB certificate. If the DB certificate is a self-signed certificate, this means that the DB cert must be added to the client wallet.

   $ orapki wallet add -wallet /path/to/client/wallet -cert db-cert.txt -trusted_cert -pwd <password>

8. Allow EUS to accept SSL connections between the client and the database:

    log in to Enterprise Manager: https://<hostname>:1158/em

   In the “Server” tab, select “Enterprise User Security” in the “Security” section, then “Manage Enterprise Domain” and click on “Configure”. In the “Configuration” tab, select the authentication methods that you want to allow between the client and the DB: Password, SSL, Kerberos.

9. Test the SSL connection between the client and the database:

   The following command reads the sql configuration files, extracts the path to the client wallet, then connects to the DB using the certificate found in the client wallet:

   $ export TNS_ADMIN=/path/to/files
   $ sqlplus /@orcl11g
   
   SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 28 11:18:57 2012
   
   Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SQL>

Once authenticated with the client certificate, the enterprise user will be mapped to a schema exactly as a user/password authenticated user. EUS will also find the groups for the LDAP user and grant the corresponding roles to the EUS user.

Advertisement

How to configure EUS + Kerberos authentication with OUD

During an EUS authentication, there are 2 communication channels: one between the sql client and the database, and another one between the database and the LDAP server. In a previous post, I explained that the database-to-OUD communication can be authenticated either through user/password or SSL.

The sql client-to-database connection also supports multiple authentication methods:

• user/password
• SSL
• Kerberos

In this post, I will explain how to configure Kerberos authentication. I am assuming that you already have a Kerberos server up and running.

 

1. Create a Kerberos Principal for the Database:
   The database service needs to have a corresponding Kerberos principal in the Kerberos server. If your Kerberos server is running on a Linux machine, this can be done with the kadmin utility. In my example, the kerberos administrator is kws/admin and creates a principal for the database having ORACLE_SID=orcl11g, running on host dbhost, with a Kerberos realm EXAMPLE.COM:

   $ kadmin -p kws/admin
   Authenticating as principal kws/admin with password.
   Password for kws/admin@EXAMPLE.COM: 
   kadmin:  add_principal -randkey orcl11g/dbhost
   WARNING: no policy specified for orcl11g/dbhost@EXAMPLE.COM; defaulting to no policy
   Principal "orcl11g/dbhost@EXAMPLE.COM" created.
   kadmin: ktadd -k /tmp/orcl11g.keytab orcl11g/dbhost@EXAMPLE.COM
   Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type AES-256 CTS mode with 96-bit SHA-1 HMAC added to keytab WRFILE:/tmp/orcl11g.keytab.
   Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type ArcFour with HMAC/md5 added to keytab WRFILE:/tmp/orcl11g.keytab.
   Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type Triple DES cbc mode with HMAC/sha1 added to keytab WRFILE:/tmp/orcl11g.keytab.
   Entry for principal orcl11g/dbhost@EXAMPLE.COM with kvno 2, encryption type DES cbc mode with CRC-32 added to keytab WRFILE:/tmp/orcl11g.keytab.
   kadmin:  exit

   The add_principal command creates a principal, and the ktadd command creates and stores the secret key in the file /tmp/orcl11g.keytab.

2. Copy the keytab to the database host:

   $ scp /tmp/orcl11g.keytab oracle@dbhost:/path/to/orcl11g.keytab

3. Create a Kerberos principal for the EUS user:
   The user that will use sqlplus also needs to be provisioned in the KDC. In the example, the user principal name is eususer@EXAMPLE.COM:

   $ kadmin -p kws/admin
   Authenticating as principal kws/admin with password.
   Password for kws/admin@EXAMPLE.COM: 
   kadmin:  add_principal eususer
   WARNING: no policy specified for eususer@EXAMPLE.COM; defaulting to no policy
   Enter password for principal "eususer@EXAMPLE.COM": 
   Re-enter password for principal "eususer@EXAMPLE.COM": 
   Principal "eususer@EXAMPLE.COM" created.
   kadmin:  exit
   $

4. Configure the database to allow Kerberos authentication:
   By default the database supports only user/password authentication. In order to allow Kerberos authentication, the file sqlnet.ora (located in $ORACLE_HOME/network/admin) needs to be modified to look like the following:

   $ cat sqlnet.ora
   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
   ADR_BASE = /export/home/oracle/app/oracle
   
   #ADDED FOR KERBEROS
   SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)
   SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orcl11g
   SQLNET.KERBEROS5_CONF=/etc/krb5.conf
   SQLNET.KERBEROS5_KEYTAB=/path/to/orcl11g.keytab
   SQLNET.KERBEROS5_CONF_MIT=true
   
   

5. Configure the database host as a Kerberos client:
   Edit /etc/krb5.conf and set the values for the Kerberos server in kdc and admin_server properties. In my example, the Kerberos server is running on kdc_host.fr.oracle.com:

   [...]
   [libdefaults]
    default_realm = EXAMPLE.COM
    dns_lookup_realm = false
    dns_lookup_kdc = false
    ticket_lifetime = 24h
    forwardable = yes
   [realms]
    EXAMPLE.COM = {
     kdc = kdc_host.fr.oracle.com
     admin_server = kdc_host.fr.oracle.com
    }
   [domain_realm]
    .example.com = EXAMPLE.COM
    example.com = EXAMPLE.COM
    .fr.oracle.com = EXAMPLE.COM
    fr.oracle.com = EXAMPLE.COM
   [...]

   You can test that the database host is properly configured by trying to get a Kerberos ticket-granting ticket for the database with the kinit utility:

   $ kinit -k -t /path/to/orcl11g.keytab orcl11g/dbhost@EXAMPLE.COM
   $ klist
   Ticket cache: FILE:/tmp/krb5cc_54321
   Default principal: orcl11g/dbhost@EXAMPLE.COM
   Valid starting Expires Service principal
   03/03/16 11:35:50 03/03/16 21:35:50 krbtgt/EXAMPLE.COM@EXAMPLE.COM
    renew until 03/04/16 11:35:50
   Kerberos 4 ticket cache: /tmp/tkt54321
   klist: You have no tickets cached
   $

6. Configure the sql client host as a Kerberos client:
   this step is the same as the previous one (edition of /etc/krb5.conf), except that it must be done on the host where the sql client is running. You can test that the Kerberos configuration is working by getting a ticket-granting ticket for the Enterprise user:

   $ kinit eususer
   Password for eususer@EXAMPLE.COM: 
   $ klist
   Ticket cache: FILE:/tmp/krb5cc_54321
   Default principal: eususer@EXAMPLE.COM
   
   Valid starting     Expires            Service principal
   03/29/12 16:32:41  03/30/12 02:32:41  krbtgt/EXAMPLE.COM@EXAMPLE.COM
       renew until 03/30/12 16:32:38
   
   
   Kerberos 4 ticket cache: /tmp/tkt54321
   klist: You have no tickets cached
   $
   

7. Configure the sql client to authenticate with Kerberos:
   Create a directory to store the SQL client configuration, and define the environment variable TNS_ADMIN to point to this directory:

   $ mkdir -p /path/for/sql/config
   $ export TNS_ADMIN=/path/for/sql/config

   In this directory, create a tnsnames.ora file defining how to contact the database:

   $ cat $TNS_ADMIN/tnsnames.ora
   ORCL11G = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl11g))
    )

   In the same directory, create a sqlnet.ora file defining the connection method to the database:

   $ cat $TNS_ADMIN/sqlnet.ora 
   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
   SQLNET.AUTHENTICATION_SERVICES = (BEQ,KERBEROS5)
   SQLNET.KERBEROS5_CONF=/etc/krb5.conf
   SQLNET.KERBEROS5_CONF_MIT=true

8. Link the LDAP user entry to the Kerberos principal:
   The Kerberos principal that will be used with SQL client must correspond to a LDAP user. In order to do this, you have 2 options:
   • if your LDAP users have the objectclass orcluserV2, you can add the attribute krbprincipalname to the user entry. For instance, if Kerberos principal eususer must correspond to LDAP entry cn=user,dc=example,dc=com, you must add krbprincipalname: eususer@EXAMPLE.COM to the entry.
     
   • if your LDAP users do not have the objectclass orcluserV2, you can user another attribute to store the Kerberos principal name. In this case, you must declare this attribute by setting the attribute orclcommonkrbprincipalattribute of the entry cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com. For instance:

     dn: cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com
     orclcommonkrbprincipalattribute: mail

     will correspond to a user entry

      dn: cn=user,dc=example,dc=com
      mail: eususer@EXAMPLE.COM

9. Test the connection:

   The user must first get a Kerberos ticket-granting ticket, then call sqlplus with the / option that specifies to use the Kerberos TGT:

   $ kinit eususer
   Password for eususer@EXAMPLE.COM: 
   $ klist
   Ticket cache: FILE:/tmp/krb5cc_54321
   Default principal: eususer@EXAMPLE.COM
   
   Valid starting     Expires            Service principal
   03/29/12 16:47:29  03/30/12 02:47:29  krbtgt/EXAMPLE.COM@EXAMPLE.COM
       renew until 03/30/12 16:47:27
   
   
   Kerberos 4 ticket cache: /tmp/tkt54321
   klist: You have no tickets cached
   $ sqlplus /@orcl11g
   
   SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 29 16:47:35 2012
   
   Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
   SQL> exit
   Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   $ klist
   Ticket cache: FILE:/tmp/krb5cc_54321
   Default principal: eususer@EXAMPLE.COM
   
   Valid starting     Expires            Service principal
   03/29/12 16:47:29  03/30/12 02:47:29  krbtgt/EXAMPLE.COM@EXAMPLE.COM
       renew until 03/30/12 16:47:27
   03/29/12 16:47:35  03/30/12 02:47:29  orcl11g/dbhost@EXAMPLE.COM
       renew until 03/30/12 16:47:27
   
   
   Kerberos 4 ticket cache: /tmp/tkt54321
   klist: You have no tickets cached
   $ 
   
   

   You can see using klist utility that after the sql connection, the user has a second ticket corresponding to the database service.

Troubleshooting tips:

If your setup is not working, you can enable Kerberos logs by adding the following in the /etc/krb5.conf of the Kerberos server:

[logging]
 kdc = FILE:/var/log/krb5kdc.log

then restart the KDC to apply the changes:

$ sudo service krb5-kdc restart

The problem can be linked to the host names (if the database hostname is defined as a fully-qualified-domain-name but the database service principal uses only the short name or vice-versa). The logs will show you which service principal is used by the database and if there is a mismatch.

Another common problem is when the database is not able to find any user entry matching the Kerberos principal from the ticket. In this case, you can have a look at OUD access log and check the search done by the database to find the user entry. This will be a search equivalent to:

$ ldapsearch -b <orclcommonusersearchbase> "(<orclcommonkrbprincipalattribute>=<krb_principal from the ticket>)"

You need to make sure that the database is using the proper search base and search filter. The search base is configured in the entry cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com, in the attribute orclcommonusersearchbase. To find its value, you can perform the following search operation:

$ OracleUnifiedDirectory/bin/ldapsearch -h $LDAPSERVER -p $PORT -b cn=common,cn=products,cn=oraclecontext,$BASEDN  "(objectclass=*)" orclcommonusersearchbase
dn: cn=Common,cn=Products,cn=OracleContext,dc=example,dc=com
orclcommonusersearchbase: ou=people,dc=example,dc=com