how to authentificate oracle users in active directory

Configuring Oracle Advanced Security Option (ASO) with Microsoft Windows Server 2003 Active Directory Kerberos KDC (Doc ID 331252.1)

 

 

Applies to:

Advanced Networking Option
Information in this document applies to any platform.

Purpose

***Checked for relevance on 22-Sep-2010***
The purpose of this article is to describe how to configure an Oracle database with Advanced Security Option (ASO) for external authentication against Microsoft Windows Server 2003 Active Directory (aka Microsoft Kerberos Key Distribution Centre (KDC)).

Scope and Application

This article is primarily intended for System and Database Administrators wishing to implement Kerberos authentication of Oracle database users to a Windows Server 2003 KDC. The article discusses aspects of configuring Microsoft Windows Server 2003 Active Directory, as well as Kerberos and Oracle Advanced Security Option and can be used as a guide for Oracle/Kerberos implementations on other platforms and versions. The article is intended to supplement the Oracle Advanced Security Administrator's Guide. Oracle Versions Oracle11gR2 ,Oracle10g R2 (10.2.0), Oracle10g R1 (10.1.0) and Oracle9i R2 (9.2.0) have all been tested successfully. All excerpts were taken from working systems. The article assumes the existence of an already fully functional Microsoft Windows Server 2003 Active Directory server and an Oracle database server with Advanced Security Option (ASO) installed.

Configuring Oracle Advanced Security Option (ASO) with Microsoft Windows Server 2003 Active Directory Kerberos KDC

Configuring Oracle Advanced Security Option (ASO) with Microsoft Windows Server 2003 Active Directory Kerberos KDC

Overview

Kerberos is a network authentication protocol originally developed by MIT that authenticates users to network resources, such as an Oracle database. Through the use of service tickets and symmetric-key cryptography, Kerberos eliminates the need to transmit passwords over the network. Oracle Advanced Security Option (ASO) provides authentication adapters for most common external authentication schemes, including Kerberos.

An Oracle database server typically services many Oracle clients. When configured for Kerberos authentication, the database server becomes a client of the Kerberos Key Distribution Centre (KDC). Oracle Advanced Security can readily interoperate with service tickets issued by a Windows 2000 domain controller, however, additional configuration steps are required to enable Kerberos authentication against Windows Server 2003.

Topology

The configuration referred to throughout the article is based on the following topology.

Kerberos Server (Microsoft KDC):

• Host name: apcbdead2.au.oracle.com
• Microsoft Windows Server 2003 Enterprise Edition with Service Pack 1
• Active Directory (incorporating Kerberos Key Distribution Centre (KDC))
• Realm name: APCBDEDOM2.AU.ORACLE.COM

Kerberos Client (Oracle Database):

• Host name: valkyrie.au.oracle.com
• Red Hat Enterprise Linux 3 - Update 4 (2.4.21-27.EL)
• Oracle10g R2 Server Enterprise Edition (10.2.0) with Oracle Advanced Security Option (ASO)
  
• Oracle10g R1 Server Enterprise Edition (10.1.0) with Oracle Advanced Security Option (ASO) with 10.1.0.4 patchset
  
• Oracle9i R2 Server Enterprise Edition (9.2.0) with Oracle Advanced Security Option (ASO) with 9.2.0.6.0 patchset

Oracle Client:

• Host name: valkyrie.au.oracle.com
• Red Hat Enterprise Linux 3 - Update 4 (2.4.21-27.EL)
• Oracle10g R2 Client installation (10.2.0) with Oracle Advanced Security Option (ASO)
  
• Oracle10g R1 Client installation (10.1.0) with Oracle Advanced Security Option (ASO) with 10.1.0.4 patchset
• Oracle9i R2 Client installation (9.2.0) with Oracle Advanced Security Option (ASO) with 9.2.0.6.0 patchset

Part 1: Configure Oracle Kerberos Client to Interoperate with Windows Server 2003 KDC

The following steps are to be performed on the Oracle Database server, the Kerberos Client.

1. Install Kerberos Software on the Kerberos Client

