Welcome to www.freeoraclehelp.com Got questions? Post comments Like the facebook page to get instant updates. Thank you!!!
Total Pageviews
Search this blog
Loading...

Kerberos Authentication for Oracle database with Microsoft Active Directory KDC

Step by Step procedure to configure Kerberos Authentication for Oracle databases is described in this article. Oracle database supports various external authentication schemes. One of the popular external authentication is Kerberos authentication, which is originally developed under Massachusetts Institute of Technology's Project Athena.

Kerberos authentication (external authentication) is different from Oracle Enterprise User Security (EUS). Kerberos client gets a ticket from KDC, goes to oracle database with this ticket, and Oracle database validates this ticket and allows the user into the database without authentication. Again, If the user has already logged into windows domain (aka NT Login), the oracle database will not ask user name and password to get into the database. Oracle database identifies the user and authorizes the access. Oracle Enterprise User Security (EUS) is not external authentication,  rather users are stored outside the database, Oracle Internet Directory (OID). Oracle Database with EUS asks the user name and password and it validates against OID (not sys.user$). This is also known as Global Authentication.

For Kerberos: create user… identified externally..
For EUS:        create user… identified globally..

Of course, since both are connected to Microsoft Active Directory(AD), it is beneficial to use both Kerberos and EUS. I am going to describe Kerberos authentication in this article. This article is organized into different sections:

Prerequisites

Windows active directory is the domain controller and oracle client machine is registered with the domain. In this example, database server is a Linux server and oracle client is from a Windows XP machine.

Microsoft Active Directory (AD):
  Hostname: win.freeoraclehelp.com
  Active Directory Domain: freeoraclehelp.com
  Operating System: Microsoft Windows Server 2003 R2 Enterprise Editon with Service Pack 2
  Active Directory that incorporates the Kerberos Key Distribution Center

Oracle Database Server:
  Hostname: db.freeoraclehelp.com
  Operating System: Enterprise Linux Enterprise Linux AS release 4 (October Update 8)
  Oracle Database: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 installation with Oracle Advanced Security Option (ASO)
  Database SID is db11gr2

Oracle Database Client:
  Hostname: mareddi.freeoraclehelp.com
  OS: Microsoft Windows XP Professional.
  Oracle Client: Oracle client 10g installation 11.2.0.1 (Installation type: Administrator) with Oracle Advanced Security Option (ASO)

Create a service account in Active Directory for oracle database server to validate the Kerberos tickets. Nothing fancy, just a regular user account.

Oracle Database Kerberos Authentication
Oracle Database Kerberos Authentication
Oracle Database Kerberos Authentication

Create a key tab file for oracle database server:

c:\soft\support\ktpass -princ oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM -ptype KRB5_NT_PRINCIPAL +desonly -crypto des-cbc-md5 -pass ***** -mapuser db -out v5srvtab

Copy this files over to db server as /etc/v5srvtab.

Configure Oracle Database

[db11gr2@db ~]$cat /oracle/product/11.2.0/database/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/product/11.2.0/database/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /oracle/product/11.2.0
DIAG_ADR_ENABLED = ON
[db11gr2@db ~]$
Set remote_os_authent to false in the database:
SQL> show parameter authe

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string
remote_os_authent                    boolean     FALSE
SQL> 
Create database users with the same name as in Windows Domain.
SQL> create user "SSO1@FREEORACLEHELP.COM" identified externally;

User created.

SQL> grant create session to "SSO1@FREEORACLEHELP.COM" ;

Grant succeeded.

SQL> 

Configure Oracle Database Client

Add the FQDN names of KDC (Active Directory) and DB Server into C:\windows\system32\drivers\etc\hosts:
127.0.0.1       localhost
192.168.1.90    win.freeoraclehelp.com win
192.168.1.52    db.freeoraclehelp.com db
Add kerberos5 in C:\windows\system32\drivers\etc\services:
Before:
kerberos           88/tcp    krb5 kerberos-sec      #Kerberos
kerberos           88/udp    krb5 kerberos-sec      #Kerberos
After:
kerberos           88/tcp    kerberos5 krb5 kerberos-sec      #Kerberos
kerberos           88/udp    kerberos5 krb5 kerberos-sec      #Kerberos
Add the following configuration to sqlnet.ora:
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

NAMES.DIRECTORY_PATH= (TNSNAMES)

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (KERBEROS5)
SQLNET.KERBEROS5_CC_NAME=c:\tmp\krb.cc
SQLNET.KERBEROS5_CONF="C:\krb5\krb5.conf"
SQLNET.KERBEROS5_CONF_MIT = TRUE
Of course, sqlnet.ora can be edited using "Net Manager" tool.
C:\krb5\krb5.conf:
[libdefaults]
default_realm = FREEORACLEHELP.COM

[realms]
FREEORACLEHELP.COM = {
kdc = win.freeoraclehelp.com
}

