EBS SSO Login Issue Post migration/re-configuration

 ok, So after migrating our EBS R12.1.3 application from on-promise to client cloud we faced multiple issues. One if the imp issue was SSO login.

Issue 1: For new users or the user whose USER_GUID was not present in FND_USER table, users were not able login and user_guid was not getting populated in FND_USER table. 

ERROR at end of URL: 

https://<URL>.<domain>/OA_HTML/jsp/fnd/fnderror.jsp?text=Exception+while+updating+user+session

Error in Access Gate log: 

01ps] SEVERE exception while updating user GUID -->[[
java.sql.SQLException: ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ORA-06512: at "APPS.FND_OID_UTIL", line 2172
ORA-06512: at line 1

Caused by: java.sql.SQLException: ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ORA-06512: at "APPS.FND_OID_UTIL", line 2172
ORA-06512: at line 1

The SSO registration was successful and we were able to extract OID details from EBS database :

SELECT * FROM fnd_user_preferences WHERE user_name='#INTERNAL' AND module_name like '%LDAP%';

Also, users with user_guid present in table were able to login. which meant there was no issue with SSO configuration. So after digging into details we found that the issue was the firewall was not open from EBS DB to OID APP server.

Temporary Workaround:

Get orclguid value from OID for the user impacted and update it manually in fnd_user table.

Permanent Solution:

Open firewall from EBS DB to OID application for ports 389 & 636.

check if ldapbind is working.

Note: if you are using LB for OID in configuration, Open firewall from EBS DB to OID LB. No need to open port to OID servers.

reference: 

ORA-20001 Unable To Call Fnd_ldap_wrapper.update_user (Doc ID 2764993.1)

Issue 2: After resolving first issue, we were facing issue with user provisioning where team was not able to update user details from user define form or from OID.

Error in user provisioning:

Unabled to call fnd_ldap_wrapper.update_user due to the following reason:

ORA-20001: Unabled to call fnd_ldap_wrapper.update_user due to the following reason:

An unexpected error occurred. Please contact your System Administrator. (USER_NAME\***********). (USER_NAME\*********)

Error in Access Gate log:

####<Apr 12, 2022 1:21:31 PM AEST> <Warning> <oracle.apps.fnd.ext.common.server> <svebs2000004pr.nbndc.local> <wls_ag1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <faf0f208cb11b592:ab35fba:17ffe8e5c1e:-8000-0000000000004afd> <1649733691391> <BEA-000000> <FndSsoFilter::doFilter()-- error encountered
oracle.apps.fnd.ext.common.server.FndSsoException: Exception while updating user session.
at oracle.apps.fnd.ext.common.server.AppsHttpServletRequestWrapper.<init>(AppsHttpServletRequestWrapper.java:354)
at oracle.apps.fnd.ext.common.server.FndSsoFilter.doFilter(FndSsoFilter.java:45)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)

Temporary Workaround :

make user_guid null in fnd_user table using below sql:

@/oracle/ebs/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/fndssouu.sql << Username to be unlinked >>

Permanent Solution:

Disable the profile option : Application SSO LDAP Synchronization