Configuring your Oracle Database for Kerberos authentication

Introduction

I have two goals with this post. To show how to setup Kerberos authentication for the Oracle Database and also demonstrate that the use/configuration of Kerberos is pretty straightforward. At least with the versions and OS I have used for this setup.

The Kerberos functionality is provided by the Advanced Security Option of the DB and the Oracle client so it is important that this option has been select while creating the DB and while installing any Oracle Database clients.

There are a lot of articles about Kerberos and how in detail the ticket exchanges work so I’ll not describe this in this blog post. Just doing a quick google search for “Kerberos flow” will give you a lot of hits and links to some good reading.

Main article:

Expected goal:

An end-user using Windows 7 is able to login to an Oracle Database without providing any username/password to the DB.
A Kerberos ticket will be used as a trusted way of providing the user identity to the database. I’ll use SQLplus to test the setup but tools like Sql Developer or Toad etc. will also be able to use this feature if they use the OCI(Thin/Thick) client.
sqlplus

Note: The user still exists locally in the database and authorization is still handled by the individual database. This is not like Enterprise User Security, we just change the authentication mechanism from Username/Password to External(Kerberos) for specific users.

 

The components:

This setup consists of the following components:

  • Active Directory 2012 R2
  • Oracle Linux OS (6.4)
  • Oracle Database 11g R2 (11.2.0.3.0)
  • Windows 7
  • Oracle Database client 11g (11.2.0.1.0)

Some prerequisites:

  • Your Windows 7 client has joined the AD Domain and a domain user is used to login to Windows.
  • An Oracle DB instance has been created and the Advanced security option selected during creation
  • The Oracle Client software has been installed on the Windows 7 client incl. the Advanced Security option
  • Linux has the required Kerberos package. E.g. kinit is available

Solution flow:

The below diagram should give an idea about the typical flow.

flow

Flow Steps:

  • 1. This is the typical Windows client login to the domain. On linux this steps would be the okinit call
  • 2. The client will get a Ticket Granting ticket back from the KDC
  • 3. SQLplus will based on the DB we want to access request a service ticket
  • 4. The KDC will provide a Service ticket
  • 5. SQLplus will present this Service Ticket to the DB
  • 6. The DB will check the validity of the ticket towards the KDC
  • 7. The DB will allow access

Setting up things:

These are the high-level steps we need to do.

  • 1. Setup the required test users and service accounts in the AD and the DB
  • 2. Get our Linux OS to talk Kerberos with the KDC using kinit
  • 3. Get Oracle to talk Kerberos with the KDC using okinit
  • 4. Create a Keytab file and move it to the DB
  • 5. Test the setup locally on the DB server using okinit and SQLplus
  • 6. Setup the Oracle Client to use Kerberos on the Windows Workstation

The details:

Here is what I had to setup in my environment to make this work.

1. Setup the required test users and service accounts in the AD and the DB
We need the following user accounts in the AD:

  • A Domain test User that will be used during login to Windows 7: tester@mydomain.test
  • A Service Account used by the DB during Kerberos communication: oracle11g@mydomain.test

We need the following account in the DB:

    • A test user that that will be used by SQLplus:TESTER@MYDOMAIN.TEST
    • Note this user needs to be created like this:
    • 1. create user "TESTER@MYDOMAIN.TEST" identified externally;  
      or
      2. create user tester identified externally as "tester@MYDOMAIN.TEST";

Notice: The database username needs to be all capital letters if you go with option 1. In option 2 the case of tester@MYDOMAIN.TEST must match whats in the AD

2. Get our Linux OS to talk Kerberos with the KDC and test it using Kinit
There is a default kerberos configuration file we need to update. The typical location is: /etc/krb5.conf
You can configure a lot of different things in this file but I’ll try to keep it to the bare minimum I had to change to get this scenario working.

The default file looks like this:

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

[libdefaults]
 default_realm = EXAMPLE.COM
 dns_lookup_realm = false
 dns_lookup_kdc = false
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true

[realms]
 EXAMPLE.COM = {
  kdc = kerberos.example.com
  admin_server = kerberos.example.com
 }

[domain_realm]
 .example.com = EXAMPLE.COM
 example.com = EXAMPLE.COM

In the [libdefaults] section I changed:

default_realm = EXAMPLE.COM to default_realm = MYDOMAIN.TEST

Now the default_realm matches the Windows Domain name

In the [realms] section I changed:
EXAMPLE.COM = {
kdc = kerberos.example.com
admin_server = kerberos.example.com
}

to

