Abdul Muqeet - Oracle Hyperion Blog: March 2012

Wednesday, March 21, 2012

finding the schema privileges

SELECT GRANTEE, 'ROL' TYPE, GRANTED_ROLE PV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '&usercheck'
UNION
SELECT GRANTEE, 'PRV' TYPE, PRIVILEGE PV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '&usercheck'
UNION
SELECT GRANTEE,
'OBJ' TYPE,
MAX(DECODE(PRIVILEGE, 'WRITE', 'WRITE,')) ||
MAX(DECODE(PRIVILEGE, 'READ', 'READ')) ||
MAX(DECODE(PRIVILEGE, 'EXECUTE', 'EXECUTE')) ||
MAX(DECODE(PRIVILEGE, 'SELECT', 'SELECT')) ||
MAX(DECODE(PRIVILEGE, 'DELETE', ',DELETE')) ||
MAX(DECODE(PRIVILEGE, 'UPDATE', ',UPDATE')) ||
MAX(DECODE(PRIVILEGE, 'INSERT', ',INSERT')) || ' ON ' || OBJECT_TYPE || ' "' ||
A.OWNER || '.' || TABLE_NAME || '"' PV
FROM DBA_TAB_PRIVS A, DBA_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.GRANTEE = '&usercheck'
GROUP BY A.OWNER, TABLE_NAME, OBJECT_TYPE, GRANTEE
UNION
SELECT USERNAME GRANTEE, '---' TYPE, 'empty user ---' PV
FROM DBA_USERS
WHERE NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS)
AND NOT USERNAME IN (SELECT DISTINCT GRANTEE FROM DBA_TAB_PRIVS)
AND USERNAME LIKE '%&usercheck%'
GROUP BY USERNAME
ORDER BY GRANTEE, TYPE, PV;

Hyperion Financial Data Quality Management (FDM) and ERP Integrator

Hyperion Financial Data Quality Management (FDM) and ERP Integrator [ID 1134897.1]
Modified 01-MAR-2012 Type DIAGNOSTIC TOOLS Status PUBLISHED
In this Document

Purpose ---
Hyperion Financial Data Quality Management (FDM) and ERP Integrator
Source System Issues:
Data Loads:
Metadata Loads:
EBS Tables:
WebLogic Plugin:
Workspace Issues:
Scripting
Logs:

Applies to:

Hyperion Financial Data Quality Management - Version: 11.1.1.3.00 and later [Release: 11.1 and later ]
Information in this document applies to any platform.

Purpose ---

The purpose of this article is to provide information on FDM and ERPI.

HYP: Financial Data Quality Management group.
Product line is Middleware
Product ID: 4389
Product Name: Hyperion Financial Data Quality Management
Component is EPM Integrator.

Hyperion Financial Data Quality Management (FDM) and ERP Integrator

EPM release 11.1.2.1.000 is now generally available---Please review the following Articles prior to installation.

Release Announcement: Document 1306738.1
EPMA Mandatory Patch: Document 1308127.1
DRM Recommended PSU: Document 1309442.1

The following information is available for FDM and ERPI:

Confirming Patches on EBS:

Document 780874.1 How to Verify EBS Setup for DrillBack to EBS in FDM

Missing Function ID in URL:

Document 976791.1 Drill Down from the ERP Integrator Displays a Blank Page due to Missing Function ID in the URL

Jobs Stuck in ERPI:

Document 1062523.1 How to Reset the Status of an Expired ERPi Job Stuck in "RUNNING" Mode

Installation stages for HFM/FDM/ERPI Configuration:

Document 951369.1 (11.1.1.3) Configure and use ERPi as a Replacement to the Financial Data Quality Management EBS Adapter
Document 1169565.1 (11.1.2) Configure and use ERPi 11.1.2 to Load Data into Financial Data Quality Management from EBS

Document 423712.1 How To Set Up ODI Agents As Windows Services

Drill Configuration/Errors:

Document 1076849.1 When Drilling Back to the EBS Source System After an ERPI Load Has Completed Unexpected Data is Seen

Document 1301241.1 Drilling from FDM to EBS or Peoplesoft Gives the Meassage: "Error calling ERPIDrillService"

Document 976791.1 Drill Down from the ERP Integrator Displays a Blank Page due to Missing Function ID in the URL

Document 1264774.1 ERPi Landing Page is not Displaying EBS Source Records

Document 1069485.1 Drill Back from FDM to ERPI is Not Working With a Copy of ERPI Adapter

Document 780874.1 How to Verify EBS Setup for DrillBack to EBS in FDM
FDM ERPI Adapter Issues

Document 1300758.1 FDM ERPI Import Error:"Error: Import failed" and "Error in getting data" Attempting to Make a Database Connection
Document 1163483.1 Using ERPI Adapter Getting Error "Error: Import Failed" "Error in getting data" During Import
Document 1052154.1 How to Leverage Multiple FDM ERPi Adapters
Document 1069485.1 Drill Back from FDM to ERPI is Not Working With a Copy of ERPI Adapter
Document 1338573.1 FDM Timing Out When Running ERPi Data Rule Import Using ERPi-Fin-B Adapter
Document 1153125.1 Open Source System from FDM into ERPi Returns No Period Activity Records When Leveraging the HFM Adapter

Source System Issues:

Document 1154124.1 How Can an Additional EBS Database be Added within ERPi/ODI for use with FDM?
Document 1234313.1 Troubleshooting Problems Encountered When Initializing a Source System in ERPi
Document 1173240.1When Initialising a Source System in ERPI 'Extract Id : null' is Returned and Initialization Does Not Occur

Data Loads:

Document 988072.1 ERPI - Data Rule Fails - ORA-01438: Value Larger Than Specified Precision Allowed for this Column
Document 1173193.1 ERPI Data Load Does Not Include All The Balances Which Were Expected or Data That Has Been Loaded Does Not Correctly Reconcile With The Source System

Metadata Loads:

Document 1078322.1 New EBS Code Combination IDs are not Updated Inside of ERPi
Document 1157855.1 Running an ERPI EBS GL Dimension Load Rule Fails With ORA-00942: table or view does not exist

EBS Tables:

Document 974390.1 Outline of eBusiness Suite Tables that ERPi 11.1.1.3.00 Requires and the Level of Access

WebLogic Plugin:

Document 1076750.1 ERPI Deployed on Weblogic: Multiple Sets of ODI Scenarios Are Triggered When Running a Single ERPI Data Load Rule From FDM
Document 1275584.1 Multiple Sets of ODI Scenarios are Triggered when Running ERPI Data Load Rule from FDM 11.1.2 (OHS)

Workspace Issues:

Document 1147940.1 ERPi Not Available in Workspace and shows up as "${ERPI}"

Scripting