Install the Kerberos software on the Oracle database server, the Kerberos Client.
In this case, Oracle is installed on Red Hat Enterprise Linux 3.0, that provides MIT-compliant Kerberos client/server software.
On Red Hat Linux, the Red Hat Package Manager, /bin/rpm, can be used to verify the required Kerberos client packages are installed.
For example:

[root@valkyrie /etc]$ rpm -qa | grep -i krb5
krb5-workstation-1.2.7-44
pam_krb5-1.73-1
krb5-libs-1.2.7-44

2. Configure Kerberos Client Configuration to Use Windows Server 2003 KDC

Modify the following system configuration files on the Oracle database server.
For example.

# /etc/krb5.conf:
[logging]
default=FILE:/var/log/krb5libs.log
kdc=FILE:/var/log/krb5kdc.log
admin_server=FILE:/var/log/kadmind.log

[libdefaults]
        default_realm = APCBDEDOM2.AU.ORACLE.COM
        default_tkt_enctypes = des-cbc-crc
        default_tgs_enctypes = des-cbc-crc
        default_etypes = des-cbc-crc
        default_etypes_des = des-cbc-crc

[realms]
APCBDEDOM2.AU.ORACLE.COM = {
        kdc = apcbdead2.au.oracle.com
}

[domain_realm]
.au.oracle.com = APCBDEDOM2.AU.ORACLE.COM
au.oracle.com = APCBDEDOM2.AU.ORACLE.COM

[kdc]
profile = /var/kerberos/krb5kdc/kdc.conf

# /etc/krb5.realms:
.au.oracle.com = APCBDEDOM2.AU.ORACLE.COM                                                                                                                         

# /etc/krb.realms:
.au.oracle.com = APCBDEDOM2.AU.ORACLE.COM

# /etc/hosts:
127.0.0.1       localhost.localdomain    localhost
192.168.1.1     valkyrie.au.oracle.com   valkyrie
192.168.1.2     apcbdead2.au.oracle.com  apcbdead2 apcbdead2.apcbdedom2.au.oracle.com apcbdedom2.au.oracle.com apcbdedom2

# /etc/services:
kerberos        88/tcp          kerberos5 krb5  # Kerberos v5
kerberos        88/udp          kerberos5 krb5  # Kerberos v5

Note: Kerberos, including Active Directory, is highly case sensitive, therefore make sure to specify the Active Directory realm name (@APCBDEDOM2.AU.ORACLE.COM) in upper case.

By convention, the Active Directory realm name is usually the same as the network domain name in which Active Directory resides or services.
For the above configuration however, this is not the case.
Here, the network domain name of au.oracle.com maps to an Active Directory realm name of APCBDEDOM2.AU.ORACLE.COM.

3. Ensure Oracle Kerberos Authentication Adapter is Installed on the Oracle Kerberos Client

Verify that the Oracle Kerberos Authentication adapter is installed on the Oracle database server, and is linked into the oracle executable.
To check that the Kerberos authentication adapters is installed, run the $ORACLE_HOME/bin/adapters command.
For example.

[oracle@V1020@valkyrie /home/oracle]$ cd $ORACLE_HOME/bin
[oracle@V1020@valkyrie /u03/app/oracle/product/10.2.0/bin]$ ./adapters

Installed Oracle Advanced Security options are:
    ...
    Kerberos v5 authentication
    RADIUS authentication


[oracle@V1020@valkyrie /u03/app/oracle/product/10.2.0/bin]$ ./adapters ./oracle
    ...
    Kerberos v5 authentication
    RADIUS authentication

If the Kerberos Authentication adapter is not listed, run the Oracle Installer and perform a custom installation to install the Advanced Security Option.

Part 2: Configure Windows 2003 Domain Controller KDC to Interoperate with a Kerberos Client

The following steps are to be performed on the Microsoft Active Directory Server, the Kerberos Server.

4. Create New User for the Kerberos Client in Microsoft Active Directory

Create one or more user accounts in Microsoft Active Directory.
Users are those that will connect to the Oracle database from user workstations using Kerberos authentication.
User scott is created in the following example, though should not be confused with demonstration database schema scott.
The following user properties exist for user scott in Active Directory Users and Computers.

[General tab]
First Name: scott
Last Name: tiger
Display Name: scott tiger