[domain_realm]
.freeoraclehelp.com = FREEORACLEHELP.COM
freeoraclehelp.com  = FREEORACLEHELP.COM
Add the TNS entry for the database (just a regular tns entry):
DB11GR2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db.freeoraclehelp.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11gr2.freeoraclehelp.com)
    )
  )

Login to database using Kerberos

Login to windows domain as SSO1 user and get a Kerberos ticket: 

C:\>okinit

Kerberos Utilities for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT
-2011 20:03:33

Copyright (c) 1996, 2010 Oracle.  All rights reserved.

Password for sso1@FREEORACLEHELP.COM:

C:\>

You may to check the ticket:

C:\>oklist

Kerberos Utilities for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT
-2011 20:04:10

Copyright (c) 1996, 2010 Oracle.  All rights reserved.

Ticket cache: c:\tmp\krb.cc
Default principal: sso1@FREEORACLEHELP.COM

   Valid Starting           Expires            Principal
05-Oct-2011 20:03:37  06-Oct-2011 04:03:33  krbtgt/FREEORACLEHELP.COM@FREEORACLE
HELP.COM

C:\>

Now is the time to test the database login using Kerberos:

Oracle Database Kerberos Authentication
As you see here, database hasn't challenged the user(for a user name or password).. that's Kerberos Authentication;)

Now check the oklist again, it would have one more ticket for database server:
C:\>oklist

Kerberos Utilities for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT
-2011 20:08:21

Copyright (c) 1996, 2010 Oracle.  All rights reserved.

Ticket cache: c:\tmp\krb.cc
Default principal: sso1@FREEORACLEHELP.COM

   Valid Starting           Expires            Principal
05-Oct-2011 20:03:37  06-Oct-2011 04:03:33  krbtgt/FREEORACLEHELP.COM@FREEORACLE
HELP.COM
05-Oct-2011 20:08:06  06-Oct-2011 04:03:33  oracle/db.freeoraclehelp.com@FREEORA
CLEHELP.COM

C:\>

Known Problems

Is that so simple? Well, there is a big list of problems. For all troubleshooting at the client, tracing is the starting point.. you can enable tracing at Oracle client’s sqlnet.ora as

TRACE_LEVEL_OKINIT = 16
TRACE_UNIQUE_OKINIT = on
TRACE_DIRECTORY_OKINIT = c:\tmp
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = c:\tmp
TRACE_FILE_CLIENT = client
LOG_FILE_CLIENT = logfile.log
TRACE_DIRECTORY_SERVER = c:\tmp
TRACE_FILE_SERVER = server
LOG_DIRECTORY_CLIENT = c:\tmp
LOG_DIRECTORY_SERVER = c:\tmp
DIAG_ADR_ENABLED = OFF

To enable tracing at the database server:

ADR_BASE = /oracle/product/11.2.0
DIAG_ADR_ENABLED = ON
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_SERVER = SUPPORT
TRACE_FILE_CLIENT = client
LOG_FILE_CLIENT = logfile.log
TRACE_FILE_SERVER = server

Problem# 1:  Cannot find KDC for requested realm

C:\>okinit

Kerberos Utilities for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT
-2011 20:28:55

Copyright (c) 1996, 2010 Oracle.  All rights reserved.

Password for sso1@FREEORACLEHELP.COM:
okinit: Cannot find KDC for requested realm
.

C:\>
Kinit trace would show:
snlinGetAddrInfo: getaddrinfo() failed with error 10109
snlinGetAddrInfo: exit
snauk5l_sendto_kdc: Returning 88: Cannot find KDC for requested realm
.
snauk5l_sendto_kdc: exit
nauk5la_get_in_tkt: Returning 88: Cannot find KDC for requested realm
.
nauk5la_get_in_tkt: exit
nauk5zi_kinit: Getting TGT failed: Cannot find KDC for requested realm
.
nauk5fq_free_principal: entry
nauk5fq_free_principal: exit
nauk5fq_free_principal: entry
nauk5fq_free_principal: exit
nauk5zi_kinit: Returning 88: Cannot find KDC for requested realm
.
nauk5zi_kinit: exit
naeueag_terminate_encryption: entry

Solution: Add in C:\windows\system32\drivers\etc\services

Before:
kerberos           88/tcp    krb5 kerberos-sec      #Kerberos
kerberos           88/udp    krb5 kerberos-sec      #Kerberos
After:
kerberos           88/tcp    kerberos5 krb5 kerberos-sec      #Kerberos
kerberos           88/udp    kerberos5 krb5 kerberos-sec      #Kerberos

Also, make sure that DB Server and KDC Server FQDN PTR Records are in DNS or add entries in hosts file with lower case names in C:\windows\system32\drivers\etc\hosts of Oracle client and /etc/hosts in DB server:

127.0.0.1       localhost
192.168.1.90    win.freeoraclehelp.com win
192.168.1.52    db.freeoraclehelp.com db

Problem2: ORA-28030: Server encountered problems accessing LDAP directory service

C:\>sqlplus /@db11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 21:03:16 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service