Document 1335753.1 How To Add An Index in Oracle EBS to Increase Performance in ERPi for Data Load Rule
Document 1077823.1 How to Extract YTD Data from ERPI into FDM in 11.1.1.3
Document 1388960.1 How To Extract YTD Data From EBS Using ERPI Without FDM
Document 1393240.1 How to Extract YTD Data From EBS Using ERPI Into FDM in 11.1.2
Document 1241804.1 Invoking The Erpi Web Services

Logs:

Document 1269052.1 Where are the Logs for ERPI and its Associated Products Stored?
EPM System Defects Fixed Finder Document 1292603.1

Thursday, March 8, 2012

How to Configure Shared Services with an MSAD Primary and Backup Domain Controller

How to Configure Shared Services with an MSAD Primary and Backup Domain Controller? [ID 884203.1]

		Modified 29-SEP-2010 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution

Applies to:

Hyperion Planning - Version: 9.3.0.0.99 to 11.1.1.2.00 - Release: 9.3 to 11.1
Information in this document applies to any platform.

Goal

How to configure Hyperion Shared Services to work with an MSAD primary and secondary domain controller so that if the primary fails, the secondary controller will authenticate users. The aim here is to have transparency in case of a failure of the primary domain controller.

Solution

There are many solutions available but the easiest two to implement are:

1. Setup a DNS alias that resolves to the IP addresses of the Primary and secondary domain controllers and configure Hyperion Shared Services (HSS) to use the DNS alias.

2. Setup two MSAD providers in Shared Services as separate external providers putting the primary as being first in the search order.

How to Reregister an Existing Hyperion Product With Shared Services in Version

How to Reregister an Existing Hyperion Product With Shared Services in Version 11.1.2 [ID 1175663.1]

		Modified 25-JUL-2011 Type HOWTO Status PUBLISHED

In this Document
  Goal
  Solution
  References

Applies to:

Hyperion Essbase - Version: 11.1.2.0.00 to 11.1.2.0.00 - Release: 11.1 to 11.1
Information in this document applies to any platform.

Goal

How to re-register an existing Hyperion product such as Essbase, Essbase Administration Services (EAS), with Shared Services in EPM version 11.1.2?

Solution

1. Locate the product_config_#.xml in the directory EPM_ORACLE_INSTANCE/config/foundation/11.1.2.0/product/product_name

For example; the Essbase product file is located in EPM_ORACLE_INSTANCE/config/foundation/11.1.2.0/product/Essbaseserver/11.1.2.0

2. Open the file product_config_#.xml and locate the line:

<property_name>="hubRegistration">Configured</property>

3. Change the status from Configured to Pending.

4. Rerun 'startconfigtool.bat' or 'startconfigtool.sh' or the EPM System Configurator again and select the product. This enables you to reregister the product with Shared Services.

How to change the Shared Services Logging Level in 11.1.2

How to change the Shared Services Logging Level in 11.1.2 [ID 1158016.1]

		Modified 29-FEB-2012 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution

Applies to:

Hyperion Essbase - Version: 11.1.2.0.00 to 11.1.2.0.00 - Release: 11.1 to 11.1
Hyperion BI+ - Version: 11.1.2.0.00 to 11.1.2.0.00   [Release: 11.1 to 11.1]
Hyperion Financial Management - Version: 11.1.2.0.00 to 11.1.2.0.00   [Release: 11.1 to 11.1]
Hyperion Planning - Version: 11.1.2.0.00 to 11.1.2.0.00   [Release: 11.1 to 11.1]
Information in this document applies to any platform.

Goal

Diagnostic logs are needed frequently to resolve customer issues. By default the logging level is set to give a minimum set of detail to save on server disk space. The logging level will often need to be change to a level to give more details to resolve a problem.

Solution

To resolve this take the following actions.

Edit Oracle_Home\Middleware\user_projects\domain\domain_name\config\fmwconfig\servers\FoundationServices0\logging.xml

Find the following line
<logger name=”oracle.EPMCSS” level=”NOTIFICATION:32” useParentHandlers=”false”>

Change NOTIFICATION to any of the following logging levels:

Message Type	Level	Description
INCIDENT_ERROR	1	A serious problem that may be caused by a bug in the product and that should be reported to Oracle Support. Examples are errors from which you cannot recover or serious problems.
ERROR	1	A serious problem that requires immediate attention from the administrator and is not caused by a bug in the product. An example is if Oracle Fusion Middleware cannot process a log file, but you can correct the problem by fixing the permissions on the document.
WARNING	1	A potential problem that should be reviewed by the administrator. Examples are invalid parameter values or a specified file does not exist.
NOTIFICATION	1	A major lifecycle event such as the activation or deactivation of a primary sub-component or feature. This is the default level for NOTIFICATION.
NOTIFICATION	16	A finer level of granularity for reporting normal events.
TRACE	1	Trace or debug information for events that are meaningful to administrators, such as public API entry or exit points.
TRACE	16	Detailed trace or debug information that can help Oracle Support diagnose problems with a particular subsystem.
TRACE	32	Very detailed trace or debug information that can help Oracle Support diagnose problems with a particular subsystem.

Save the logging.xml file and restart the service

Rebuilding Shared Services After Unrecoverable LDAP Corruption

Rebuilding Shared Services After Unrecoverable LDAP Corruption [ID 1073261.1]

		Modified 28-SEP-2011 Type TROUBLESHOOTING Status PUBLISHED

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details

Applies to:

Middleware > Business Intelligence > Hyperion Query & Reporting
Middleware > Enterprise Performance Management
Information in this document applies to any platform.

Purpose

LDAP is corrupted or is in an unstable state and the customer does not have a backup of the LDAP data files.

Last Review Date

March 12, 2010

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Backup Shared Services Repository (This step is generally done by customer’s database administrator).
a. Oracle Database procedures
b. Microsoft SQL Server procedures
c. IBM DB2 procedures.
Backup openLDAP data files. The openLDAP files are located at Hyperion_Home\SharedServices\9.3.1\openLDAP\var\openldap-data for v9 and Hyperion_Home\products\Foundation\openLDAP\\var\openldap-data for v11.
Backup CSS.xml file.
a. The css.xml file is located at Hyperion_Home\deployments\APP_SERVER\SharedServices9\config for v9.

b. For v.11 you need to extract the cssconfig.xml from the shared services registry. Login to Shared Services with the admin login and go to Application Groups / Foundation / Deployment Metadata / Shared Services Registry / Foundation Services / Shared Services. Right click on CSSConfig and click on export for edit and save it to any location.
Export user security using CSSExport (If possible) - Customer may have a recent copy of the export to use. The CSSExport documentation is located in the zip file at Hyperion_Home\common\utilities\CSSImportExportUtility\cssimportexport.zip. Once it is extracted the document is located at Hyperion_Home\common\utilities\CSSImportExportUtility\cssimportexport\importexport\doc\impexp.pdf
Extract HFM Security – Launch the HFM win32 client. Login as admin. Click on the Extract Security button under the Extract section. Click the connect button and highlight the application. Click on the Open Application button. Note: Only do this step if you have HFM.

