Configuring OBIEE 11g To use Hyperion SSO Tokens For Essbase, HFM,EPM Fails With Error '[nQSError: 43119] Query Failed: [nQSError: 96002] Essbase Error: Login fails due to invalid login credentials (HY000)' (Doc ID 1940079.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Business Intelligence Reporting and Publishing Option - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

Please see: Document 1993210.1 - OBIEE To Essbase Supported and Recommended Authentication Method and Release notes: Section 44.4.14, "Configuring SSO for Essbase and Hyperion Financial Management Data Sources With Oracle Business Intelligence 11.1.1.5, 11.1.1.6, and 11.1.1.6.2"

 

You are trying to setup shared SSO token between OBIEE (11.1.1.7.x)and Essbase (11.1.2.3)  by following steps outlined in the System Administrators Guide:

11.6 Configuring Oracle Business Intelligence to Use Hyperion SSO Tokens when Communicating with Essbase, Hyperion Financial Management, and EPM Workspace

 

Steps followed :

11.6.4.1 Enabling Oracle Business Intelligence to Generate or Accept a Hyperion SSO Token when Connecting to Essbase, HFM, and EPM Workspace Release 11.1.2.1 or Later

Before enabling to generate or accept a token, see Section 11.6.1, "Prerequisites for Configuring to Use Tokens."

To configure Oracle Business Intelligence for shared token generation when connecting to Essbase and Hyperion Financial Management and to accept tokens when integrated into EPM Workspace Release 11.1.2.1 or later (installed with the EPM System Installer), you first run the runRegSyncUtil utility, then you run an epmsys_registry command.

To enable Oracle Business Intelligence to generate or accept a Hyperion SSO token:

  1. Locate the regSyncUtil_OBIEE-TO-EPM.zip file in the BI_ORACLE_HOME/common/CSS/11.1.2.0 directory and extract it to the same location.

  2. Copy the reg.properties file from the system where Essbase or Hyperion Financial Management is installed:

    EPM_ORACLE_INSTANCE/config/foundation/11.1.2.0
    

    to the following location on the system where Oracle Business Intelligence is installed:

    BI_ORACLE_HOME/common/CSS/11.1.2.0/regSyncUtil/src
    
  3. On Linux systems, make runRegSyncUtil.sh executable using the following command:

    chmod +x runRegSyncUtil.sh
    
  4. Open runRegSyncEditUtil.sh (or runRegSyncEditUtil.bat on Windows systems) for editing. You can find the runRegSyncEditUtil file in the BI_ORACLE_HOME/common/CSS/11.1.2.0/regSyncUtil directory.

  5. In the file, update the ORACLE_HOME and ORACLE_INSTANCE environment variables as appropriate for the given Oracle Business Intelligence environment. Note that ORACLE_HOME should be equivalent to MW_HOME/Oracle_BI1 and ORACLE_INSTANCE should be equivalent to MW_HOME/instances/instancen.

  6. Save and close the file.

  7. Run the utility. The following example shows how to run the utility on Linux:

    BI_ORACLE_HOME/common/CSS/11.1.2.0/regSyncUtil/runRegSyncUtil.sh
    
  8. Run the following epmsys_registry.sh command:

    BI_ORACLE_INSTANCE/config/foundation/11.1.2.0/epmsys_registry.sh removeproperty 
    SHARED_SERVICES_PRODUCT/@applicationId
    
  9. Restart the BI Domain, including the Administration and Managed Servers.

  10. Restart all OPMN processes.

  11. If the BI Domain has the "virtualize" property set to "true," then perform the following steps to set a system property that enables the CSS to use the appropriate attribute for GUIDs:

    1. In the MW_HOME/user_projects/domains/bifoundation_domain/bin directory, edit the setDomainEnv.sh file on UNIX or .cmd on Windows using the syntax appropriate for your operating system and shell.

      Add a Java system property after the EXTRA_JAVA_PROPERTIES line, as shown in the following example for the Windows platform:

      When you integrate with EPM System Release 11.1.2.1, add the Java system property as follows:

      set JAVA_PROPERTIES=%JAVA_PROPERTIES% %EXTRA_JAVA_PROPERTIES%
      set JAVA_PROPERTIES=%JAVA_PROPERTIES% -Didstore.identityAttribute=<name of the GUID attribute used by the identity store>
      

      When you integrate with EPM System Release 11.1.2.2 or later, add the Java system property as follows:

      set JAVA_PROPERTIES=%JAVA_PROPERTIES% %EXTRA_JAVA_PROPERTIES%
      set JAVA_PROPERTIES=%JAVA_PROPERTIES% -Doracle.epm.css.identity.type=fusion
      
    2. Restart the Oracle WebLogic Server Administration Server and Managed Servers.

    3. After restarting the Managed Servers, confirm that the Java system property has been applied by reviewing the standard out file (typically bi_server1.out or the console) and looking for the Java system property that you have added.

When these steps are complete, you can select SSO in the data source connection pool in the Oracle BI Repository to designate an SSO connection. In the case of Workspace integration, you can continue to configure Oracle Business Intelligence for SSO using the Hyperion SSO token.

 

However after following these steps and running a report in OBIEE based off Essbase gave the following error : (Note that Shared Services and OBIEE were using the same OID for authentication. The OID users were provisioned in Shared Services prior to testing this setup.

Error
  View Display Error
 
Odbc driver returned an error (SQLExecDirectW).
  Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A
general error has occurred. [nQSError: 43113] Message returned from OBIS.
  [nQSError: 43119] Query Failed: [nQSError: 96002] Essbase Error: Login fails   due to invalid login credentials (HY000)


When admin/password was used in the Connection Pool for essbase in rpd and unchecked SSO in connection pool settings, then the report ran fine

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms