Active Directory Authentication with an Oracle Database

Wouldn’t it be nice to not have to manage user passwords in your Oracle Databases? Setting up Active Directory aka Kerberos Authentication is a way to avoid having to manage passwords and allows users to authenticate against your Active Directory. Below is a list of useful links and My Oracle references that were used when setting up this example:

EDIT - This post gets a lot of hits so I will include some updated information. If you're on 12c you need to add kerberos5pre to your list of services. e.g.

SQLNET.AUTHENTICATION_SERVICES= (beq,kerberos5pre,kerberos5)

If you are an Oracle Support Customer check out
How To Configure Kerberos Authentication In A 12c Database ( Doc ID 1996329.1 ).

If not, here is a good 3rd party write up  https://wiki.loopback.org/display/KB/Oracle+Database+12c+Kerberos+Configuration 

Master Note For Kerberos Authentication (Doc ID 1375853.1)
Configuring ASO Kerberos Authentication with a Microsoft Windows 2008 R2 Active Directory KDC (Doc ID 1304004.1)
Blog Example
Blog with troubleshooter
3rd party Explanation

Traditionally the Oracle Kerberos Authentication adapter was a component of Advanced Security Option(ASO). Kerberos Authentication could be used only if ASO was licensed. Starting with Oracle RDBMS 11.2 Oracle Kerberos Authentication is no longer part of ASO and it can be used in any of the supported versions without the ASO licenses. Oracle Kerberos Authentication does not require any external Kerberos library.

In this example we are setting up the Oracle Linux server dbhost.local which contains the database “mydb”

On the Active Directory Server (or domain member)

Create a service account in Active Directory for the database server moon to validate the Kerberos tickets with. This user does not need any specific rights but enable “password never expires” We called this account "oracledb_svc". Ensure that you deselect Setup option “Use DES Encryption” and select option “Do not require Kerberos PreAuthentication” for this user.

Commands to create user account via Active Directory PowerShell

New-ADUser -name oracledb_svc -Path "ou=myusersou,dc=mydomain,dc=local" -Description "Account for Oracle DB AD auth" -AccountPassword (Read-Host -AsSecureString "password") -Enabled $true

Set-ADAccountControl oracledb_svc -doesnotrequirepreauth $true -PasswordNeverExpires $true

Now set a service principal name using the hostname of your target and the service account. *Host does not need to be a member of the domain, but the host name must be registered in DNS.

DOS command to set service principal name

setspn -A oracle/dbhost.local@MYDOMAIN.LOCAL oracledb_svc

(oracle is just the name of the service, we reuse this name in the kerberos config to point here. This has no connection to service_names of the database.)

Now extract a “keytab” file for this user so we don’t need to enter password to create tickets.

DOS command to export keytab

ktpass.exe -princ oracle/dbhost.mgmt.local@MYDOMAIN.LOCAL -mapuser mydomain\oracledb_svc -crypto all -pass password -out z:\keytab

On the Database Server

Edit the sqlnet.ora file to contain the following:

#Customizations for AD Auth

SQLNET.KERBEROS5_KEYTAB=/u01/app/oracle/product/11.2.0.x/db_1/network/admin/kerberos/keytab

SQLNET.KERBEROS5_CONF=/u01/app/oracle/product/11.2.0.x/db_1/network/admin/kerberos/krb5.conf

SQLNET.KERBEROS5_CONF_MIT=TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)

Copy the Keytab file exported earlier into the path referenced in the sqlnet.ora file above.

Create a new krb5.conf file into the path referenced in the sqlnet.ora file above. Its contents should contain the following:

[libdefaults]

default_realm = MYDOMAIN.LOCAL

[realms]

MYDOMAIN.LOCAL = {

 kdc = dc1.mydomain.local:88

 kdc = dc2.mydomain.local:88

 kdc = dc3.mydomain.local:88

}

[domain_realm]

.mydomain.local = MYDOMAIN.LOCAL

mydomain.local = MYDOMAIN.LOCAL

.mgmt.local = MYDOMAIN.LOCAL

mgmt.local = MYDOMAIN.LOCAL

On the Database

--Value for the query should be blank. If not set it to ''
select value from v$parameter where name = 'os_authent_prefix';
--Value for the query should be FALSE. If not change it.
select value from v$parameter where name = 'remote_os_authent';

On the Windows Client (must be member of domain)

Edit the sqlnet.ora to contain the following. Don't forget to copy the krb5.conf file, it should be the same the one created on the Linux server.

SQLNET.KERBEROS5_CC_NAME=OSMSFT://
SQLNET.AUTHENTICATION_SERVICES= (beq,kerberos5)
SQLNET.KERBEROS5_CONF =C:\apps\oracle\product\11.2.0\client_1\network\admin\kerberos\krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true

Testing

1. Create the user in the database as an external user

SQL> create user BOB IDENTIFIED EXTERNALLY as 'bob@MYDOMAIN.LOCAL;

SQL> grant create session to BOB;

2. Login to the Windows client machine with the domain account created in the database during previous step. Connect to the database via sqlplus. Users AD creds will automatically pass using the command below:

To use SQL Developer with Kerberos

Tools->Preferences->Datbase->Advanced Parameters
Fill in the config file path to C:\apps\oracle\product\11.2.0\client_1\network\admin\kerberos\krb5.conf AND Check off "Use OCI/Think driver"

When you create your database connection choose “Kerberos Authentication” and leave username/password blank.

Troubleshooting

• If you get the error “Cannot Find Kdc For Requested Realm”, this is known to happen when a prior version of Oracle Client was installed.
  See: Okinit 11.2.0.1 Fails With "Cannot Find Kdc For Requested Realm" (Doc ID 1105684.1)

On the windows client open c:\windows\system32\drivers\etc\services and edit to appear as follows:
kerberos           88/tcp    kerberos5 krb5 kerberos-sec      #Kerberos
kerberos           88/udp   kerberos5 krb5 kerberos-sec      #Kerberos

• If sqlplus crashes when starting see these two Oracle doc ID’s.

Sqlplus Is Crashing With error ORA-24550 During KERBEROS AUTHENTICATION (Doc ID 1367957.1)

Sqlplus Corruption When Login Using Kerberos Authentication (Doc ID 1112324.1)

This is usually due to a misconfiguration in the krb5.conf file. In my case we I had to add the domain mgmt.local to the bottom of the domain list in the kr5b.conf files. Identifying this required setting up tracing in the sqlnet.ora file on the Windows client. Here are some useful trace values:
#SQLNET Client TRACING

trace_filelen_client = 100

trace_fileno_client = 2

tnsping.trace_directory = E:\trace

tnsping.trace_level = admin

TRACE_LEVEL_OKINIT=SUPPORT

TRACE_DIRECTORY_OKINIT=E:\trace

TRACE_FILE_OKINIT=E:\trace\okinit.trc

TRACE_LEVEL_CLIENT=16

TRACE_DIRECTORY_CLIENT=E:\trace

TRACE_UNIQUE_CLIENT=on

TRACE_FILE_CLIENT=client

TRACE_TIMESTAMP_CLIENT=ON

DIAG_ADR_ENABLED=OFF

• The command line tools oklist and okinit allow you list your kerberos ticket and manually request them. Example of the oklist tool:

% oklist –f
27-Jul-1999 21:57:51   28-Jul-1999 05:58:14
krbtgt/SOMECO.COM@SOMECO.COM
Flags: FI