MYDOMAIN.TEST = {
kdc = ad2012r2.mydomain.test
admin_server = ad2012r2.mydomain.test
}

The kdc and admin_server both point to my AD Server. While testing I found that just having the kdc entry was enough.

Finally in the [domain_realm] section I changed:

.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

to

tester = MYDOMAIN.TEST
.tester = MYDOMAIN.TEST

This part is important and sometimes confuses people.
In the domain_realm section we setup a mapping between the domain name of the DB server and the Kerberos realm to use.
In my case the FQDN of the DB server was idm.tester while the AD domain was MYDDOMAIN.TEST

To test the Kerberos configuration we will used the kinit OS command.

I suggest testing both the Domain users created earlier to ensure they both work.

Here is the output for TESTER@MYDOMAIN.TEST

[oracle@idm]$ kinit tester
Password for tester@MYDOMAIN.TEST: 
[oracle@idm]$ klist
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: tester@MYDOMAIN.TEST

Valid starting     Expires            Service principal
11/04/14 11:15:16  11/04/14 21:15:19  krbtgt/MYDOMAIN.TEST@MYDOMAIN.TEST
	renew until 11/11/14 11:15:16

The above result shows that we received a valid TGT from the KDC.

3. Get the Oracle client(on the server) to talk Kerberos with the KDC using OKinit

The okinit command is the Oracle equivalent of kinit but it uses configuration from the Oracle home. So we use this command to verify that the configuration from an Oracle point of view is correct.
We need to add a few lines to the sqlnet.ora used by the Database.

SQLNET.AUTHENTICATION_SERVICES = (BEQ,KERBEROS5)   # Allow Kerberos5 as authentication method
SQLNET.KERBEROS5_CONF = /etc/krb5.conf         # Location of the OS level kerberos configuration file
SQLNET.KERBEROS5_CONF_MIT=TRUE                 # Parse the krb5.conf file based on the MIT Kerberos configuration format.

Now the okinit command can be tested. Ensure the ORACLE_HOME has been set.

[oracle@idm bin]$ ./okinit -e 23 tester
Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 25-FEB-2015 12:06:19
Copyright (c) 1996, 2009 Oracle. All rights reserved.
Password for tester@MYDOMAIN.TEST: 
[oracle@idm bin]$ 

[oracle@idm bin]$ ./oklist 
Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 25-FEB-2015 12:07:19
Copyright (c) 1996, 2009 Oracle. All rights reserved.
Ticket cache: /tmp/krb5cc_54321
Default principal: tester@MYDOMAIN.TEST
 Valid Starting Expires Principal
25-Feb-2015 12:06:21 25-Feb-2015 20:06:19 krbtgt/MYDOMAIN.TEST@MYDOMAIN.TEST

Based on the above we can see that a TGT has been placed in the default Kerberos cache.

4. Create a Keytab file and move it to the Database Server

The Database Service will use the keytab file instead of using username/password when interacting with the KDC. (Use Google to get more details on the contents of the keytab file)

On the Domain Controller you need to use the ktpass tool to generate a keytab file and also associate an SPN(Service Principal) entry to the oracle11g AD User.

C:\Users\Administrator>ktpass -princ orcl/idm.tester@MYDOMAIN.TEST -ptype KRB5_NT_PRINCIPAL 
-crypto RC4-HMAC-NT -pass somepassword -mapuser oracle11g@mydomain.test -out c:\keytab_11g
Targeting domain controller: AD2012R2.mydomain.test
Using legacy password setting method
Successfully mapped orcl/idm.tester to oracle11g.
Key created.
Output keytab to c:\keytab_11g:
Keytab version: 0x502
keysize 64 orcl/idm.tester@MYDOMAIN.TEST ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype
 0x17 (RC4-HMAC) keylength 16 (0xf58d4301ca8034c16ef1843dc53bd113)

The -princ value is case sensitive and constructed in the following way: <Service name>/<FQDN of the DB Server>@<Kerberos realm> The Service name is defined in the SQLNET.AUTHENTICATION_KERBEROS5_SERVICE parameter in the sqlnet.ora file on the Database Server The Kerberos realm is provided in the krb5.conf file. You can search in AD for the oracle11g user and validate that the “User logon name” field on the Account tab contains orcl/idm.tester. This means the SPN has been created as expected. A keytab file keytab_11g should also be created and placed at c:\ Copy this file to the Database server.

The keytab file can be validated using the the oklist tool.

[oracle@idm bin]$ ./oklist -k /tmp/keytab_11g
Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 25-FEB-2015 13:41:09
Copyright (c) 1996, 2009 Oracle. All rights reserved.

