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 

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

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:
default_realm = MYDOMAIN.LOCAL

 kdc = dc1.mydomain.local:88
 kdc = dc2.mydomain.local:88
 kdc = dc3.mydomain.local:88

.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_CONF =C:\apps\oracle\product\11.2.0\client_1\network\admin\kerberos\krb5.conf


  1. Create the user in the database as an external user
SQL> grant create session to BOB;
  1. 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.


  • 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 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:
trace_filelen_client = 100
trace_fileno_client = 2
tnsping.trace_directory = E:\trace
tnsping.trace_level = admin
  • 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
Flags: FI


  1. This comment has been removed by the author.

  2. Shouldn't the setspn command include the host's fqdn as follows:

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

    Instead of:

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


Post a Comment

Popular posts from this blog

Create a custom backup report with BI Publisher for Oracle Enterprise Manager/Cloud Control 13c

Oracle Enterprise Manager notify when Recovery Area low