Enter user-name:
C:\>

Solution:  Correct the user name case.

[db11gr2@db ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 5 21:05:04 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  drop user "sso1@FREEORACLEHELP.COM" ;

User dropped.

SQL> create user "SSO1@FREEORACLEHELP.COM" identified externally;

User created.

SQL>  grant create session to "SSO1@FREEORACLEHELP.COM" ;

Grant succeeded.

SQL> 

Problem3: ORA-12631: Username retrieval failed (Mismatch of kvno).

C:\Documents and Settings\sso1>sqlplus /@db11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 19:11:15 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12631: Username retrieval failed

Enter user-name:

Database alert shows:

Fatal NI connect error 12631, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
  Time: 05-OCT-2011 17:26:57
  Tracing to file: Xv
  Tns error struct:
    ns main err code: 12631
    
TNS-12631: Username retrieval failed
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (4743) as a result of ORA-609

Solution: Re-genrate the key tab file with the right kvno#.

Enable tracing to know insights into the error:

SQL> alter system set events '609 errorstack(3)'; 

Now alert log would show the trace file name:

Fatal NI connect error 12631, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
  Time: 05-OCT-2011 17:26:57
  Tracing to file: Xv
  Tns error struct:
    ns main err code: 12631
    
TNS-12631: Username retrieval failed
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
Errors in file /oracle/product/11.2.0/diag/rdbms/db11gr2/db11gr2/trace/db11gr2_ora_4743.trc:
ORA-00609: could not attach to incoming connection
ORA-12631: Username retrieval failed
opiodr aborting process unknown ospid (4743) as a result of ORA-609

Trace file:

2011-10-04 20:04:51.670367 : nauk5wj_ktfileint_open:exit
2011-10-04 20:04:51.670403 : nauk5y2_kt_get_entry:Searching for keytype=3 ,kvno=3;Current keytype=1,kvno=1
2011-10-04 20:04:51.670430 : nauk5fq_free_principal:entry
2011-10-04 20:04:51.670453 : nauk5fq_free_principal:exit
2011-10-04 20:04:51.670481 : snauk5t_close_file:entry
2011-10-04 20:04:51.670506 : snauk5k_lock_file:entry
2011-10-04 20:04:51.670533 : snauk5k_lock_file:Resetting lock.
2011-10-04 20:04:51.670557 : snauk5k_lock_file:exit
2011-10-04 20:04:51.670595 : snauk5t_close_file:exit
2011-10-04 20:04:51.670622 : nauk5fq_free_principal:entry
2011-10-04 20:04:51.670644 : nauk5fq_free_principal:exit
2011-10-04 20:04:51.670684 : nauk5y2_kt_get_entry:Returning 114: Key table entry not found
.
2011-10-04 20:04:51.670706 : nauk5y2_kt_get_entry:exit
2011-10-04 20:04:51.670728 : nauk5fq_free_principal:entry
2011-10-04 20:04:51.670751 : nauk5fq_free_principal:exit
2011-10-04 20:04:51.670779 : nauk5kz_rd_req_simple:Returning 114: Key table entry not found
.
2011-10-04 20:04:51.670801 : nauk5kz_rd_req_simple:exit
2011-10-04 20:04:51.670822 : nauk5ahgetcontext:entry
2011-10-04 20:04:51.670844 : nauk5ahgetcontext:Using default context.
2011-10-04 20:04:51.670866 : nauk5ahgetcontext:exit
2011-10-04 20:04:51.670888 : nauk5kz_rd_req_simple:nauk5kz_rd_req_simple: Key table entry not found

.2011-10-04 20:04:51.670911 : nauk5fq_free_principal:entry
2011-10-04 20:04:51.670934 : nauk5fq_free_principal:exit
2011-10-04 20:04:51.670955 : nauk5a_process_RDREQ:exit
2011-10-04 20:04:51.670978 : nauk5a3recvclientauth:exit
2011-10-04 20:04:51.671001 : nauk5avalidate:nauk5a3recvclientauth() failed to process the request
2011-10-04 20:04:51.671023 : nauk5avalidate:failed
2011-10-04 20:04:51.671044 : nauk5avalidate:exit
2011-10-04 20:04:51.671066 : nau_scn:credential validation function failed
2011-10-04 20:04:51.671088 : nacomsd:entry
2011-10-04 20:04:51.671110 : nacomfsd:entry
2011-10-04 20:04:51.671132 : nacomfsd:exit
2011-10-04 20:04:51.671154 : nacomsd:exit

So, we now know that it’s kvno mismatch.

[db11gr2@db ~]$$ORACLE_HOME/jdk/bin/kinit -k -t /etc/v5srvtab oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM
New ticket is stored in cache file /tmp/krb5cc_500
[db11gr2@db ~]$kvno oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM
oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM: kvno = 3
[db11gr2@db tmp]$klist -k -t /etc/v5srvtab
Keytab name: FILE:/etc/v5srvtab
KVNO Timestamp         Principal
---- ----------------- --------------------------------------------------------
   1 12/31/69 19:00:00 oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM
[db11gr2@db tmp]$

I have deleted the current keytab file and regenerated a new keytab with kvno:3.

c:\> c:\soft\support\ktpass -princ oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM -ptype KRB5_NT_PRINCIPAL +desonly -crypto des-cbc-md5 -pass ***** -mapuser db -out v5srvtab -kvno 3
but it kept changing on the DB server.
2011-10-05 18:19:39.138779 : nauk5y2_kt_get_entry:Searching for keytype=3 ,kvno=4;Current keytype=3,kvno=3

So, I know that its incrementing by 1. I found the most recent kvno on DB Server (ie. 4) and added one to that and generated the key tab.

c:\> c:\soft\support\ktpass -princ oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM -ptype KRB5_NT_PRINCIPAL +desonly -crypto des-cbc-md5 -pass ***** -mapuser db -out v5srvtab -kvno 5
That is it!!
SQL> alter system set events '609 off' ;

Problem4: ORA-12638: Credential retrieval failed

Solution:  Make sure that SQLNET.KERBEROS5_CC_NAME is set in sqlnet.ora and okinit has been run before attempting to connect to the database.

Problem5: ORA-12631: Username retrieval failed (Time mismatch between DB Server and KDC).

Solution: Check the time settings on the KDC and the database server. The difference must be less than 5 minutes, or less than the value for sqlnet.kerberos5_clockskew as set for the Oracle database server. This value is in seconds and defaults to 300.

Problem6: ORA-12638: Credential retrieval failed (domain to realm mapping failed).

Trace file shows Returning –1429577725:

nauk5lu_get_host_realm: Returning -1429577725: Generic error.
nauk5lu_get_host_realm: exit
nauk5ahgetcontext: entry
nauk5ahgetcontext: Using default context.
nauk5ahgetcontext: exit
snauk5f_get_host_realm: snauk5f_get_host_realm: Generic error.
nauk5abuildprinc: exit 

Solution: Change the domain to a lower case name and make sure that windows domain is in lower case.

Before:

[libdefaults]
default_realm = FREEORACLEHELP.COM

[realms]
FREEORACLEHELP.COM = {
kdc = win.freeoraclehelp.com
}

[domain_realm]
.FREEORACLEHELP.COM = FREEORACLEHELP.COM
FREEORACLEHELP.COM  = FREEORACLEHELP.COM

After:

[libdefaults]
default_realm = FREEORACLEHELP.COM

[realms]
FREEORACLEHELP.COM = {
kdc = win.freeoraclehelp.com
}

[domain_realm]
.freeoraclehelp.com = FREEORACLEHELP.COM
freeoraclehelp.com  = FREEORACLEHELP.COM

Related Posts

30 Comments:

Raj Mareddi said...

Thanks Alexander.

Look out for one more post in the same context of EUS and EUS + Kerberos (WNA) for Oracle Databases.

Hopefully this weekend.

Swathi said...

Raj,

Got a good idea about external authentication, Thanks a bunch, Swathi

Raj Mareddi said...

Thank you, Swathi!

Anonymous said...

Hi Raj, Would you have any ideas about implementing WNA with Kerberos tickets for Apps (R12) zero sign-on?

I have follwed FMW Integration Guide for OAM 11g, Chapter 7 Configuring OAM to use WNA (basis of Note 1175190.1), which ends with "confirm that access is granted with no additional login".

If an E-Business user is not challenged by a login prompt, then how does the system determine the responsilibites for that user? There must be some way to authorize the authenticated user that is missing from these instructions.

Attempting to login to R12 gives GUID=NOT_FOUND. I've heard that an attribute called orclguid is used associate logins across wna, oid and E-Business's FND_USER table, but where is this described?

TIA,

Raj Mareddi said...

Hello,

If an E-Business user is not challenged by a login prompt, then how does the system determine the responsilibites for that user?

The user logs on to their PC, this authenticates them against Microsoft Active Directory. As part of that logon process, Microsoft Kerberos Authentication issues a valid Kerberos ticket to the user.When the user attempts to access E-Business Suite, they will be redirected to Oracle Access Manager. Oracle Access Manager recognizes the Microsoft Kerberos ticket, it then issues its own Oracle security tokens to the user including orclguid in HTTP headers, and redirects the user back to the E-Business Suite. The E-Business Suite recognizes the OAM cookie and looks up the user's assigned applications responsibilities using ORCLGUID value in USER_GUID in FND_USER table. That done, it issues its own E-Business Suite security tokens and then passes the user through to E-Business Suite without requiring any additional login.

There must be some way to authorize the authenticated user that is missing from these instructions. Attempting to login to R12 gives GUID=NOT_FOUND...

Have you registered EBS (R12) with OAM? You need to follow either of the following notes to register EBS with 10g or 11g OAM.

OID Attribute "orclguid" is sent in HTTP headers by OAM. Check 4.e and 4.b in the notes.

Integrating Oracle E-Business Suite Release 12 with Oracle Access Manager 11g using Oracle E-Business Suite AccessGate (Doc ID 1309013.1)
=> 4.e in "Install and Configure Oracle E-Business Suite and Oracle Access Manager"
Integrating Oracle E-Business Suite with Oracle Access Manager 10g using Oracle E-Business Suite AccessGate (Doc ID 975182.1)
=> 4.b in "Install and Configure Oracle E-Business Suite and Oracle Access Manager"

I am coming up with step by step instructions for the whole setup very soon.

HTH,
Raj Mareddi

Anonymous said...

Hi Raj, Thanks for your reply. Yes, I have followed 1309013.1 and that allows me to login to E-Business as a user that is in OID (using OAM 11g / Webgate and Access Gate 11g). I get the OAMLogin challenge page and entering credentials of a user in OID gets me to the responsibility list.

We now want to login to E-Business as a user that is defined in AD, using Kerberos tickets as you have described without being prompted for a password.

From your reply, E-Business ... looks up the user's assigned responsibilities using ORCLGUID value in FND_USER table. But my FND_USER table has nothing to do with the user in AD. So it is impossible for E-Business to "find" my AD user there. Do I need to create a user in FND_USER that has the same orclguid as the AD user?

Going back to the OID example: When ODSM was used to create a user in OID, I noticed the same user, with the same GUID was created in FND_USER. So that links oid with E-Business. I'm expecting we need to do the same when id store is AD. I might try some ad-hoc way of doing that now for testing unless you have other suggestion.

Thanks again,

Raj Mareddi said...

Hello,

Here is the flow:

1. Users logs into Windows Domain using his NT ID/User name.
2. User goes to ERP in WNA enabled browser.
3. Browser gets a kerberos ticket and then goes to EBS.
4. EBS simply sends the user to OAM ; EBS can't do any validations for this kerberos ticket.
5. OAM verifies the kerberos session, identifies the user using UID using krbprincipalname in OID.
6. Once OAM successfully verifies the user, it will set OAM SSO Cookie and HTTP Header of ORCLGUID value from the OID of the user.
7. EBS receives this GUID value in the HTTP Header and looks up in FND_USER.USER_GUID for that value
8. If there is a match (meaning there is a user record with that GUID), then it pulls the list of responsibilities.. worklists..of the respective user.

So, the user should exist in AD, OID, and FND_USER for a successful login to EBS.

From your reply, E-Business ... looks up the user's assigned responsibilities using ORCLGUID value in FND_USER table. But my FND_USER table has nothing to do with the user in AD.
This is correct, EBS has no interaction with AD whatsoever.

By default USER_GUID is null and when the user logged into EBS with SSO successfully, it fetches the value of ORCLGUID and puts in USER_GUID column of the user record in FND_USER table. We can't manually construct the value for GUID. If we need to manually do it, we need to find ORCLGUID value in OID and then update in FND_USER.USER_GUID.

What is your provisioning ? The following are options:

AD => OID => EBS
AD <= OID <= EBS
AD => OID (Manually create in EBS)
AD <= OID (Manually create in EBS)
OID => EBS (Manually create in AD)
OID <= EBS (Manually create in AD)
Manually create in AD, Manually create in OID, Manually create in EBS
...etc

Anonymous said...

Hi Raj, Thanks for your detailed explanation.

My provisioning was initially bidirectional OID <=> EBS. Now, it seems to only work from EBS => OID. As I don't have admin access to AD, I've tried to work around by creating a user in EBS with a name (MERI8O) that matches the sAMAccountName of the user already in AD.

That process created a user_guid in fnd_user for MERI8O that matched the automatically - created user in OID. krbprinciplename and sAMAccount Name were both null in OID, so I set them to meri8o using ODSM.

Expectation was that login might work - but no joy; got same message GUID=NOT_FOUND.

Raj Mareddi said...

So, users is created in EBS and then provisioned into OID right ? No provisioning from AD. There is a good chance that attributes aren't matching. Check with AD Admin to send the all the attribute values for the user in question.

A typical user name in OID should look like:


cn=windows sso user4,cn=ad,cn=users,dc=freeoraclehelp,dc=com
objectclass=top
objectclass=organizationalPerson
objectclass=orcladobject
objectclass=orcladuser
objectclass=orcluserv2
objectclass=person
objectclass=inetorgperson
uid=SSO4@LAB.FREEORACLEHELP.COM
mail=SSO4@LAB.FREEORACLEHELP.COM
displayname=Windows SSO
sn=SSO4
orclsamaccountname=LAB.FREEORACLEHELP.COM$SSO4
krbprincipalname=SSO4@LAB.FREEORACLEHELP.COM
orclsourceobjectdn=CN=Windows SSO User4,CN=Users,DC=LAB,DC=FREEORACLEHELP,DC=COM
orcluserprincipalname=SSO4@LAB.FREEORACLEHELP.COM
cn=Windows SSO User4
orclobjectsid=AQUAAAAAAAUVAAAA3yEM7QuNJsAH427TVQQAAA==
orclobjectguid=TbiaIGFlrkeFEU4450/p+A==


Also, have you checked if WNA w/ OAM is certified with EBS ? EBS is little different than other webgates.

I will try to setup this combo and get back to you. I just need some time for that.

Anonymous said...

Hi Raj, Thanks for the attribute list - any chance you can get the corresponding list for the same user from AD in order to compare the OID record with the AD record? Will wait till you have your instance running later ...

Meanwhile instead of trying to modify individual record attributes manually, I am proceeding with Oracle® Fusion Middleware Integration Guide for Oracle Identity Management 11g Release 1 (11.1.1) E10031-01 Chapter 18 Integrating with Microsoft Active Directory.

This describes using expressSyncSetup or Oracle Enterprise Manager FMW Control to create synchronization profiles. I used the latter method, but found only one AD user has been provisioned (created) so far in OID.

Also, do you think I need to "Configure External Authentication Plug-In" as described in that same chapter?

I am going by Steven Chan's blog which says WNA and OAM are certified for R12. E,g, http://blogs.oracle.com/stevenChan/entry/why_does_ebs_integration_with

Thanks again,

Bakorea said...

Hi Raj,
this was bery neat and clear instructions, with troubleshooting at the end. I have tried to implelment using Metalink's Doc id: 331252.1 but failed, and Oracle suspects we misconfugured our KDC keytab.I am goin to try it again using your document and Mentalink's to see where it went wrong.

Raj Mareddi said...

Bakorea,

Sure, good luck with that. You can post here any problem you might face. Thanks

Anonymous said...

Hi Raj,
We've finally been able to get WNA zero sign on to work for E-Business authentication. The key step is to set OID as the Default Store in OAM.

For some reason the authentication could not match on orclsamaccountname, which is in the format DEV.DOMAIN.COM$meri8o (same as your post above). I had to change that to just "meri8o" to get the zero sign on to work. Another way I found was to match on "sn" instead (set User Attribute Name = sn).

Thanks for your help.

Raj Mareddi said...

Congrats. Thanks for letting me know.

bakorea said...

Hi Raj,
I was going through your steps comparing with those from Oracle Metalink doc ( as mentioned earlier) to see the difference.
1. I noticed that there no parameter KRB5_NT_PRINCIPAL,and surely in the log for ktpass complained about it and reported as unknown, KRB5_NT_PRINCIPAL.
2. But then discovered something in your steps that might be due to typo, for principal 'oracle' matched to sqlnet.ora service 'oracle' but in clietn tnsnames, you created an alias of 'db11gr2' which I assume should have failed both to authenticate as kerberos was told service if 'oracle' as I pointed out below:
service name 'oracle' not used consistently across the board..how did the test succeed then? should have failed !! :=(

1. ktpass, created principle for 'oracle' in "ktpass -princ 'oracle'/db.free.."
2. then we configured Oracle database for kerberos in sqlnet.ora with 'oracle' as service_name as hown here:
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE='oracle'

3.Configured Oracle Client, again in sqlnet.ora
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE='oracle'

4. But when configuring tnsnames on client, 'oracle' service name disappears and is replaced by db11gr2! bothtfor th ealias and the
servicename in database. alias should reflect whenat we have given in parameter
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE='oracle', but since we gave different, kerberos should return error.


5. Our test connection test via alias db11gr2 should fail as kerberos does not know that alias, or am I am missing something??
as we said on client, and server and indeed on AD that service we allow is 'oracle', so how did you make it work mith apparent miscinfiguration?

6. checking the granted ticket, is to 'oracle' servicename and not 'db11gr2', hence I suspect you had typo in presenting your test?

oklist, returns principal granted connectionvia kdc is
'oracle'/db.freeoraclehelp.com@FREEORACLEHELP.COM
and not
'db11gr2'/db.freeoraclehelp.com@FREEORACLEHELP.COM

regards,

Nazir

Raj Mareddi said...

Nazir,

There is an apparent confusion here.

AUTHENTICATION_KERBEROS5_SERVICE is the service tag that the KDC Client (DB Server) communicates with the KDC for ticket validations.

Principal name is oracle/db.freeoraclehelp.com@FREEORACLEHELP.COM is the name DB server that goes with to KDC for validations. This information is stored in /etc/v5srvtab and has nothing to do with the DB service name. This is per host, not per database. Meaning, I have can dozens of databases from the same Oracle Home can be Kerberos Auth enabled. All it needs the sqlnet.ora in TNS_ADMIN. We can also enable Kerberos Auth for database in other homes for example: 10gR2 home.. all 10g home needs is sqlnet.ora similar to one in

http://www.freeoraclehelp.com/2011/10/kerberos-authentication-for-oracle.html#DB

Similarly we have principal, http/oid.freeoraclehelp.com@FREEORACLEHELP.COM for the Web Server for which WNA is enabled.

Secondly, KRB5_NT_PRINCIPAL is the type of the principal value. Meaning, this is Kerberos version 5 principal.

Again, this has got nothing to do with SERVICE_NAME in tns entry. The above article is a perfect working case :)

Thanks Nazir for the question :)

