Groups are not displayed after configuring SQL Authenticator with OBIEE 12C

On a newly installed OBIEE 12c server, we configured the SQL Authenticator but we were unable to list the Groups though there were no errors initially as the datasource was unable to connect to the database itself. Once we fixed below warning we got a few errors which showed that the tables were messed up.
Here is how we fixed the first warning.

Nov 16, 2017 3:45:21 PM oracle.bi.security.provider.wls.authentication.dbms.SQLGroupProviderDelegateImpl validateConfiguration
WARNING: Configuration validation connection exception
java.sql.SQLException: Connection pool not usable.
        at oracle.bi.security.provider.wls.authentication.dbms.SQLGroupProviderDelegateImpl.getConnection(SQLGroupProviderDelegateImpl.java:488)
        at oracle.bi.security.provider.wls.authentication.dbms.SQLGroupProviderDelegateImpl.validateConfiguration(SQLGroupProviderDelegateImpl.java:208)
        at oracle.bi.security.provider.wls.authentication.dbms.SQLGroupProviderDelegateImpl.<init>(SQLGroupProviderDelegateImpl.java:108)
        at oracle.bi.security.provider.wls.authentication.dbms.SQLGroupAuthenticationProvider.initialize(SQLGroupAuthenticationProvider.java:28)
        at com.bea.common.security.internal.legacy.service.SecurityProviderImpl.init(SecurityProviderImpl.java:60)
        at com.bea.common.engine.internal.ServiceEngineImpl.findOrStartService(ServiceEngineImpl.java:363)
        at com.bea.common.engine.internal.ServiceEngineImpl.findOrStartService(ServiceEngineImpl.java:315)
        at com.bea.common.engine.internal.ServiceEngineImpl.lookupService(ServiceEngineImpl.java:257)
        at com.bea.common.engine.internal.ServicesImpl.getService(ServicesImpl.java:72)
        at weblogic.security.service.internal.WLSIdentityServiceImpl.initialize(WLSIdentityServiceImpl.java:47)
        at weblogic.security.service.CSSWLSDelegateImpl.initializeServiceEngine(CSSWLSDelegateImpl.java:297)
        at weblogic.security.service.CSSWLSDelegateImpl.initialize(CSSWLSDelegateImpl.java:218)
        at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.InitializeServiceEngine(CommonSecurityServiceManagerDelegateImpl.java:1965)
        at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initializeRealm(CommonSecurityServiceManagerDelegateImpl.java:498)
        at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.loadRealm(CommonSecurityServiceManagerDelegateImpl.java:845)
        at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.initializeRealms(CommonSecurityServiceManagerDelegateImpl.java:907)
        at weblogic.security.service.CommonSecurityServiceManagerDelegateImpl.preInitialize(CommonSecurityServiceManagerDelegateImpl.java:1189)
        at weblogic.security.service.SecurityServiceManager.preInitialize(SecurityServiceManager.java:570)
        at weblogic.security.PreSecurityService.start(PreSecurityService.java:141)
        at weblogic.server.AbstractServerService.postConstruct(AbstractServerService.java:76)
        at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.glassfish.hk2.utilities.reflection.ReflectionHelper.invoke(ReflectionHelper.java:1262)
        at org.jvnet.hk2.internal.ClazzCreator.postConstructMe(ClazzCreator.java:332)
        at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:374)
        at org.jvnet.hk2.internal.SystemDescriptor.create(SystemDescriptor.java:471)
Solution:
Documentation mentions to deploy the datasource on both ADMIN SERVER and the BI SERVER but for some reason the admin had missed deploying it to the ADMIN SERVER.
Once you choose ADMIN server as the target the warning disappears and in case there are underlying errors it will be displayed.

[REF] https://docs.oracle.com/middleware/1221/biee/BIESC/privileges.htm#BIESC534
Section 3.4.4.3.3 Configuring the Data Source Using Oracle WebLogic Server Administration Console

Read More

JDBC Version with EPM 11.1.2.4