[Account tab:]
User logon name: scott@apcbdedom2.au.oracle.com
User logon name (pre-Windows 2000): APCBDEDOM2\scott

5. Create Oracle Database Service Principal in Microsoft Active Directory

Windows Server 2003 implements a default service ticket encryption type of RC4-HMAC that, as of version Oracle10g R2 (10.2.0), is not yet supported to be able to decrypt - refer to Oracle Enhancement Request Bug 2695053. Therefore, it is necessary to request the creation the Oracle database service principal user specifying an Oracle-supported encryption type, such as DES-CBC-CRC. As of Oracle10g R2 (10.2.0), only the following Kerberos encryption types are supported; DES_CBC_CRC, DES_CBC_MD5.

Note that starting Database server 11gR2 oracle supports  RC4-HMAC

Before Kerberocised clients, such as Oracle, can request Active Directory 2003 to encrypt service tickets using client-defined encryption types, Windows Server 2003 must first be patched to allow this. Due to the restriction described by http://support.microsoft.com/default.aspx?scid=kb;en-us;833708, apply the hotfix or Windows Server 2003 Service Pack 1, implement registry parameter HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Kdc\KdcUseRequestedEtypeForTickets of type of REG_DWORD and value Hex 1, then restart the Kerberos KDC service.

Using Active Directory Users and Computers, create a new user to be used for the Oracle database service principal.
Assuming the Oracle database runs on host valkyrie.au.oracle.com, create a user with a service principal username of valkyrie.au.oracle.com and password of oracle.
For example:

[General tab]
First Name: valkyrie
Display Name: valkyrie

[Account tab:]
User logon name: valkyrie@apcbdedom2.au.oracle.com
User logon name (pre-Windows 2000): APCBDEDOM2\valkyrie

[Account Options:]
Password never expires.


Note: Do not select the 'User must change password on next logon' option.

Complete the creation of the Oracle database service principal user in Microsoft Active Directory using Microsoft's ktpass.exe utility.
The ktpass utility is provided as part of Windows Server 2003 Support Tools, available from http://support.microsoft.com/kb/892777.
Note. Be sure to only use the Windows 2003 version of ktpass to create AD users, and not that from Windows 2000.

The following command modifies the service principal account to use DES encryption types, alters the service name format to incorporate a service name of oracle and extracts the key table to a file - this is required by the database server running on host valkyrie.au.oracle.com.
For example:

C:\Program Files\Support Tools>.\ktpass -princ oracle/valkyrie.au.oracle.com@APCBDEDOM2.AU.ORACLE.COM -mapuser valkyrie -pass oracle /desonly -crypto des-cbc-crc -out c:\temp\keytab.valkyrie
Targeting domain controller: apcbdead2.apcbdedom2.au.oracle.com
Using legacy password setting method
Successfully mapped oracle/valkyrie.au.oracle.com to valkyrie.au.oracle.c.
WARNING: pType and account type do not match. This might cause problems.
Key created.
Output keytab to c:\temp\keytab.valkyrie:
Keytab version: 0x502
keysize 81 oracle/valkyrie.au.oracle.com@APCBDEDOM2.AU.ORACLE.COM ptype 0
(KRB5_NT_UNKNOWN) vno 7 etype 0x1 (DES-CBC-CRC) keylength 8 (0xdab97a0bca45864c)

Note: Kerberos, including Active Directory, is highly case sensitive, therefore make sure to specify the Active Directory realm name (APCBDEDOM2.AU.ORACLE.COM) in upper case.

After running ktpass.exe, Active Directory Users and Computers should display the modified user properties for service principal valkyrie.
For example:

[General tab]
First Name: valkyrie
Display Name: valkyrie

[Account tab:]
User logon name: oracle/valkyrie.au.oracle.com@apcbdedom2.au.oracle.com
User logon name (pre-Windows 2000): APCBDEDOM2\valkyrie

[Account Options:]
Password never expires.
Use DES encryption types for this account

When selecting passwords for users/service principals, passwords must comply with the domain password policy.
By default, Windows Server 2003 Active Directory enforces a password policy that; is not account name-based, contains a minimum 6 characters in length, contains characters from at least three of the following groups - A-Z (upper), a-z (lower), 0-9 (numeric), non-alphanumeric characters (e.g. punctuation).