~Raj

Raj Mareddi said...

Hey Anonymous :)

Apparently, orclsamaccountname doesn't have the domain name in OID. Whats the attribute you guys are using to look up a user in OID ? Default one we are using is UID. By the way, we dont have to make OID default store as long as long resources have got the right Auth policy.. right auth policy has got right scheme, right scheme has got OID as the store.

Default store is the Weblogic Embedded LDAP store.. admin users like weblogic, groups like Administrators, Operators..etc would be stored in the Weblogic embedded ldap store. We gotta be cautious with this.

I have completed the EBS integration with OAM 11g.. hoping to post it today..

Anyways, good to know ;)

Thanks

bakorea said...

Hi Raj,
thanks for your distinguishing between the two services. Yes, I got it, as the exmaple in Oracle article, had oracle as the principle and the password as oracle, hence it was not easy to distinguish the two.I will soon start testing teh solution in our environment, one thing though Ihave noticed, Oracle uses /desonly you use +desonly in the ktpass command. what is the difference?

regards,

nazir

Raj Mareddi said...

It's basically setting the encryption for the user. KTPASS Comment help says:

DES-only encryption is set by default.

+ Sets an account for DES-only encryption.

- Releases restriction on an account for DES-only encryption.


Windows 7 and Windows Server 2008 R2 do not support DES by default.

