Configuring HFM server to use SSL database connections

Adding the database's certificate to the truststore on the HFM server(s)

The following steps must be performed on each and every EPM server where the HFM datasource runs. The %MW_HOME% environment variable used below is the location of the Oracle Middleware installation. This environment variable is not created by default during the EPM installation and is used here to show the parent directory of the EPM installation.

The location of the EPM installation is specified by the EMP_ORACLE_HOME environment variable. The example below places the keystore and truststores in a directory co-located with the EPM installation. The keystore and truststore files can be located anywhere on the HFM server's filesystem.

Create a new directory under %MW_HOME% to store the Java keystore and PKCS12 truststore.
1. cd %MW_HOME%
2. mkdir certs
Copy the Java keystore file cacerts from the JDK.
1. cd %MW_HOME%\certs
2. copy %MW_HOME%\jdk1.8.0_181\jre\lib\security\cacerts testing_cacerts
  The reason for copying the JDK's keystore and using it instead of the JDK's default keystore is that if the JDK is upgraded and the previous JDK gets deleted, the keys and certificated inserted into the default keystore will be lost.
Copy the Base 64 certificate to %MW_HOME%\certs.
Import the certificate into the Java keystore file testing_cacerts.
1. For example, keytool -importcert -file bur00cbb-certificate.crt -keystore testing_cacerts -alias "myserver"
  1. You will have to specify the password for the keystore.
  2. You should replace "myserver" with the fully qualified domain of the database server.
2. When you are prompted with a question of whether or not the certificate should be trusted, specify y.

Create the truststore in PKCS12 format from the JDK's Java keystore file. For example,

keytool -importkeystore -srckeystore testing_cacerts -srcstoretype JKS -deststoretype PKCS12 -destkeystore testing_cacerts.pfx

Updating the HFM JDBC connections to use SSL

Reconfigure the HFM database JDBC connection to use SSL.
1. Launch the EPM Configuration tool.
  1. Select the Configure Database and Deploy to Application Server nodes under the Financial Management node.
  2. Click Next.
  3. Perform each of these steps for the HFM JDBC connection
    1. Enter the SSL port, Service name, username and password for the connection in the port, service name, username,and password columns.
    2. Click ( + ) to open the Advanced database options.
    3. Select Use secure connections checkbox.
    4. Enter the location of the Java keystore created in Step 2.
    5. Click Apply.
    6. Click ( + ) to open the Advanced database options.
    7. Click Edit and use modified JDBC URL. Note that no changes should be made to the displayed JDBC URL.
    8. Click Apply.
    9. Click Next.
2. Go through through the remaining steps to deploy the HFM application as described in the EPM documentation.
Open a command window or shell to manually update the EPM registry so that the ODBC connection used by the DataSource can be SSL enabled.
Execute each of the commands listed below:
```
epmsys_registry.bat addproperty FINANCIAL_MANAGEMENT_PRODUCT/DATABASE_CONN/@ODBC_TRUSTSTORE "C:
\Oracle\Middleware\certs\testing_cacerts.pfx"
epmsys_registry.bat addencryptedproperty FINANCIAL_MANAGEMENT_PRODUCT/DATABASE_CONN
/@ODBC_TRUSTSTOREPASSWORD <truststorepassword>
epmsys_registry.bat addproperty FINANCIAL_MANAGEMENT_PRODUCT/DATABASE_CONN
/@ODBC_VALIDATESERVERCERTIFICATE false
```
In the above examples, the path C:\Oracle\Middleware is the value of %MW_HOME% in steps 1,2, and 3.

The property FINANCIAL_MANAGEMENT_PRODUCT/DATABASE_CONN/@ODBC_VALIDATESERVERCERTIFICATE should only be set to false if a self-signed certificate is being used. The value of the FINANCIAL_MANAGEMENT_PRODUCT/DATABASE_CONN/@ODBC_TRUSTSTOREPASSWORD should be the password of the original Java keystore copied in Step 2.

Update the TNS names entry used by HFM

Edit TNSNAMES.ORA to create a new entry and rename the old entry. The following example shows an updated TNSNAMES.ORA file on the HFM server that has the necessary changes applied. The reason for these changes is that HFM looks for and uses a TNS names entry named HFMTNS. This entry must have the protocol and the port changed for XFMDataSource to work properly.


HFMTNS_UNENC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myserver_service)
(SERVER = DEDICATED)
)
)
HFMTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = myserver)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = myserver_service)
(SERVER = DEDICATED)
)
)

The original HFMTNS entry has been renamed HFMTNS_UNENC. The new HFMTNS was made by copying the HFMTNS_UNENC entry, renaming it HFMTNS. The protocol was then updated to TCPS and the port was changed to 1522. The port specified must be the same port specified in the TNS LISTENER.ORA file.