Did you ever wonder which version of JDBC we use with Hyperion 11.1.2.4 ?


Here is how you find out.



1. Go to E:\Oracle\Middleware\jdk160_35\bin
2. java -jar E:\Oracle\Middleware\wlserver_10.3\server\lib\odbc6.jar -getversion
3. Hyperion 11.1.2.4 uses JDBC 4.0 version and ODBC 6.0

Read More

Importing wildcard certificates into a java keystore

Weblogic managed servers support java keystores (.jks) but you have received signed wildcard certificates(.crt) which cannot be directly imported into the keystore.
You may get below error:
Error
keytool error: java.lang.Exception: Input not an X.509 certificate
java.lang.Exception: Input not an X.509 certificate
        at sun.security.tools.keytool.Main.addTrustedCert(Main.java:2655)
        at sun.security.tools.keytool.Main.doCommands(Main.java:1009)
        at sun.security.tools.keytool.Main.run(Main.java:343)
        at sun.security.tools.keytool.Main.main(Main.java:336)
There are myriad number of ways in achieving this along with support from many blogs, videos or you can follow these steps and achieve it. 

Firstly, ensure you have received all the certificates like the CA Root, additional Trust Certificates and intermediate certificates along with a wildcard certificate and a private key/server key which was used while generating the CSR (certificate signing request).
Some clients send these certificates packaged into PKCS12 if not follow the steps from the beginning

Tools required: OpenSSL software

1. Add all certificates and the private key to a single .pem file
openssl x509 -in each_certificate.crt -out complete_pem.txt -outform PEM

2. Now create a .pkcs12 file which is compatible with JAVA 8

openssl pkcs12 -export -inkey complete_pem.txt -in complete_pem.txt -out complete_key.p12

You will be prompted for a password, don't forget to keep a record of it.

3. Create a java keystore now:

Go to JAVA_HOME\bin preferably above Java 8 

keytool -­importkeystore -­deststorepass <hidden> -­destkeypass <hidden> -­destkeystore clientIdentity.jks -­srckeystore

complete_key.p12 -­srcstoretype PKCS12 ­-srcstorepass <password> ­-alias <anything_meaningful>

Here: deststorepass/destkeypass is the destination keystore password

-destkeystore : Java keystore you create.

-srckeystore : is the keystore you created in step 2

-srcstorepass : the password you used in step 2

This is identity keystore as it also contains the private key. Although you can use the same keystore for both. Generally, I like to keep Identity keystore and the Trust keystore separated. 

Similarly, you can create another keystore with only trusted root certificates which will be named as clientTrust.jks.

keytool -import -v -trustcacerts -alias CARoot -file AddTrustExternalCARoot.crt -keystore clientTrust.jks -storepass <hidden>

Import these keystores into Weblogic managed servers, FR Studio , EAS Clients , java security folder(cacerts) etc. There are many documents available on Oracle support for Full SSL Deployment. 

Note: alias is case-sensitive.It is important to note the aliases as Identity key requires it.

This post covers only steps for generating java keystore, when provided with certificates from trusted entities. Once keystores are read it can be used in various locations as required.

For further generic steps on security management refer here:

Security Management

Read More

HPCM 11.1.2.4.123

Error:
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ENABLED_FLG": invalid identifier

Symptoms:
Launching HPCM application from Workspace gives above error post patching HPCM with 11.1.2.4.123 patch.


Solution:
Manually run this step from the Read Me:
Run the following database update script in the Profitability and Cost Management product schema/user using

Oracle SQL Developer:
<EPM_ORACLE_HOME>\products\Profitability\database\Common\Oracle\create_11.1.2.4.120.sql

This creates a column ENABLED_FLG column.

Read More

New Knowledge Article: Troubleshooting Hyperion Financial Management Crash and Hang Issues

Oracle recently launched this new article for most of the HFM issues. It is handy and easy to use.
Helps during troubleshooting.

• Troubleshooting HFM Crash and Hang Issues (Doc ID 2060755.2) 

It provides detail step through process of each issue.

Here's one snippet of it. 

Read More