So, the options in ktpass might change little bit depending on your MS AD Version. In your environment, what version of AD is going to be used?

Cheers,
Raj Mareddi

bakorea said...

Hi Raj,
I am going to use both windows 2003, sp1( same as Oracle used in proof of concept for metalink article already mentioned) and also we have windows 2008R2 server. The windows 2008R2 is already setup and as expected it does not support DES enc type, however explaintion from MS onhow to enable are not clear. could not find the sections mentioned.
1. I got following errors when running okinit'
okinit: Program lacks support for key type

I enabled trace and show:
nauk5la_get_in_tkt: Returning 25: Additional pre-authentication required
.
nauk5la_get_in_tkt: exit
nauk5la_get_in_tkt: entry
nauk5la_get_in_tkt: Getting TGT failed: Program lacks support for key type
.
nauk5fq_free_principal: entry
nauk5fq_free_principal: exit
nauk5fq_free_principal: entry
nauk5fq_free_principal: exit
nauk5la_get_in_tkt: Returning 85: Program lacks support for key type

HOW TO ENABLE DES encryption in windows 2008R2.
Article from MS to enable is on
http://support.microsoft.com/kb/977321

regards,

nazir

Raj Mareddi said...

Nazir,

Yes, that's right.. I have seen a customer facing this problem when Active Directory is upgraded to 2008 R2 from 2003. The DES encryption stopped working after the upgrade and Oracle SSO Server needed DES. They ended up enabling DES Support in 2008 R2 AD.

