How to Create an Oracle Wallet SSL Certificate Request Containing Subject Alternative Name?

Certificate Problem

I recently had an issue after configuring Oracle HTTP Server SSL for a customer. The customer was using self-signed certificates which they managed and created themselves. I created the certificate request using the Oracle Wallet Manager as per usual and they then sent me the certificates to import into the wallet. Unfortunately the users were seeing this error when they connected to the EPM Workspace, even though the certificates were imported successfully:

Your connection is not private.

Attackers might be trying to steal your information...

NET::ERR_CERT_COMMON_NAME_INVALID

The customer determined that the issue was because the certificate didn't contain a SAN or subjectAltName (Subject Alternative Name) field. Unfortunately, the Oracle Wallet Manager UI doesn't contain a field for the SAN when you create your certificate request:

The Subject Alternative Name (SAN) is an extension to the X. 509 specification that allows users to specify additional host names for a single SSL certificate. The use of the SAN extension is standard practice for SSL certificates, and it's on its way to replacing the use of the common name. 

So, how do we create a certificate request which includes the subjectAltName parameter? 

Windows Command Line to the rescue! Fortunately orakpi, the command line utility for the Oracle Wallet supports the additional subjectAltName  parameter. For a Oracle EPM Hyperion 11.2.x installation it can be found in the bin folder of your Oracle Client install e.g. Oracle\Middleware\dbclient64\bin

Create the Wallet Using ORAKPI

You will need to set the JAVA_HOME in your command line session before you run the command. To create the wallet using orakpi run the following command:

orapki wallet create -wallet D:\Oracle\SSL -pwd Password -auto_login

Create the Certificate Request Using ORAKPI

We can now use orakpi to create the certificate request which includes the subjectAltName parameter. First we need to get the main body of the distinguished name (DN) of the certificate request. We can use the Oracle Wallet Manager to create this for us by entering the information in a dummy certificate request and then just copying the DN string (close and don't save afterwards):

Copy the DN entry and you can paste this into your command line entry.

orapki wallet add -wallet D:\Oracle\SSL -dn "CN=hyperion.brovanture.com, OU=IT Services, O=Brovanture Ltd, L=Manchester, ST=Manchester, C=GB" -keysize 2048 -addext_san DNS:hyperion.brovanture.com

Notice the last part of the command. This is where we have added our SAN parameter, the one we couldn't add when generating using the Oracle Wallet Manager UI.

You can now go back to using the Oracle Wallet Manager UI to manage the export of the certificate request containing the SAN and import of the new certificates once you receive them. You won't be able to see the SAN entry in the UI but don't worry, it's there just not displayed.

EPM 11.2.8 FDMEE ODI Upgrade Issue

I encountered an issue this week when upgrading an 11.2.2 EPM environment to version 11.2.8. 

Note that is is only an issue when using an Oracle database and when upgrading from a version older than 11.2.5.

The problem occurred at the "Manual Configuration Tasks for FDMEE" task where you  run the RCU Upgrade Assistant (UA.bat) to manually upgrade the version of ODI used by your FDMEE instance. I got the following errors:

• The specified database does not contain any schemas for Oracle Data Integrator or the database user lacks privilege to view the schemas.
• Cause: The database you have specified does not contain any schemas registered as belonging to the component you are upgrading, or else the current database user lacks privilege to query the contents of the schema version registry.
• Action: Verify that the database contains schema entries in schema version registry.  If it does not, specify a different database.  Verify that the user has DBA privilege. Connect to the database as DBA.

The problem is that the UA assumes that we used the RCU to create our ODI repository which is not the case. In the EPM world your ODI repository is created inside your FDMEE repository when you configure FDMEE. 

The UA will look at the SYSTEM.schema_version_registry table to check the version of ODI but it is missing from our table:

So the UA errors with the messages listed above.

You also see the following error message if you try and connect to the ODI repository using the ODI Studio:

• ODI-10179 / ODI-26168: Client requires a repository with version 05.02.02.09 but the repository version found is 05.02.02.07

There's a support document (2699076.1) which explains how the ODI version matches with the schema version. We'll use this in our hack later:

• The "05.02.02.07" corresponds to ODI Version 12.2.1.3.0.
• And "05.02.02.09" is for Versions 12.2.1.3.190708, 12.2.1.4.0 and 12.2.1.4.200123.

To get around the issue I ran the RCU to create a bogus ODI repository. This also creates the entry in the SYSTEM.schema_version_registry table necessary for me to run the upgrade assistant:

This creates the entry for ODI in my SYSTEM.schema_version_registry:

However, as you can see above it is pointing to the new bogus ODI repository and the version is incorrect because I used the upgraded EPM 11.2.8 version of the RCU (12.2.1.4.0) to create the entry.

I ran the following SQL to change the schema from EPM_ODI_REPO to my FDMEE schema EPM_FDMEE and changed the version from 12.2.1.4.0 to 12.2.1.3.0:

update system.schema_version_registry set OWNER='EPM_FDMEE', VERSION='12.2.1.3.0' where COMP_ID='ODI';

commit;

Now you can see the schema and version are correct:

I was then able to run the UA successfully:

The SYSTEM.schema_version_registry is then updated with the upgraded information:

The upgrade was successful and I was also able to login to ODI using the ODI Studio as there was no longer a mismatch between client and repository versions.

If I were to run into this again I would just insert the ODI row into the SYSTEM.schema_version_registry table directly using SQL with the correct schema and version rather than running the RCU and then editing.

Happy upgrading!