Give the security extract a name and location. Click the extract button.

Make sure you get a message stating that the extract was successful.
Delete all openLDAP data files located at Hyperion_Hcssome\SharedServices\9.3.1\openLDAP\var\openldap-data for v9 Hyperion_Home\products\Foundation\openLDAP\var\openldap-data for v.11.
Run the configureHubLDAP.bat program in Hyperion_Home\SharedServices\9.3.1\openLDAP for v.9 or Hyperion_Home\products\Foundation\openLDAP for v11.
Drop shared services repository from database. (This step is generally done by customer’s database administrator).
Run configtool for shared services for Configure Database, choose 1st time configuration.
Use the config tool to re-register other hyperion applications (BI+,HFM, etc)
Restore CSS.xml file from step 3
Restart Shared Services/openLDAP services
Import user security using CSSImport. See documentation from step 4.
Import HFM security. Launch the HFM win32 client. Click on the Load Security button under the Load section. Pick the security file that was extracted on step 5. Click the Load button. Make sure you get a message that the security load was successful. Note: Only do this step if you have HFM.

How can OBIEE Connect to a Hyperion Provider Cluster?

How can OBIEE Connect to a Hyperion Provider Cluster? [ID 802345.1]

		Modified 31-OCT-2011 Type HOWTO Status MODERATED

In this Document
  Goal
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Business Intelligence Server Enterprise Edition - Version: 10.1.3.4.0 [1900] and later [Release: 10g and later ]
Information in this document applies to any platform.

Goal

How can OBI EE connect to a Hyperion Provider Cluster?

Solution

Currently, OBI EE does not support this functionality.

Enhancement Request Number 8407429 'SUPPORT FOR HYPERION APPLICATION PROVIDER SERVICES & CLUSTERED ESSBASE DATABASES' has been raised to request that this functionality be added to a future release.

There is a workaround approach described in the following Whitepaper: -

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/overview/obiee-essbase-modeling-guide-130897.pdf

A copy is downloadable from the Link below.

References

NOTE:780932.1 - Configuring Smartview with OBIEE

Attachments

obiee-essbase-modeling-guide.pdf (332.76 KB)

Easy Steps to Enterprise Performance Management 11.1.2.x Full SSL Configuration

Easy Steps to Enterprise Performance Management 11.1.2.x Full SSL Configuration [ID 1391487.1]

		Modified 02-MAR-2012 Type WHITE PAPER Status PUBLISHED

In this Document
  Abstract
  Document History
  Easy Steps to Enterprise Performance Management 11.1.2.x Full SSL Configuration
  Summary

Applies to:

Hyperion Planning - Version: 11.1.2.1.000 and later [Release: 11.1 and later ]
Information in this document applies to any platform.

Abstract

The purpose of this exercise is to setup SSL in an EPM 11.1.2.1 distributed installation using the following setup:

- Oracle Linux OS: HSS, Planning, OHS, Calc manager, Profitability,

- Microsoft Windows 2003 SP2 64 Bit: Essbase, EAS, APS (Provider Services), EPMA (Webtier and dimension server), HFM

This Whitepaper could also be used in the following installation setups:
- Single Server EPM Installation
- SSL OffLoading where only the OHS server is SSL and not the web application servers

NB: This whitepaper is only meant to be used for testing purposes in a test environment and not meant for production environment.

Document History

Author: Bachir Ndiaye
Create Date 04-01-2012

Easy Steps to Enterprise Performance Management 11.1.2.x Full SSL Configuration

Table of Contents:

Introduction

I. Preparing Keystores and Certificates

a) Creating the CA Private Key

b) Creating the CA Public Key

c) Creating the Servers Public Keys and CSRs

d) Signing CSRs Generated for the EPM Servers

e) Generating Certificates from OHS and Microsoft IIS

f) Adding Certificates to Keystore

g) Setting Up the Default JRockit Keystore on Each Server

II. Setting Up SSL To The Already Deployed Web Applications

a) Hyperion Foundation Services (HSS) Web Application SSL Configuration

b) Hyperion Planning Web Application SSL Configuration

III. Setting SSL to EPMA, HFM And OHS

a) Setting Up EPMA with SSL

b) Setting Up HFM with SSL

c) Setting Up OHS with SSL

IV. OHS Webserver Configuration/Re-Configuration

V. Additional Configurations

a) HFM

b) EAS

Introduction
The purpose of this exercise is to setup SSL in an EPM 11.1.2.1 distributed installation using the following setup:

- Oracle Linux OS: HSS, Planning, OHS, Calc manager, Profitability,
- Microsoft Windows 2003 SP2 64 Bit: Essbase, EAS, APS (Provider Services), EPMA (Webtier and dimension server), HFM

This Whitepaper could also be used in the following installation setups:
- Single Server EPM Installation
- SSL OffLoading where only the OHS server is SSL and not the web application servers

NB: This whitepaper is only meant to be used for testing purposes in a test environment and not meant for production environment.

The first section is included for ease of implementation in a situation where you do not have certificates available.
Please note that all the Screenshots referenced are attached as EPM11121SSL_Screenshots.dococ

I. Preparing Keystores and Certificates

In this exercise, we will use Openssl as a tool to create our own CA authority in order to sign certificate requests that we will create later.
The certificate authority will be named after the Linux server Bachirlnx2 for simplicity
Unzip the folder key_cert_gen to a root drive (E:\)
As a Certificate Authority (CA) we should have a good private key encryption (2048 or 1024 bit long) in order to store it securely in files. This can be done using OpenSSL commands.

a) Creating the CA Private Key
E:\key_cert_gen>openssl version
OpenSSL 0.9.7j 04 May 2006

E:\key_cert_gen>openssl genrsa -out Bachirlnx2CA.key -des 1024
"Loading screen into random state -done
Generating RSA private key, 1024 bit long modulus
..........................++++++
...............++++++
e is 65537 (0x10001)
Enter pass phrase for Bachirlnx2CA.key:
Verifying: Enter pass phrase for Bachirlnx2CA.key:"

E:\key_cert_gen>type Bachirlnx2CA.key

Explanation of the commands:
* genrsa command is used to generate a pair of private key and public key using RSA algorithm.
* -out Bachirlnx2CA.key tells openssl to store the private key in a file called Bachirlnx2CA.key.
* -des option is used to encrypt the private key file Bachirlnx2CA.key with DES algorithm.
* 1024 used to force openssl to generate keys a length of 1024 bits.
* type Bachirlnx2CA.key is Windows command to show the content of Bachirlnx2CA.key.
Note: the file Bachirlnx2CA.key will be created under the folder E:\key_cert_gen

b) Creating the CA Public Key

Now we are ready to generate a self-signed public key certificate based on our private key.
Actually the private key file Bachirlnx2CA.key contains a pair of keys: a private key and a public key.
The private key will be used only by the CA Authority (us) to sign any documents and the public key will be used by whoever (EPM Servers)
receives the document signed by us to verify the signature.