BUG:8831131 - NEED TO GET IE8 CERTIFIED ON WINDOWS PLATFORM
BUG:8831156 - NEED WINDOWS 7 PLATFORM CERTIFIED FOR WNA
BUG:9127966 - WINDOWS 7 WITH IE8 PLATFORM CERTIFIED FOR SSO AND WNA
1076018.1 - Oracle Application Server and Oracle Identity Management Certification With Windows 2008
418234.1 - Windows Vista, Windows 7, Windows 2008 or Windows 2008 R2 Clients and Oracle AS Single Sign-On Windows Native Authentication
469747.1 - WNA Stops Working After IDM Version 10.1.4.2 or 10.1.2.3 Patchset Has Been Applied
OSSO WNA is Failing on Windows7 or Windows 2008 Clients (Doc ID 973190.1)

http://technet.microsoft.com/en-us/library/dd560670%28WS.10%29.aspx
http://support.microsoft.com/kb/977321

Cheers,
Raj Mareddi

Mike said...

This is gratefully appreciated, but do you think it might be possible to update it to use 3DES or AES as I have read that Oracle 11g supports them? As you know MS has not enabled DES by default in 2008 for obvious reasons. It is an old crappy protocol that needs to die. Thanks!

Raj Mareddi said...

Mike,

Only DES_CBC_CRC, DES_CBC_MD5 Kerberos encryption types are supported in Oracle10g R2.

The Kerberos implementation now makes use of secure encryption algorithms like 3DES and AES in place of DES. This makes using Kerberos more secure. The Kerberos authentication mechanism in Oracle Database now supports the following encryption types:

DES3-CBC-SHA (DES3 algorithm in CBC mode with HMAC-SHA1 as checksum)

RC4-HMAC (RC4 algorithm with HMAC-MD5 as checksum)

AES128-CTS (AES algorithm with 128-bit key in CTS mode with HMAC-SHA1 as checksum)

AES256-CTS (AES algoritm with 256-bit key in CTS mode with HMAC-SHA1 as checksum)

The Kerberos implementation has been enhanced to interoperate smoothly with Microsoft and MIT Key Distribution Centers.

Please refer to http://docs.oracle.com/cd/B28359_01/network.111/b28530/whatsnew.htm#sthref29

bakorea said...

Hi Raj,
I am back (from Nov 2011) and I am now facing new problem.
I am facing following error:
ORA-12637: Packet receive failed

Trace shows:
04-JUL-2012 16:10:49:545] nsprecv: entry
[04-JUL-2012 16:10:49:545] nsprecv: reading from transport...
[04-JUL-2012 16:10:49:545] nttrd: entry
[04-JUL-2012 16:10:50:577] nttrd: exit
[04-JUL-2012 16:10:50:577] ntt2err: entry
[04-JUL-2012 16:10:50:577] ntt2err: Read unexpected EOF ERROR on 1616
[04-JUL-2012 16:10:50:577] ntt2err: exit
[04-JUL-2012 16:10:50:577] nsprecv: error exit
[04-JUL-2012 16:10:50:577] nserror: entry
[04-JUL-2012 16:10:50:577] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[04-JUL-2012 16:10:50:577] nsrdr: error exit
[04-JUL-2012 16:10:50:577] nsdo: nsctxrnk=0
[04-JUL-2012 16:10:50:577] nsdo: error exit
[04-JUL-2012 16:10:50:577] nsnareceive: error exit
[04-JUL-2012 16:10:50:577] nserror: entry
[04-JUL-2012 16:10:50:577] nserror: nsres: id=0, op=68, ns=12537, ns2=12532; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[04-JUL-2012 16:10:50:577] nacomrc: received 12637 bytes
[04-JUL-2012 16:10:50:577] nacomrc: failed with error 12637
[04-JUL-2012 16:10:50:577] nacomrc: exit
[04-JUL-2012 16:10:50:577] na_receive_packet: failed with error 12637
[04-JUL-2012 16:10:50:577] na_receive_packet: exit
[04-JUL-2012 16:10:50:577] na_client: failed with error 12637

