User created in Apps 11i/R12/12i not sync to OID

You have E-Business Suite 11i/R12/12i Integrated with OID/SSO (10g AS Infrastructure),  Integration is one way (Apps -> OID) or two way (OID <—> Apps). After few months users complain that they created some users in Apps 11i/R12 (FND_USER) but these users are not created in OID.
In order to troubleshoot such issue, you should know process flow

User creation process flow (Apps to OID)
1. You create user in Apps and profile option APPS_SSO_IDENTITY & APPS_LDAP_SYNC is enabled

2. DBMS_LDAP will check in OID for password policy (to check, if password supplied during user creation meets password policy at OID)

3. User creation will create business events in Oracle Apps

4. ODISRV (Oracle Directory Integration Server) will check if any provisioning profile is enabled to pull data from Apps.

5. OID provisioning server will consume business events from Apps (11i/R12)
 

  Your first attempt should be to follow checklist as per metalink note 295606.1 (For 11i) & 380487.1 (For R12/12i)

As per “2.11 Users created in E-Business Suite not propagated to OID” of note 295606.1, you checked and found that

A.
E-Business Suite is set to propagate users to OID  – Yes
B.
There are relevant WorkFlow events get created in 11i Workflow Tables – Yes
C.
Business Event is moved from Pending events to Consumed events – Yes
D.
ODISRV is running on OID Server – Yes  (ODISRV is Directory Integration Daemon used to sync data between OID and Applications or other LDAP Servers) . To know more about OID click here
E. You checked that ApplicationsToOID provisioning profile exists, enabled and no errors – Yes
F.
Latest event propagation was successful in provisioning profile. – Yes
G.
You enabled Extra logging in provisioning profile but no error message in provisioning profile. (Done at OID level) – Yes
H.
You enabled Debug for FND Messages but there was no error message in FND_LOG_MESSAGES table. – YesNext in section 2.11 says; “As of today, no reports have been filed with successful status and failed provisioning from Apps to OID. If this is the case, provide Oracle Support with all logs” 

Step missing under section 2.11 of note  295606.1 ( I hope Oracle Support will put this check under section 2.11) is to check AppsDN Account password.

What is this AppDN Account ? 
AppsDN is special administration account which E-Business Suite (11i/R12/12i) uses to connect to OID for management and administrative tasks when integrating with OID/SSO. This is user’s password is supplied during Apps 11i/R12 registration with OID/SSO.
– 

Where it is AppsDN account stored ?
AppsDN password is stored in E-Business Suite & in OID. In OID it is stored under Entry Management -> Default Realm -> Oracle Context -> Products -> EBusiness -> EBusiness Name Used during Registration

Lets assume your OID realm is onlineappsdba.com and while registering Apps (11i/R12) with OID you used application name (-appname ) as VIS11I then this AppsDN account should be in OID under
Entry Management -> com -> onlineappsdba -> Products -> EBusiness -> VIS11I

How to verify if AppsDN account is expired ?

Login to E-Business Database as apps User and run