Service Key Table: /tmp/keytab_11g

Ver Timestamp Principal
 3 01-Jan-1970 01:00:00 orcl/idm.tester@MYDOMAIN.TEST

This shows that our principal has been created correctly in the keytab file.

The sqlnet.ora file needs to be updated with two more parameters:

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orcl  #A unique service name. It does not need to correspond to the DB Service name
SQLNET.KERBEROS5_KEYTAB=/tmp/keytab_11g   # The absolute location of the keytab file.

 

5. Test the setup locally on the DB server using okinit and sqlplus
All should be setup now and ready for the first test.

In case there is some old kerberos ticket in the cache this can be cleared by running okdstry

[oracle@idm bin]$ ./okdstry

Kerberos Utilities for Linux: Version 11.2.0.1.0 - Production on 25-FEB-2015 13:48:44

Now we can request a new TGT using okinit as shown in step 3.
Use oklist to validate that the ticket is in the Kerberos cache.

Then  we can call sqlplus like this:

[oracle@idm bin]$ ./sqlplus /@orcl
SQL> show user
USER is "TESTER@MYDOMAIN.TEST"
SQL>

The result should be that you logged in to the Database as the user TESTER@MYDOMAIN.TEST.

 

6. Setup the Oracle Client on the Windows 7 workstation to use Kerberos
The next step is to setup the Oracle Database client on the Windows workstation to support Kerberos as authentication method.
The main difference on Windows is that we do not need to use the okinit command to ask for a Kerberos ticket as Windows will take care of this and place the ticket in a cache ready for use.

Note: Tools like kerbtray.exe will provide an easy way to look at the kerberos tickets available in the OS cache.

Login to your workstation using the “tester” user.

Please ensure the Oracle Client has been setup and can connect to the Database using username/password as credentials.

The following parameters must be added to the sqlnet.ora file:

SQLNET.AUTHENTICATION_SERVICES = (NTS,KERBEROS5)
SQLNET.KERBEROS5_CONF = c:/tmp/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.KERBEROS5_CC_NAME=OSMSFT://    # Based on this  sqlnet will look for the kerberos ticket in the Windows OS Credentials Cache.

Then we need to create a krb5.conf file with the following contents:

[realms]
 MYDOMAIN.TEST = {
 kdc = ad2012r2.mydomain.test
 admin_server = ad2012r2.mydomain.test
}
[domain_realm]
tester = MYDOMAIN.TEST
.tester = MYDOMAIN.TEST
.mydomain.test = MYDOMAIN.TEST
 mydomain.test = MYDOMAIN.TEST

Place the file at c:\tmp so it matches the path used in the sqlnet.ora file.

Now we can use sqlplus and test access using a Kerberos ticket.

C:\Users\tester>sqlplus /@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 13 11:26:27 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option

SQL> show user
USER is "TESTER@MYDOMAIN.TEST"
SQL>

 

Support notes and references:

For more info have a look at this Oracle Support note: Configuring ASO Kerberos Authentication with a Microsoft Windows 2008 R2 Active Directory KDC (Doc ID 1304004.1)

Also the official Advanced Security Administrator’s Guide has more details.

Let me know if you have any comments or corrections.

Comments

  1. Thank you so much for this very helpful guide and walk-through. We found it to be very helpful. However, it seems there may be some needed updates or perhaps another edition for 12c environments. A few things I’ve discovered that may be worth noting:

    Kerberos external auth documentation is no longer recorded in the Advanced Security Admin Guide, but instead is now documented in a subsection of the Database Security Guide (http://docs.oracle.com/database/122/DBSEG/configuring-kerberos-authentication.htm#DBSEG060 ). It seems Advanced Security is now reserved for encryption in the DB itself.

    We have been advised that on the client side, the SQLnet.ora parameter SQLNET.AUTHENTICATION_SERVICES should not contain NTS at all. (or if this is incorrect, perhaps you could expound on how they work together)

    There seems to be some confusion abound on the currently correct setting for SQLnet.ora parameter SQLNET.KERBEROS5_CC_NAME. Some say OSMSFT:// some say MSLSA:// . What is the current correct and supported value for this parameter?

    One other question: let’s say you have some application that isn’t very flexible and requires a username and password specified. Even still, we would benefit from centralized password management if the users can just be instructed to supply perhaps their full UPN as username and the appropriate password. Is there any trick or requirement to get that to work (i.e., supply username and password explicitly to the OCI-using software but still have the DB perform kerb auth with those explicit credentials)?
    Many Thanks,
    — Justin

Add Your Comment