Anonymous said...

Hello Raj;
We are trying to configure Oracle Kerberos authentication with Microsoft 2003 AD. We have oracle DB on OEL 5. But it fails with an Oracle error ORA-12638: Credential retrieval failed. I have checked the clockskew and realm seetings as suggested by you for this error.
Client trace shows some lines as below;
[22-JUN-2012 14:03:48:920] snauk5cw_get_tkt: AcquireCredentialsHandle:call number: failed with status: 1.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: AcquireCredentialsHandle:Status failed with status: 0.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: InitializeSecurityContext:Status failed with status: -2146893053.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: Returning 204: File permissions incorrect

Oracle says the issue is with AD.
Any help gratefully appreciated. Do I need to enable Samba services on DB server to make it a part of AD domain?

Thanks Manoj

Guilherme Poli said...

Congratulations, your article is amazing.

Could you please help me with a doubt? Is it possible to use the configuration described here with Oracle Standard Edition? If Enterprise Edition is needed, do we need the Advanced Security Option?

Thank you.

Raj Mareddi said...

Guilherme Poli,

Yes, Advanced Security Option is needed for Kerberos Authentication.

Anonymous said...

Hi all

We're facing the same issue as Manoj.

[22-JUN-2012 14:03:48:920] snauk5cw_get_tkt: AcquireCredentialsHandle:call number: failed with status: 1.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: AcquireCredentialsHandle:Status failed with status: 0.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: InitializeSecurityContext:Status failed with status: -2146893053.
[22-JUN-2012 14:03:48:935] snauk5cw_get_tkt: Returning 204: File permissions incorrect


The problem only pops up if we're using constrained delegation on our middle-tier. Using unconstrained delegation everything is working fine. Any ideas?

Many thanks
Andreas.

bakorea said...

Hi Raj,
I was of th eopinion you were away on holidays or something as I didn't see any updates..now that you are back, can you have time to check my issue published on "May 15, 2012 5:40 PM"
Still facing same issue of error:
"ORA-12637: Packet receive failed"
environments are as follows:
1. Oracle 11gR2 client on XP
2. Database server, 11gR2 on Linux, Red Hat enterprise edition
3. AD and Kerberos on Windows 2003, SP1

regards,

Nazir

Меир said...

Great article. I configure Kerberos5 authentication successfully on Oracle 10g on Linux machine. But the issue is that I cannot connect to the database from this host itself by regular password authentication and, hence, Grid Control also cannot connect to the database. ORA-12638: Credential retrieval failed occurs. Do I have to use okinit and how can I do this? Because actually I want OMS Grid Control Agent to use password authentication, not Kerberos5 authentication. Thank you for any advice.

Post a Comment