The default domain password policy can be displayed or changed using Administrative Tools' Default Domain Security Settings by clicking Security Settings, Account Policies, Password Policy. If modified, run 'C:\> gpupdate /force' for user/group policy changes to take immediate effect. All changes made affect the entire domain.

Where multiple databases exist on different servers, you must configure separate service principal usernames for each database host.
Additionally, as a precaution, you might consider changing the keytab file name to reflect the target host for which it was created e.g. keytab.valkyrie.
Whilst the same service name may be used by different database hosts, Active Directory limits one service for each service principal user.

Binary copy (ftp) the key table generated from running ktpass.exe (c:\temp\keytab.valkyrie) to the appropriate Oracle database server.

Part 3: Configure Oracle Database to Interoperate with Windows Server 2003 Active Directory KDC

The following steps are to be performed on the Oracle Database server, the Kerberos Client.

6. Configure Oracle Net Parameters for Database Server

Add the following parameters and appropriate values to the sqlnet.ora file on the database server.

SQLNET.KERBEROS5_CONF
SQLNET.KERBEROS5_KEYTAB
SQLNET.KERBEROS5_CONF_MIT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE
SQLNET.AUTHENTICATION_SERVICES

For example:

# /u03/app/oracle/product/10.2.0/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.KERBEROS5_REALMS = /etc/krb5.realms
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_KEYTAB=/etc/keytab.valkyrie
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.KERBEROS5_CONF_MIT=true
SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5)

Copy the key table file (keytab.valkyrie) generated from Active Directory 2003 to the location specified by parameter SQLNET.KERBEROS5_KEYTAB.

7. Configure Oracle Initialisation Parameters for Database Server

Add the following parameters to the database initialisation parameter file (initSID.ora, spfileSID.ora):

OS_AUTHENT_PREFIX=""
REMOTE_OS_AUTHENT=FALSE

The default value of parameter OS_AUTHENT_PREFIX  is "OPS$" which, for externally identified database users created without this prefix, may result in Oracle error ORA-1017: 'invalid username/password; logon denied'. A limit of 30 characters exists for database usernames - the longer the Active Directory realm name, the shorter usernames may be. Using the default value may limit the maximum length of externally identified usernames, resulting in ORA-978: 'identifier is too long'.

To check the value of initialisation parameters OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT, run the following commands:

SQL> show parameter os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string

SQL> show parameter remote_os_authent
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE

8. Create an Externally Authenticated Oracle User

Create an externally identified user in the Oracle database.
The username should be the same as that created in Active Directory in Step 6 above and appended with the '@' symbol and Active Directory realm name.
The username must be created in upper case and quoted.
For example.

SQL> create user "SCOTT@APCBDEDOM2.AU.ORACLE.COM" identified externally;
User created.
 
SQL> grant connect, resource to "SCOTT@APCBDEDOM2.AU.ORACLE.COM";
Grant succeeded.

Part 4 - Configure the Oracle Database Client to Interoperate with the Oracle Database Server

The following steps are to be performed on the Oracle Client.

9. Configure Oracle Net Parameters for the Oracle Client

Each Oracle client workstation must have Kerberos client and Oracle client software installed and appropriately configured.
In this case however, the Oracle client happens to be the same server as the database server, therefore the same Kerberos system configuration and Oracle home happen to be used.
Though possible to authenticate end-users against Windows Server 2003 KDC when logging to Oracle client workstations running Unix, this configuration is out of the scope of this article.

Add the following parameters and appropriate values to the sqlnet.ora file on the Oracle client.

SQLNET.KERBEROS5_CONF
SQLNET.KERBEROS5_CONF_MIT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE
SQLNET.AUTHENTICATION_SERVICES

Local operating system user scott, that exists on the Oracle client, defines a separate Oracle Net configuration to that of the Oracle database.
For example.

# /home/scott/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=true
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.AUTHENTICATION_SERVICES=(kerberos5)

# /home/scott/tnsnames.ora
V1020 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = valkyrie.au.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = V1020.au.oracle.com)
    )
  )

V1010 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = valkyrie.au.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = V1010.au.oracle.com)
    )
  )