To give out the public key, we need to be put it into a certificate with our name, and signed by our own private key.
This process is call generating a self-signed public key certificate. OpenSSL can do this in a single command

E:\key_cert_gen>openssl req -new -key Bachirlnx2CA.key -x509 -days 3650 -out Bachirlnx2CA.crt -config openssl.cnf

E:\key_cert_gen>type Bachirlnx2CA.crt

Explanation of the commands:
* -req command is used to generate a certificate signing request or self-signed certificate.
* -new option is used to prompt for certificate subject information.
* -key Bachirlnx2CA.key option is used to specify the key file containing the private key and public key. Password will be prompted.
* -x509 option is used to tell req to generate self-signed certificate.
* -days 3650 option is used to make the self-signed certificate valid for 3650 days, about 10 years.
* -out Bachirlnx2CA.crt option is used to tell req to store the self-signed certificate in a file called Bachirlnx2CA.crt.
* -config openssl.cnf option is used to specify the configuration file.
* type Bachirlnx2CA.crt is Windows command to show the content of Bachirlnx2CA.crt.

When you are prompted for distinguished name information, just press Enter key to take the default values.

To print the certificate in clear text then type the following command:

E:\key_cert_gen>openssl x509 -in Bachirlnx2CA.crt -noout -text

Certificate:
Data:
Version: 1 (0x0)
Serial Number:
d4:0f:1b:d5:f0:02:b0:89
Signature Algorithm: md5WithRSAEncryption
Issuer: C=GB, ST=LANCASHIRE, L=MANCHESTER, O=ORACLE, OU=SUPPORT CA, CN=B
achirlnx2
Validity
Not Before: Dec 5 10:16:55 2011 GMT
Not After : Dec 2 10:16:55 2021 GMT
Subject: C=GB, ST=LANCASHIRE, L=MANCHESTER, O=ORACLE, OU=SUPPORT CA, CN=
Bachirlnx2
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public Key: (1024 bit)
Modulus (1024 bit):
00:a3:17:0b:2f:23:44:3f:e5:5a:ff:3b:3a:ca:98:
09:ba:9f:2a:3d:48:21:37:bd:da:fb:ea:bb:88:da:
...
...

As a CA, now we have our private key (Bachirlnx2CA.key) and our public key certificate (Bachirlnx2CA.crt).
We are now ready to sign any requests.

To get the CA to be trusted by the Machines in our EPM environment, the CA public key that's been generated needs to be copied to any Microsoft Windows machines that are part of the EPM system Installation and installed to its trusted root Certification Authority.

(see Fig Ib1_1)

(see Fig Ib1_2)

c) Creating the Servers Public Keys and CSRs

The next section describes how someone else can use keytool to generate a public key and ask us to sign it
In this section, let's assume that userA is using the keytool and wants to have his own private key to sign documents. But he needs his public key certificate to be signed by us, Bachirlnx2.

Why?
Because our CA is trusted.
So UserA starts to generate his own private key and stores it in a keystore file which is more like a keys/certificates container.
This can be done by a single keytool -genkeypair command as shown in the following command session:

F:\Oracle\Middleware\jrockit_160_20\jre\bin>java -version
java version 1.6.0_20
JavaI SE Runtime Environment (build 1.6.0_20-b02)
Oracle JrockitI (build R28.0.2-11-135406-1.6.0_20-20100624-2119-windows-x86_64 , compiled mode)

F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -genkeypair -keyalg RSA -alias Bachirlnx2_key -keysize 1024 -keystore E:\EPM\EPMStore.jks -storepass jksplanning -keypass keyplanning

What is your first and last name? 
[Unknown]: Bachirlnx2 
What is the name of your organizational unit? 
[Unknown]: SUPPORT 
What is the name of your organization? 
[Unknown]: ORACLE 
What is the name of your City or Locality? 
[Unknown]: MANCHESTER 
What is the name of your State or Province? 
[Unknown]: LANCASHIRE 
What is the two-letter country code for this unit? 
[Unknown]: GB 
Is CN=Bachirlnx2, OU=SUPPORT, O=ORACLE, L=MANCHESTER, ST=LANCASHIRE, C=GB correc 
t? 
[no]: yes

The keystore with the private key is created in E:\EPM\EPMStore.jks.

Important to note that the first and last name should always be the server name concerned.
In this case the server name is the Linux server (Bachirlnx2) where HSS,
Hyperion Planning amongst others will be installed. Nothing to do with the CA server,
it just so happened that in this case the CA server is also the server where some of the EPM components will be installed.
Here is what UserA did:
* java -version command is used to check the Java version.
* keytool -genkeypair command is used to generated a key pair: UserA's private key and UserA's public key.
* -keyalg RSA is the encryption algorithm. Beware that if you do not mention this parameter then
the default encryption would be DSA which is not supported by weblogic.
* -keystore EPMStore.jks option specifies the keystore file name to hold the key pair.
* -alias Bachirlnx2_key option specifies the entry name of the key pair in the keystore file,
because keystore file can hold multiple key and certificate entries.
* -keysize 1024 option specifies the key size to be 1024 bits.
* -storepass option specifies a password to protect the keystore file (in this case I used password: jksplanning)
* -keypass option specifies a password to protect Bachirlnx2_key entry in the keystore file (in this case I used password: keyplanning).

Now that the keystore with the key file for EPM server Bachirlnx2 has been created under E:\EPM, you can view its content with the following command:

F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -list -keystore e:\EPM\EPMStore.jks -storepass jksplanning
Keystore type: JKS
Keystore provider: SUN
Your keystore contains 1 entry
bachirlnx2_key, Dec 5, 2011, PrivateKeyEntry,
Certificate fingerprint (MD5): 98:F4:E9:B8:34:B3:7C:0D:E7:58:10:B6:DC:1A:F5:B8

UserA can now use keytool to generate a CSR (Certificate Signing Request) containing his public key and ask us as a CA to sign it for him.
To do this, He needs to run one keytool certreq command as shown below:

F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -certreq -alias Bachirlnx2_key -Keypass keyplanning -keystore E:\EPM\EPMStore.jks -storepass jksplanning -file E:\EPM\Bachirlnx2.csr

Notes on what UserA did: 
* keytool -certreq command is used to generated a CSR (Certificate Sign Request) based on the given key pair Bachirlnx2.key. 
* -alias Bachirlnx2_key option specifies the entry in the keystore file where to get the key pair. 
*-keystore EPMStore.jks option specifies the keystore file. 
* -file Bachirlnx2.csr option specifies the file name where the CSR will be stored. 
* type Bachirlnx2.csr command shows the content of Bachirlnx2.csr

Normally, the distinguished name of the owner of the key pair should be asked when generating a CSR.
But keytool has already asked and stored the distinguished name when generating the key pair.