SQL> select fnd_preference.get(‘#INTERNAL’, ‘LDAP_SYNCH’,’USERNAME’) ;

Out put should be like
orclApplicationCommonName=”VIS11I”,cn=”EBusiness”,cn=Products, cn=OracleContext,dc=onlineappsdba,dc=com

Then find password stored in Apps as
SQL> select fnd_preference.eget(‘#INTERNAL’, ‘LDAP_SYNCH’,’EPWD’,’LDAP_PWD’) Password from dual;

Check out put  in my case its  “welcome123”

Now Verify if Password in OID is same and not expired 

Connect to OID host and issue command like

$ORACLE_HOME/bin/ldapcompare -h oid_host -poid_port  \
-D cn=orcladmin -w orcladmin_password \
-b “orclApplicationCommonName=<Apps_Registration>, cn=EBusiness,cn=Products,cn=OracleContext,dc=<you_doamin_name>” \
-a userpassword -v <password>

Like in my case
$ORACLE_HOME/bin/ldapcompare -h oid1.com -p 389-D cn=orcladmin -w welcome1 \
-b “orclApplicationCommonName=VIS11I, cn=EBusiness,cn=Products,cn=OracleContext, dc=onlineappsdba,dc=com” \
-a userpassword -v welcome123

If Password is correct, you will get output like
The value welcome123 is contained in the attribute userpassword in DN
orclApplicationCommonName=VIS11I,cn=EBusiness,cn=Products, cn=OracleContext,dc=onlineappsdba,dc=com.

If Password is wrong, you will get output like
The value welcome123 is notcontained in the attribute userpassword in DN orclApplicationCommonName=VIS11I,cn=EBusiness,cn=Products, cn=OracleContext,dc=onlineappsdba,dc=com.

If Password is expired, you will get output like
ldap_compare_s: Invalid credentials
ldap_compare_s: additional info: Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired. Please contact the Administrator to change your password.

Why AppsDN account expired ?

In OID, there is default password policy which locks accounts whose passwords are not changed in last 60 days.

How to prevent this happening in future ?

Option 1: Create another policy in OID and select option “Not to lock account” and assign new password policy to Administrative accounts like AppsDN & orcladmin.  (Note : Multiple Password Policy is supported from OID 10.1.4 and higher

Option 2: Reset AppsDnpassword every 59 days (Depending on your password policy you can reset password to same old value, reseting password unlocks account in case account is locked). 
Note1* If you are changing AppsDN password then you have to register Apps again with OID.
Note2* If you are changing password, password policy option (User should change password on first login) should be disabled during reset of AppsDN password

Related Metalink Notes

437697.1 – How to check/change the eBiz AppsDN user password in AS10g OID
459729.1 – The OID test in the SSO diagnostics page throws an error: “Application container is not created properly”
401836.1 –  Gsl_pwdexpired_excp: Your Password Has Expired Ora-31202
380487.1 – Oracle Application Server 10g with Oracle E-Business Suite Release 12 Troubleshooting Guide
295606.1  Oracle Application Server 10g with Oracle E-Business Suite Release 11i Troubleshooting

Share This Post with Your Friends over Social Media!

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Oracle Gold Partner specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

6 comments
lakshmikanthan says June 16, 2008

Hi,
Thanks atul in this documents are very useful for me.
last few days i have being searching in this query command for how to verified query account.
Now i got solution for that thanks
by
lakshmi

Reply
Paul says August 25, 2008

Hi Atul,
Very useful article. Appreciate your efforts.

I have a question:

We applied the ATG patch RUP 6 in dev and we found out that after the patch, Discoverer user is out of sync with 11i Apps. We can sign into Apps directly with that userid, but when we try to login to Discoverer Plus with that userid, we get ‘Invalid userid/password’ message.

Please let me know your thoughts.

Thanks again.

Reply
Atul says August 26, 2008

Paul,
Are you using 10g AS SSO/OID ?

There is issue with discoverer login to 11i and fix is to apply patch, check in metalink and if you can’t find note update here and I’ll give you metalink note and patch number

Reply
Paul says September 6, 2008

Hi Atual & Team,

I would like to know what exactly happens during Registration & Deregistration in OID?
Does that mean that when you de-register the apps then you can not login to apps at all?
Is there any tool by which you can do this using GUI Interface instead of command level?

Anothr question is, do you always have to DeRegister and ReRegister the apps when you apply ATG RUP patch?

Thanks in advance..

Reply
Atul says September 7, 2008

I would like to know what exactly happens during Registration & Deregistration in OID?

—During registration : Entry is created in OID under tree cn=EBusiness, cn=Products, cn=OracleContext, $your_default_realm_in_OID

During deregistration this entry is deleted

Does that mean that when you de-register the apps then you can not login to apps at all?

–If you wish to login change login profile option to local and reset user password or try localLogin.jsp to login as sysadmin

Is there any tool by which you can do this using GUI Interface instead of command level?
–Nothing which I am aware of..

Anothr question is, do you always have to DeRegister and ReRegister the apps when you apply ATG RUP patch?
–No, there is no need to deregister or reregister

Reply
Amit Vaghela says December 9, 2010

Dear Atul,

We are going to implement SSO in oracle EBS 11.5.10.2.So here my questions are

(1) Which all tables are being updated internally when user is created in EBS 11i?

(2)Which all tables are updated when user is given responsibility.

Find the below tables which i have found out.

tables those reflect are –>

FND_RESPONSIBILITY
WF_USER_ROLES,
FND_APPLICATION,
FND_USER,

Password Management

FND_ORACLE_USERID

Regards,
Amit Vaghela.

Reply
Add Your Reply