Custom JDBC URL for Hyperion Planning in Full SSL

One thing that's lacking in Oracle's documentation and knowledge base is how to correctly configure a Hyperion Planning data source when your database server only permits SSL connections.  This has led to frustrating searches through online blogs, Microsoft documentation (in the case when our database is MS SQL Server), and so on.

Here is the underlying problem:

Once your DBA has configured the back-end relational database to only accept SSL client connections, it is impossible to create (or use) a data source within Hyperion Planning's data source administration screen.  This is because, unlike the EPM System Configurator, there is no checkbox for enabling SSL, and no input boxes to specify your Java keystore and password.  This means we have to click the checkbox for a custom JDBC URL, and we must figure out the syntax ourselves.

After reaching multiple dead-ends, I stumbled upon the solution.  Because I had already gone through the steps of configuring SSL for the various databases, and I deployed the applications to WebLogic, the JDBC URL syntax we need to emulate has already been provided!

Solution

On any of your EPM servers where you've deployed to WebLogic, navigate to this folder:

Open any one of these *.jdbc.xml files and examine it.  Assuming the JDBC data source you're examining has been correctly configured for SSL via the EPM System Configurator, you'll have the exact syntax that's needed for this specific environment.


Let's walk through a specific example.  Suppose we have already created our Java keystore with a default password of "changeme", as per Oracle's documentation within epm_security.pdf, and we've saved it as:
E:\Oracle\Middleware\ssl\myIdentity.jks

Furthermore, let's say we're using Microsoft SQL Server as our database (2008 or 2012, but you want to be using 2012 by now), and we have empty database named "Vision" for our new Planning data source.

In this scenario, here's the custom JDBC URL we'd copy & paste into the data source creation screen.  Bear in mind, the text you see below is all one line.  I've added linebreaks for ease of reading:

jdbc:weblogic:sqlserver://SQLSERVERHOSTNAME:1433;
  databaseName=Vision;
  ENCRYPTIONMETHOD=SSL;
  TRUSTSTORE=E:\Oracle\Middleware\ssl\myIdentity.jks;
  TRUSTSTOREPASSWORD=changeme;
  VALIDATESERVERCERTIFICATE="true";
  loadLibraryPath=E:\Oracle\Middleware\wlserver_10.3\server\lib

Personally, I find configuring EPM for full end-to-end SSL extremely time consuming and tedious. My preference is to put an SSL offloader in front of Oracle HTTP Server, but some IT organizations are becoming increasingly concerned about hardening their data's security as it passes through their internal network.