Now UserA sends his CSR file, Bachirlnx2.csr, to the CA to be signed. The CA Bachirlnx2CA will sign his CSR file into a public key certificate Bachirlnx2.crt.
d) Signing CSRs Generated for the EPM Servers
When we as a CA got UserA's CSR (Certificate Signing Request), Bachirlnx2.csr, we can then sign it with our CA private key (created earlier) using the "openssl x509 -req" command as shown in the command session below:
E:\key_cert_gen>openssl x509 -req -in Bachirlnx2.csr -CA Bachirlnx2CA.crt -CAkey Bachirlnx2CA.key -out Bachirlnx2.crt -days 3650 -CAcreateserial -CAserial bachirlnx2CA.seq

Loading 'screen' into random state - done 
Signature ok subject=/C=GB/ST=LANCASHIRE/L=MANCHESTER/O=ORACLE/OU=SUPPORT/CN=Bachirlnx2 Getting CA Private Key 
Enter pass phrase for Bachirlnx2CA.key: Note that CSR generated by "keytool" is compatible with "OpenSSL".

Note that CSR generated by keytool is compatible with OpenSSL.
Here are some notes on what we did: * openssl x509 -req command signs a CSR (Certificate Sign Request) with my private key Bachirlnx2CA.key and public key certificate Bachirlnx2CA.crt.
* -in Bachirlnx2.csr option specifies the CSR file received from UserA.
* -CA Bachirlnx2CA.crt option specifies the CA public key certificate file.
* -CAkey Bachirlnx2CA.key option specifies the CA private key file. Password will be prompted.
* -days 3650 option specifies that the signed certificate is good for 3650 days.
* -out Bachirlnx2.crt option specifies the file name to store UserA's public key certificate signed by the CA.
* -CAcreateserial option tells OpenSSL to created a serial number file, if it has not been created. The serial number value will start with 1.
It will be inserted into the resulting certificate.
* -CAserial Bachirlnx2CA.seq option specifies the serial number file name.
Run the following command to view the content of the generated certificate using OpenSSL:

E:\key_cert_gen>openssl x509 -in Bachirlnx2.crt -noout -text

Certificate: 
   Data: 
     Version: 1 (0x0) 
     Serial Number: 
         dc:0b:13:91:1f:0a:7d:5f
     Signature Algorithm: md5WithRSAEncryption
    Issuer: C=GB, ST=LANCASHIRE, L=MANCHESTER, O=ORACLE, OU=SUPPORT CA, CN=Bachirlnx2 
    Validity
     Not Before: Dec  5 14:32:08 2011 GMT
       Not After : Feb 21 14:32:08 2020 GMT 
    Subject: C=GB, ST=LANCASHIRE, L=MANCHESTER, O=ORACLE, OU=SUPPORT, CN=Bachirlnx2 
     Subject Public Key Info: 
           Public Key Algorithm: dsaEncryption 
            DSA Public Key:
              pub: 
               0f:03:65:3f:77:fb:6c:b8:dc:fd:fd:81:a1:7d:05:              
8f:2a:13:06:bf:f1:03:06:0d:71:83:61:7b:c5:b4:
    88:b3:ad:76:5b:92:c4:2a:ae:64:ca:a6:d2:a1:5e:
      13:dc:b8:49:92:81:ec:50:e9:2c:69:5d:ee:88:ad: 
.......
........
.......

The detailed information of the certificate seems to be good. The issuer is the correct CA created earlier Bachirlnx2CA. The subject is CN=Bachirlnx2. The expiration date is 2020.

We have finished with generating the certificate for Server Bachirlnx2 (for UserA).
As this is a distributed installation over 2 Machines, we need to generate the certificate for the other server (Server name=VMBNTALLEY64).
For Server VMBNTALLEY64 we need to generate a private key signed by the same CA.
We will use the same keystore so that at the end of the process we have a keystore that has all the certificates and Keys used across the EPM System. Then all we have to do is copy the same keystore to the servers participating in the installation.
Here are the following tasks that need performing:
- Generate the server private key in the same keystore EPMStore.jks
F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -genkeypair -keyalg RSA -alias vmbntalley64_key -keysize 1024 -keystore E:\EPM\EPMStore.jks -storepass jksplanning -keypass keyplanning

What is your first and last name? 
[Unknown]:  VMBNTalley64   (It is Important here to give the Machine Name)
What is the name of your organizational unit? 
 [Unknown]:  SUPPORT 
What is the name of your organization? 
 [Unknown]:  ORACLE 
What is the name of your City or Locality? 
[Unknown]:  MANCHESTER 
What is the name of your State or Province? 
[Unknown]:  LANCASHIRE 
What is the two-letter country code for this unit? 
 [Unknown]:  GB 
Is CN=VMBNTalley64, OU=SUPPORT, O=ORACLE, L=MANCHESTER, ST=LANCASHIRE, C=GB correct? 
[no]:  yes

- Generate the certificate request (CSR file) to be signed by the CA Authority Bachirlnx2CA

E:\key_cert_gen>openssl x509 -req -in vmbntalley64.csr -CA Bachirlnx2CA.crt -CAkey Bachirlnx2CA.key -out vmbntalley64.crt -days 3000 -CAcreateserial -CAserial bachirlnx2CA.seq

Loading 'screen' into random state - done 
Signature ok 
subject=/C=GB/ST=LANCASHIRE/L=MANCHESTER/O=ORACLE/OU=SUPPORT/CN=VMBNTalley64 
Getting CA Private Key 
Enter pass phrase for Bachirlnx2CA.key:

At this point we have dealt with the 2 servers (Bachirlnx2 and VMBNTALLEY64), as far as preparing the certificates is concerned for the servers participating in the installation.
e) Generating Certificates from OHS and Microsoft IIS

For The Oracle HTTP Server OHS:
Start the Wallet manager:

- Microsoft Windows -> Start -> all Programs -> Oracle OHSxxxx -> Integrated Management Tools -> Wallet Manager

- Linux/Unix Start a terminal and change directory to Oracle\Middleware\ohs\bin
Run ./owm
Go to Wallet -> New and enter an alphanumeric password (i.e planning99)

(see Fig Ie1_1)

Click on Yes in order to create a new Certificate Request

(see fig Ie1_2 )

Enter the details of the certificate request bearing in mind that the common name is in fact the server name where the OHS server resides.

(see fig Ie1_3)

(see fig Ie1_4)
Right click on Certificate [Requested] -> Export Certificate Request. Specify a path and a filename (i.e OHS.csr)
(see fig Ie1_5)

Sign the exported OHS certificate request with the CA using OpenSSL: E:\key_cert_gen>openssl x509 -req -in E:\EPM\OHS.csr -CA E:\EPM\Bachirlnx2CA.crt -CAkey E:\EPM\Bachirlnx2CA.key -out E:\EPM\OHS.crt -days 3000 -CAcreateserial -CAserial bachirlnx2CA.seq