V920 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = valkyrie.au.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = V920.au.oracle.com)
    )
  )


10. Obtain an Initial Ticket for the Oracle Client

Oracle clients must obtain an initial Kerberos Ticket Granting Ticket (TGT) from the Kerberos KDC before being able to connect to the Oracle database.
Failure to request and obtain the initial TGT will result in Oracle error: ORA-12638 'Credential retrieval failure'.
To obtain an initial TGT, run the $ORACLE_HOME/bin/okinit utility on the Oracle client.
For example.

[scott@V1020@valkyrie /home/scott]$ okinit
                                                                                         
Kerberos Utilities for Linux: Version 10.2.0.1.0 - Production on 16-AUG-2005 15:44:11
                                                                                         
Copyright (c) 1996, 2004 Oracle.  All rights reserved.
                                                                                         
Password for scott@APCBDEDOM2.AU.ORACLE.COM:
[scott@V1020@valkyrie /home/scott]$

Note: the database password entered by user scott is the same as that specified when user scott was created in Active Directory as described in Step 3.

To display the list of service tickets obtained by the user, run the $ORACLE_HOME/bin/oklist utility on the Oracle client.
For example.

                                                                                                

[scott@V1020@valkyrie /home/scott]$ oklist
                                                                                         
Kerberos Utilities for Linux: Version 10.2.0.1.0 - Production on 16-AUG-2005 15:45:46
                                                                                         
Copyright (c) 1996, 2004 Oracle.  All rights reserved.
                                                                                         
Ticket cache: /tmp/krb5cc_502
Default principal: scott@APCBDEDOM2.AU.ORACLE.COM
                                                                                         
   Valid Starting           Expires            Principal
16-Aug-2005 15:41:52  16-Aug-2005 23:44:11  krbtgt/APCBDEDOM2.AU.ORACLE.COM@APCBDEDOM2.AU.ORACLE.COM
[scott@V1020@valkyrie /home/scott]$

File krb5cc_ (credential cache), that is solely readable by the user, is created in /tmp directory.
For example.

[scott@V1020@valkyrie /home/scott]$ ls -l /tmp/krb5cc_502
-rw-------    1 scott    dba           527 Aug 16 15:41 /tmp/krb5cc_502


To destroy all tickets obtained by the client, run the $ORACLE_HOME/bin/okdstry utility on the Oracle client.
For example.

[scott@V1020@valkyrie /home/scott]$ okdstry
                                                                                         
Kerberos Utilities for Linux: Version 10.2.0.1.0 - Production on 16-AUG-2005 15:47:51
                                                                                         
Copyright (c) 1996, 2004 Oracle.  All rights reserved.
                                                                                         
[scott@V1020@valkyrie /home/scott]$

Note: After running okdstry, the user's credential cache file is removed and tickets no longer be visible by oklist.

11. Connect to Oracle Database from Oracle Client using Kerberos Authentication

Connect to the Oracle database from the Oracle client workstation.
Rather than specifying a database username and password, use the '/' character.
The '/' character instructs Oracle to connect to the database service (oracle) as the externally identified user (scott) who, based on their Kerberos service tickets and credentials, is authenticated by the KDC.
For example.

[scott@V1020@valkyrie /home/scott]$ sqlplus /@V1020

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 16 15:56:53 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options

SQL>


After connecting to the database, running oklist will list a service ticket for the Oracle database service (oracle).
For example.

Users that have successfully connected to the database using Kerberos authentication can be seen using dynamic view SYS.V$SESSION.
For example.

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 16 16:01:24 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters and Data Mining options

SQL> select username,program from v$session where username like 'SCOTT@%';

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
SCOTT@APCBDEDOM2.AU.ORACLE.COM sqlplus@valkyrie.au.oracle.com (TNS V1-V3)

SQL>

References

Microsoft Active Directory Account Policy Settings
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/library/TechRef/353f7ad9-b53d-41d0-9867-199f6595a01b.mspx

References

NOTE:185897.1 - Kerberos Troubleshooting Guide
NOTE:190312.1 - How to extract and merge Kerberos keytables from Microsoft Active Directory
Oracle Database Advanced Security Administrator's Guide