Loading 'screen' into random state - done 
Signature ok 
subject=/CN=Bachirlnx2/OU=SUPPORT/O=ORACLE/L=MANCHESTER/ST=LANCASHIRE/C=GB
Getting CA Private Key 
Enter pass phrase for Bachirlnx2CA.key:

Now that OHS certificate has been signed, we are left with IIS.

Generating IIS certificate (only necessary if setting up EPM components that use IIS such as EPMA)

Setting IIS with SSL:
On the windows Machine, Go to Start -> Run -> inetmgr
(see Fig Ie1_6)

(see Fig Ie1_7)

(see Fig Ie1_8)

(see Fig Ie1_9)

(see Fig Ie1_10)

(see Fig Ie1_11)

(see Fig Ie1_12)

(see Fig Ie1_13)

(see Fig Ie1_14)

(see Fig Ie1_15)

Sign the certificate request iiscert.csr:

E:\key_cert_gen>openssl x509 -req -in E:\EPM\iiscert.csr -CA Bachirlnx2CA.crt -CAkey Bachirlnx2CA.key -out E:\EPM\iiscert.crt -days 3000 -CAcreateserial -CAserial bachirlnx2CA_RSA.seq

Loading 'screen' into random state - done 
Signature ok subject=/C=GB/ST=LANCASHIRE/L=MANCHESTER/O=ORACLE/OU=SUPPORT/CN=VMBNTalley64 
Getting CA Private Key 
Enter pass phrase for Bachirlnx2CA.key:

Make a copy of the certificate iiscert.crt to iiscert.cer and import the certificate back to IIS.

Right click on Default web site -> Properties -> Directory Certificate -> Server Certificate

(see Fig Ie1_16)

(see Fig Ie1_17)

(see Fig Ie1_18 )

(see Fig Ie1_19)
(see Fig Ie1_20)
(see Fig Ie1_21)

(see Fig Ie1_22)

IIS is now SSLed. Test by launching the following url.
(see Fig Ie1_23)
So what have we got so far?
- We created a CA called Bachirlnx2 in order to sign certificates and as a result, we generated a private key Bachirlnx2CA.key
and a public key Bachirlnx2CA.crt
- We created a keystore called EPMStore.jks (to be used as a central storage for all certificates across the EPMSystem).
The keystore was created with private keys from each server that is part of the EPM Install.
The private keys created in the EPMStore.jks are Bachirlnx2.key and VMBNTALLEY64.key
- A certificate request was then created for the server hosting HSS, Planning, Calcmanager, Profitablity called Bachirnlnx2.csr.
This was then signed by the CA and we ended up with a public key Bachirlnx2.crt
- A certificate request was also created for the server (VMBNTALLEY64) hosting EPMA, HFM, EAS called vmbntalley64.csr.
This was then signed by the CA and we ended up with a public key vmbntalley64.crt
- A certificate request was generated from the OHS wallet manager, signed by the CA which resulted in OHS.crt
- A certificate request from the IIS server which was then signed by the CA and resulted in iiscert.crt
f) Adding Certificates to Keystore
The next step now is to get all these certificates into the same keystore EPMStore.jks which will be then copied over to each server in the EPM system. CA certificates have to be imported first then the other certificates:
- Importing CA certificate:
F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -importcert -alias Bachirlnx2CA -keypass planning -file E:\EPM\Bachirlnx2CA.crt -keystore E:\EPM\EPMStore.jks -storepass jksplanning

Owner: CN=Bachirlnx2, OU=SUPPORT CA, O=ORACLE, L=MANCHESTER, ST=LANCASHIRE, C=GB 
Issuer: CN=Bachirlnx2, OU=SUPPORT CA, O=ORACLE, L=MANCHESTER, ST=LANCASHIRE, C=GB 
Serial number: d40f1bd5f002b08
Valid from: Mon Dec 05 10:16:55 GMT 2011 until: Thu Dec 02 10:16:55 GMT 2021 Certificate fingerprints: 
   MD5:  B6:8F:82:C9:3B:02:8D:55:CB:B6:44:2D:E2:06:67:5C 
    SHA1: 61:B4:23:AC:D5:5E:97:56:D2:1C:85:7F:B1:41:FF:5C:7A:B8:80:FF
    Signature algorithm name: MD5withRSA 
  Version: 1 
Trust this certificate? [no]:  yes

Certificate was successfully added to keystore
- Importing certificate from Bachirlnx2F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -importcert -alias Bachirlnx2rsa -file E:\EPM\Bachirlnx2.crt -keystore E:\EPM\EPMStore.jks -storepass jksplanning Certificate was added to keystore

- Importing certificate from VMBNTALLEY64F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -importcert -alias vmbntalley64rsa -file E:\EPM\vmbntalley64.crt -keystore E:\EPM\EPMStore.jks -storepass jksplanning Certificate was added to keystore

- Importing certificate from OHS F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -importcert -alias OHScrt -file E:\EPM\OHS.crt -keystore E:\EPM\EPMStore.jks -storepass jksplanning Certificate was added to keystore

- Importing IIS certificate iicert.cer into the Keystore:

F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -importcert -alias iiscert -file E:\EPM\iiscert.cer -keystore E:\EPM\EPMStore.jks -storepass jksplanning Certificate was added to keystore
Now that all certificates have been imported into the keystore as shown by the following command, we will use the same keystore across the EPM System:
- For the weblogic application deployment servers
- And as a JRockit keystore.

Command to list the certificates installed so far in the keystore:
F:\Oracle\Middleware\jrockit_160_20\jre\bin>keytool -list -keystore E:\EPM\EPMStore.jks -storepass jksplanning

Keystore type: JKS 
Keystore provider: SUN 
Your keystore contains 7 entries 
bachirlnx2ca, 06-Dec-2011, trustedCertEntry, 
Certificate fingerprint (MD5): B6:8F:82:C9:3B:02:8D:55:CB:B6:44:2D:E2:06:67:5C 
vmbntalley64_key, 06-Dec-2011, PrivateKeyEntry, 
Certificate fingerprint (MD5): 3F:AB:49:9F:D8:06:91:83:69:17:49:06:F1:C1:56:68 
ohscrt, 06-Dec-2011, trustedCertEntry, 
Certificate fingerprint (MD5): 8D:3F:DF:19:D7:B5:01:A1:AF:ED:C6:0B:1F:0F:0E:FA 
iiscert, 07-Dec-2011, trustedCertEntry, 
Certificate fingerprint (MD5): E6:71:AC:D5:88:91:E2:12:70:A6:E1:65:9E:3C:42:AE 
vmbntalley64rsa, 06-Dec-2011, trustedCertEntry, 
Certificate fingerprint (MD5): 5E:BE:EF:18:87:76:91:73:38:E7:6A:A8:59:8F:79:AB 
bachirlnx2rsa, 06-Dec-2011, trustedCertEntry, 
Certificate fingerprint (MD5): 8C:39:A7:7E:42:BD:C3:7B:AD:6B:24:F8:93:69:BF:4C 
bachirlnx2_key, 06-Dec-2011, PrivateKeyEntry, 
Certificate fingerprint (MD5): 69:1C:9D:0D:42:E0:58:44:E6:F8:95:67:50:13:EC:76

g) Setting Up the Default JRockit Keystore on Each Server

- On each Server, go to the following location Oracle\Middleware\jrockit_160_20\jre\lib\security rename the file cacerts to cacertsold
Copy EPMStore.jks to location Oracle\Middleware\jrockit_160_20\jre\lib\security and rename it to cacerts

- On each server create the folder structure E:\EPM and copy the keystore EPMStore.jks to that folder.

THIS ENDS THE SECTION ON PREPARING KEYSTORES AND CERTIFICATES!
II. Setting Up SSL To The Already Deployed Web Applications

a) Hyperion Foundation Services (HSS) Web Application SSL Configuration

Go through the HSS deployment following the documentation without SSL.
Once HSS has been successfully deployed without SSL and that you are able to login successfully. Stop the HSS service and start the Weblogic admin server by doing the following:
Windows: Start -> Programs -> Oracle Weblogic -> User Projects -> Start Admin Server for Weblogic

On Linux:
Change Directory to Oracle/Middleware/User_Projects/domains/EPMSystem/bin and Run ./startWeblogic.sh

After the admin server has been started, launch the following url to login to the admin console http://server:7001/console and login.

In this example the weblogic admin user is epm_admin

(see Fig IIa1_1)
Go to environment -> Servers to show the list of servers deployed to this instance.

(see Fig IIa1_2)

(see Fig IIa1_3)

Click on the FoundationServices0 server to edit the HSS configuration.

On the General page, enable SSL by selecting the checkbox 'ssl listen port enabled'

This requires that the Keystore HSS be specified

(see Fig IIa1_4)

On this linux server hosting Shared Services I have copied the keystore EPMStore.jks to /u01/OHS_WALLET/RSA_Encrypt/.

Note also that the same keystore EPMStore.jks was copied to Oracle\Middleware\jrockit_160_20\jre\lib\security  and renamed tocacerts.
The password to be entered here is the keystore password generated during the Keytool GenKeyPair command and in this example, it was jksplanning

(see Fig IIa1_5)

The SSL page allows to specify the server Private key. Recall that when the user UserA used the keytool to generate the key pair, an entry was added to the keystore (EPMStore.jks) named Bachirlnx2_key with a password keyplanning, which is the server private key that needs to be specified here. (see Fig IIa1_6 )

The hostname verification needs to be set to None to disable the hostname verifier.

(see Fig IIa1_7 )         Save the configuration changes and restart the HSS service (for Windows) or stop and start process (for linux/Unix)
(see Fig IIa1_8 )
We started HSS in the foreground just to make sure that SSL loads correctly:
Start HSS in the foreground to make sure that the SSL configurations are correct. Once Server has fully started without errors, login to HSS using the SSL port specified in the configuration (default is 28443)

Start up entries that shows that SSL has initialised successfully:

<06-Dec-2011 16:11:32 o'clock GMT> <Notice> <Security> <BEA-090171> 
<Loading the identity certificate and private key stored under the alias 
Bachirlnx2_key 
from the jks keystore file /u01/OHS_WALLET/RSA_Encrypt/EPMStore.jks.> 
<06-Dec-2011 16:11:33 o'clock GMT> <Notice> <Security> <BEA-090169> 
<Loading trusted certificates from the jks keystore file /u01/OHS_WALLET/RSA_Encrypt/EPMStore.jks.> 
<06-Dec-2011 16:11:33 o'clock GMT> <Notice> <Server> <BEA-002613> 
<Channel "Default[1]" is now listening on fe80:0:0:0:213:72ff:fe99:174d:28080 for protocols iiop, t3, 
<Channel "DefaultSecure[3]" is now listening on 0:0:0:0:0:0:0:1:28443 for protocols iiops,  t3s, 
CLUSTER-BROADCAST-SECURE, ldaps, https.> <06-Dec-2011 16:11:33 o'clock GMT> 
<Notice> <Server> <BEA-002613><Channel "Default[2]" is now listening on 127.0.0.1:28080 for protocols iiop, t3, 
CLUSTER-BROADCAST, ldap, snmp, http.> 
<06-Dec-2011 16:11:33 o'clock GMT> <Notice> <Server> <BEA-002613> 
<Channel "DefaultSecure[2]" is now listening on 127.0.0.1:28443 for protocols iiops, t3s,

(see Fig IIa1_9)

Now that HSS has been manually setup with SSL, you will need to run the configuration utility again on the same server to reconfigure The Hyperion Foundation -> Configure Common Settings

(see Fig IIa1_10)

Select the option to use SSL for Web Application Server Communication.

(see Fig IIa1_11)

Once that's done, configure the other already deployed EPM web application servers.

b) Hyperion Planning Web Application SSL Configuration
We will configure Hyperion Planning as an example but the process and information entered are the same for all Web applications deployed in the same server:

Login to the Weblogic admin console and edit the Hyperion planning Server. Enable the SSL port (8343)
(see Fig IIb1_1)
Navigate to the Keystore and SSL ports and enable the following settings which are similar to those of HSS

(see Fig IIb1_2)

(see Fig IIb1_3)

(see Fig IIb1_4)

Restart Hyperion Planning web application server in foreground in order to make sure that the settings are correct:
              <06-Dec-2011 17:18:49 o'clock GMT> <Notice> <Security> <BEA-090171> <Loading the identity certificate and private key stored under the alias Bachirlnx2from the jks keystore file /u01/OHS_WALLET/RSA_Encrypt/EPMStore.jks.>
<06-Dec-2011 17:18:49 o'clock GMT> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the jks keystore file /u01/OHS_WALLET/RSA_Encrypt/EPMStore.jks.>
<Channel "DefaultSecure" is now listening on 10.167.110.40:8343 for protocols iiops, t3s, CLUSTER-BROADCAST-SECURE, ldaps, https.>

You should be able to login directly to Hyperion planning web via the SSL port:
(see Fig IIb1_5)

Use the same procedure to enable SSL on all EPM Products deployed on the same Machine.

III. Setting SSL to EPMA, HFM And OHS
a) Setting Up EPMA with SSL
It has two tiers to it, the Dimension sever tier on IIS application server and the Web application tier on Weblogic Application Server.

As for the Dimension then it has already been setup with SSL which was achieved by simply setting IIS with SSL (see section on IIS).
To test that the Dimension Server is listening on SSL, launch the following url on https:
https://IISserver/hyperion-bpma-server/Sessions.asmx
(see Fig IIIa1_1)
To test the login section, click on the link CreateSession and then login as admin user and the password.
Once you click on the Invoke button a new popup will appear with the session ID which means that everything works fine as far as the dimension server/Shared Services interaction is concerned. As for the EPMA Webtier then the process is the same as the SSL configuration of the other web application servers. The only difference here is that it is on a different machine.
- Login to the weblogic admin console on the EPMA machine and edit the EpmWebReports0 Server Enable the SSL port (19047 but in this example I have changed it to 19043)
(see Fig IIIa1_2)

Navigate to the Keystore and SSL tabs and make the necessary changes

(see Fig IIIa1_3)

Remember the server private key alias was created as vmbntalley64_key

(see Fig IIIa1_4)

Login directly to EPMA web tier using the SSL port and launch the dimension library to make sure that all works in SSL (https://vmbntalley64:19043/awb):

(see Fig IIIa1_5 )

Do the same for the Datasync web application:

(see Fig IIIa1_6)

(see Fig IIIa1_7)
EPMA is done!

b) Setting Up HFM with SSL
(see Fig IIIb1_1)

(see Fig IIIb1_2)

(see Fig IIIb1_3)

The IIS side of HFM could be tested by launching the following url (https://vmbntalley64/hfm):

HFM works fine when accessed via the IIS SSL port (port 443)

(see Fig IIIb1_4)

NOW that all the components have been setup in SSL, We need now to configure OHS so that users can go through SSL via OHS -> Workspace

to access all available components via SSL.

This type of Architecture is a full SSL configuration and in a case where only OHS needs to be configured for SSL (SSL OFFLOADING) then you would only do the OHS part.

c) Setting Up OHS with SSL The OHS certificate request was already generated via the Wallet and signed by our CA Authority Bachirlnx2 to generate OHS.crt.

The next step now is to import all required certificates into the wallet starting with the CA certificate Bachirlnx2CA.crt:
- Bachirlnx2CA.crt
- OHS.cert
- IIS certificate IIcert.cer
- Certificate from each of the servers: Bachirlnx2.crt and vmbntalley64.crt

Importing the CA certificate Bachirlnx2CA

Start by importing the CA certificate:
Right  click on Trusted certificates -> Import trusted certificates

(see Fig IIIc1_1)

(see Fig IIIc1_2)

Now import the certificate generated from the Wallet request:

Import user certificate and select the OHS certificate signed by our CA Authority.

A successful import will show the status Certificate ready

(see Fig IIIc1_3)

Save the Wallet (the password that we have setup during the initial creation is planning99)

Once it is saved, set the auto login to ON.

(see Fig IIIc1_4)

Now that the CA certificate and the OHS certificate have been imported, import the other certificates:

(see Fig IIIc1_5)

Import Certificate from server Vmbntalley64

(see Fig IIIc1_6)

Import Certificate from server Bachirlnx2

(see Fig IIIc1_7)

Once the wallet has been saved, you would need to make the following changes to some of the configuration files:

Go to the following file location on the OHS server
/Oracle/Middleware/user_projects/epmsystem1/httpConfig/ohs/config/OHS/ohs_component

Edit the file ssl.conf and make the following changes:
Set the Listen to the desired SSL port to be used and the Vistual Host Context

# OHS Listen Port
Listen 20443
- Set the virtual Host context
*******************************************************************
##
## SSL Virtual Host Context
##
NameVirtualHost Bachirlnx2:20443
<VirtualHost Bachirlnx2:20443> <IfModule ossl_module>
# SSL Engine Switch:
# Enable/Disable SSL for this virtual host.
SSLEngine on
SSLProxyEngine On

# Client Authentication (Type):
# Client certificate verification type and depth. Types are
# none, optional and require.
SSLVerifyClient None

# SSL Ciphis Suite:
# List the ciphiss that the client is permitted to negotiate.
SSLCiphisSuite SSL_RSA_WITH_RC4_128_MD5,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_
WITH_DES_CBC_SHA,TLS_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_256_CBC_SHA
# SSL Certificate Revocation List Check
# Valid values are On and Off
SSLCRLCheck Off

#Path to the wallet
SSLWallet "/u01/OHS_WALLET"
SSLProxyWallet "/u01/OHS_WALLET"
***********************************************************************

Save and Restart the OHS server and test the OHS SSL by launching the following url

https://OHSserver:20443/

OHS loads in SSL and is happy with the certificate.

(see Fig IIIc1_8)

IV. OHS Webserver Configuration/Re-Configuration           Now that all components are setup for SSL, we need to configure the OHS webserver to complete the configuration.
The configuration utility has to be run from the OHS server to be used as a webserver:

(see Fig IV1_1)

(see Fig IV1_2)

Start OHS once the configuration is finished. You can start OHS via the Windows services if on Windows.
If on Linux then you can use the following command:
CD to /home/oracle/Oracle/Middleware/user_projects/epmsystem1/httpConfig/ohs/bin
run ./opmnctl startall

Launch the workspace url and here we are all Configured EPM products are available and communicating in Full SSL:

(see Fig IV1_3)

V. Additional Configurations
a) HFM
                            One issue to be aware with HFM is that the reverse proxy with IIS does not work as shown below when trying to access an application via workspace:

(see Fig Va1_1)

To resolve this issue, you would need to disable the SSLSessionCache in the file
/home/oracle/Oracle/Middleware/user_projects/epmsystem1/httpConfig/ohs/config/OHS/ohs_component/ssl.conf
Disable the existing SSLSession... parameters and add the parameter SSLSessionCache none

# SSLSessionCache "shmcb:${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}${COMPONENT_NAME}/ssl_scache(512000)"
# SSLSessionCacheTimeout 300
SSLSessionCache none
Restart OHS and this time it should work:
                  (see Fig Va1_2)

b) EAS

- On the Server where the EAS console is installed, Go to the following directory Oracle\Middleware\EPMSystem11R1\products\Essbase\eas\console\bat and edit the file admincon.bat
Add a reference to the keystore EPMStore.jks used by the web applications :

set JAVA_OPTIONS=-client -Xmx256M -DEPM_ORACLE_HOME=%EPM_ORACLE_HOME% -Djava.io.tmpdir=..\temp -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration
%EAS_JAVA_OPTIONS% -Djavax.net.ssl.trustStore=E:\EPM\EPMStore.jks
- save
- You should now be able to login to the EAS console in https
(see Fig Vb1_1)

(see Fig Vb1_2)

NOTE: This procedure could also be used on an already configured non SSL EPM Environment. We have already explained the different options.

Summary

We have just shown in hopefully easy steps how you can configure EPM 11.1.2.x with full SSL.

We started by going breaking down the whole Myth surrounding certificates and certificate authorities by:
- being our own certificate authority
- Generating our own certificate requests
- Signing them with our certificate authority

We then move on to get the our keystores ready and in the right places for the EPM environment and once that was done we were then ready to configure EPM with SSL in easy steps.

Note: The white paper is intended to be used in test environment only but could be used as a reference.

Attachments

EPM11121SSL_Screenshots.doc (8,534.5 KB)

key_cert_gen.zip (1,252.41 KB)

Related