Announcing the AutoEPM Patch Tracker

UPDATE: This service has been discontinued; this post is left as historical reference. If you need this sort of thing, contact me on LinkedIn.

With the recent release of EPM 11.2, I thought it would be a good time to make public a tool I've built and used "behind the scenes" for quite some time, the AutoEPM Patch Tracker.

It's a patch tracker focused on EPM products, automatically aggregating updates and pointing to readmes, hopefully a bit faster to peruse than My Oracle Support. It also recommends Critical Product Updates that can be applied to the EPM stack, and the most common "standard" updates (only for 11.1.2.4 at the moment). With a free account via LinkedIn sign-on, all lists can be downloaded in CSV, JSON, and XML (Atom).

It's only a small showcase of what we can do at AutoEPM by Targlet. If you'd like to find out how to integrate this level of automation and intelligence to your EPM enviroments, feel free to reach out! I'll be at OpenWorld London next week as well, always happy to have a chat.

Oracle Hyperion EPM 11.2 - first impressions

Last month, Oracle finally released the long-awaited on-premises release of EPM 11.2, rumoured to be the last hurrah for the venerable suite. After a couple of test installations, I have a few thoughts from an infrastructure perspective.

First, the bad news:

• This is very much a .0 release, in many ways the first since 11.1.2.0 (the last time so much of the infrastructure had substantially changed). It is rough in quite a few places, and configuration is not for the faint of heart.
• I have encountered what, to me, looks like a blocking bug in multi-server configurations. I filed a support request with Oracle and will see how it goes. At the moment I simply wouldn't be able to recommend it in situations where high-availability is necessary.
• Even after configuration, it is harder to operate by non-techies than in the past. Even the "start" and "stop" links provided, which used to be ok for single-machine use at least, are not sufficient anymore - some extra scripting will be necessary to make it useable even in the most basic scenarios.
• Documentation has not fully caught up with 12c, particularly in the area of additional configuration tasks for Financial Close Manager (which was already pretty lacking). Some of the additions, to deal with the now-necessary Repository Creation Utility, are ambiguous.
• Weblogic seems hungrier for memory than in previous releases. This might be due to 12c, or to the fact that it's now using Java 8 under the hood - I had already measured an increase in this area when replacing Java 6 with 7 on 11.1.2.4.
• Essbase seems to have been shipped almost unmodified from the latest 11.1.2.4 version. Among other things, this makes it currently impossible to do a full-SSL configuration if Essbase is in the picture. It's disappointing, particularly considering how Essbase customers are typically among the few who often require fully-encrypted solutions.
• The documentation seems to state that running any "Configure Database" task more than once will end in failure. I suspect this is true only for certain components, but I have not had the time yet to explore this area. This makes it even harder than before to migrate databases after configuration, so make sure you take design choices that will stand the test of time.
• EPMA lives! Well, not really - the product is gone, but somehow it's still avilable in the Provisioning screen and you can assign roles for it. 🤷🏻‍♂️
• This is the end of the line for IIS stalwarts. Using IIS as front-end webserver will simply not work (according to docs).
• It's not particularly clear whether Oracle 18c and 19c are officially supported as database. In practice they seem to work (after all, they are really 12.2.0.2 and .03 rebranded), but it would be nice if the matrix was clarified.

But there are also some good news!

• The products themselves are basically the same, with the occasional extra feature. Users shouldn't need any substantial retraining, with the notable exception of any EPMA or Interactive Reporting power-users still around.
• The 12c stack, in theory, unlocks a number of attractive possibilities for system administrators and implementors - containers, no-downtime patching, and so on. It will take some time to fully unpack how EPM can take advantage of these goodies.
• Recent Windows Server releases are supported.
• In my test, everything seems to work even with the free editions or Oracle and SQLServer (XE 18c and Express 2017), which is nice for consultants and other professionals.
• Both 12c and Java 8 support modern crypto standards, which should make things less awkward when risk-assessing and pentesting.
• Java 8 brings a lot of improvements to the language and the ecosystem. Whole classes of problems have been removed (MaxPermGen, anyone?), which should make services more reliable and performant. Custom integrations will also be easier and faster to develop and run.

In the past, I would have expected such a release to be followed by a quick stream of fixes, necessary to make it actually fit for production use. We will have to see if it is going to be the case here, considering how long it took to debut and the overall tone of the Oracle roadmap (mostly cloud-focused). Should Oracle really commit to improving the on-premise, this release would soon prove itself as a substantial improvement over 11.1.2.4.

If you'd like to know more and discuss your options in the EPM space, make sure to get in touch with us.

HFM 11.1.2.4.102 still insecure

In the last few months, I've described how HFM 11.1.2.4 is not working in secure configurations (i.e. does not support encryption/SSL):

• Hyperion Financial Management (HFM) 11.1.2.4.0 cannot be fully secured
• Financial Management 11.1.2.4.101 still cannot be secured

It looks like the recently-released patch .102 still does not fix this glaring omission. Clearly security is very low in Oracle's list of priorities (but I'm sure their cloud setups are really really secure, uh-uh...).

Anyway, in the previous posts I recommended to work around this problem by having all HFM components on one single well-firewalled box. This setup was already sub-optimal (it's a single point of failure, and of course it might not meet some workload requirements), but as I went through other items it became clear that it's even more untenable than I previously thought.

This is because 11.1.2.4 components integrating with HFM (Financial Reporting, Calculation Manager, OBIEE and so on) will talk directly to application and cluster processes, bypassing the Weblogic-based web-application. Because of the previously-mentioned bug, communication will be completely unencrypted.

This means that theoretically, if any component uses the HFM API to integrate, it would have to run on the same single box as HFM.

The only exceptions to this rule are:

• the EPM Architect Dimension Server service, which will go through the web-app for its own calls (all related to metadata, like deployment, lookups etc). However, EPM Architect's own DataSynchronization service (which can automatically copy data across EPM products) will again go directly to appserver processes without encryption.
• webservices-based products like Financial Close Manager, Tax Governance etc (i.e. products built on Oracle SOA). These integrate with HFM via its web service interface exposed by Weblogic, which can be easily secured with SSL.

Bonus: a teaser

If you feel adventurous, you can figure out why HFM does not work with SSL.

1. Create the EPM Registry properties mentioned in logs as reported in my first post.
2. Run Process Monitor while you start the HFM JavaServer process.
3. In the resulting capture, look for "File Not Found" containing "user_projects" in path. One of these files should look familiar...

Cluster strategies in Oracle Hyperion Financial Management

While poking around HFM for other reasons, I’ve found a bunch of interesting constants...

• STR_HITREG_SERVER_STRATEGY = "ServerStrategy";
• STR_HITREG_SERVER_STRATEGY_ROUNDROBIN = "0";
• STR_HITREG_SERVER_STRATEGY_RANDOM = "1";
• STR_HITREG_SERVER_STRATEGY_LOADBASED = "2”;

From other code and messages, it looks like ServerStrategy is supposed to be a property of the HFM database node in EPM Registry. It’s retrieved on startup, and will always default to 0 (”round robin”) because the property is not actually there unless you manually create it. It lseems to govern the load-distribution strategy employed by clients (i.e. the HFM web application), i.e. where to send each new login if the user has not logged on before and/or the documented StickyServer option is not enabled.

Don't raise your expectations anyway: it looks like the ”load based” strategy (2) has not actually been implemented yet, and if you choose it you’ll actually get the Round-Robin one; whereas ”Random” (1) is really random.

From what I can gather, Round-Robin will follow the list of servers specified in property serverList of the cluster node in EPM Registry, going through the list in the specified order. It’s also entirely in memory and never saved to disk or DB, from which we can deduce that:

• each webapp or client will do its own thing, with no coordination between them;
• each webapp or client will forget everything on restart;
• each webapp will default to Round-Robin strategy and send logins to each appserver in the order specified by serverList

The main advice at this point is hence to keep your beefier appservers at the beginning of serverList, since they're more likely to receive traffic. Alternatively, add ServerStrategy and trust the random algorithm to distribute logins... randomly ;)

I suspect this stuff is a prelude of things to come, because it’s used in sections of code that also deal with SSL initialisation for communication with the appserver, i.e. something that is not quite baked yet but must have been added recently. It’s also pure Java, so can’t be older than 11.1.2.2. Does that mean we’ll finally get real load-balanced clustering in HFM (more than 10 years after people asked for it)…?

Financial Management 11.1.2.4.101 still cannot be secured

I was hoping the recent wave of patches (.100 and .101) for HFM-- sorry, Oracle EPM Financial Management 11.1.2.4 would have started to address those well-known security problems that have been plaguing this (otherwise remarkable) release. Unfortunately, that is not the case.

To begin with, the server component is still looking for the wrong properties in registry ("isSSL" and "SSL_Port" rather than the actually existing "isSslEnabled" and "ssl_port"). This means that, whichever option you select in Configuration Utility, the component will default to ssl-disabled.

If we try to trick it, by manually adding the properties it's looking for, we get the following errors and the service shuts down:

[...] [FM] [ERROR] [] [oracle.FM.HSX.SERVER.oracle.epm.fm.hsxserver.HsxServer] [...] 
[SRC_CLASS: oracle.epm.fm.hsxserver.HsxServer] 
[SRC_METHOD: startThriftServices] An error occurred while starting HSX_SERVER_NAME service.[[

org.apache.thrift.transport.TTransportException: Either one of the KeyStore or TrustStore must be set for SSLTransportParameters

at org.apache.thrift.transport.TSSLTransportFactory.getServerSocket(TSSLTransportFactory.java:99)
at oracle.epm.fm.common.BaseSeviceManager.getSSLServerTransport(BaseSeviceManager.java:121)
at oracle.epm.fm.common.BaseSeviceManager.startService(BaseSeviceManager.java:68)
at oracle.epm.fm.hsxserver.HsxServer.startThriftServices(HsxServer.java:64)
at oracle.epm.fm.hsxserver.HsxServer.init(HsxServer.java:43)
at oracle.epm.fm.hsxserver.HsxServer.main(HsxServer.java:27)
]]

[...] An error occurred while starting HSX_SERVER_NAME service.[[
oracle.epm.fm.common.exception.HFMException: EPMHFM-66019: An error occurred while starting HSX_SERVER_NAME service.
at oracle.epm.fm.hsxserver.HsxServer.startThriftServices(HsxServer.java:70)
at oracle.epm.fm.hsxserver.HsxServer.init(HsxServer.java:43)
at oracle.epm.fm.hsxserver.HsxServer.main(HsxServer.java:27)
]]

As you can see, it complains about missing keystores. This was always a possibility, since we never got a chance to specify such a thing during configuration; however, most SSL-aware software usually ships with demo keys preconfigured for tests, so it was not unreasonable to expect HFM would do the same. No such luck!

Hopefully both problems will be comprehensively addressed sooner rather than later. As it is, secure 11.1.2.4 implementations still have to rely on both HFM web and app components running on a single box with a tight firewall, which is far from ideal.

Bonus: Peeking Under The Hood

These errors lift the veil on some of the development strategies Oracle employed to remove hard dependencies between HFM and Windows-specific DCOM.

It looks like they are using Apache Thrift, which can be roughly described as a serializer library to auto-generate network APIs for existing programs. They basically dropped DCOM network code and replaced it with a simpler network interface built on (if my speculation from the previous post is correct) some Weblogic subcomponent; this interface uses Thrift-generated serializations to pass data between appserver processes and the ADF-based web application.

In theory, such an approach could make it easier for third-party components to open connections directly to the HFM appserver, bypassing the need for specific client libraries (with their byzantine requirements) and just using Thrift to figure out necessary calls. However, I'm not really an expert in this field and I don't really know what this effort would entail; at this point, your best bet for custom integration is still likely to be the official HFM SDK.

Hyperion Financial Management (HFM) 11.1.2.4.0 cannot be fully secured

(If you don't know what HFM is or does, look away now!)

If you work in the Oracle EPM space, you know that the long-awaited version 11.1.2.4.0 has finally been released, without much fanfare. For HFM, this release marks a major turning point: core services have been deeply modified in order to make them run on Unix/Linux, and ASP.Net was entirely removed from the stack, so that HFM can be officially supported on Exalytics system running Oracle Linux.

HFM is built on the Microsoft C++ stack, so it was an arduous task to make it portable without throwing away a decade of development efforts. To Oracle's credit, the mission was basically accomplished; however, *nix shops will likely not be too eager to deploy 11.1.2.4.0 in production environments just yet -- nor will traditional HFM customers. This release is rough, with quite a few functional bugs (taskflows cannot be displayed; the interface does not fully work in IE11; and so on), and unfortunately security was another casualty.

If you enable SSL for internal communication between EPM components, all services should encrypt their network traffic. In order to do that, they have to be manually configured, installing identity certificates required by the SSL protocol; this is a standard operation in Weblogic, and documented for other components in the Security Configuration Guide. Unfortunately, the HFM application server:

1. is not a Weblogic instance (at least not officially -- will clarify this later)
2. is not documented in the Guide yet.

Straight out of the gate we have a problem -- what certificate will HFM use? Nevermind, let's assume there is one hardcoded somewhere (shock, horror!). We simply enable full-SSL in Common Settings:

Then activate SSL support in the HFM Application Server configuration task:

And we can see in the EPM Registry that these settings have gone in:

We then configure the rest of the stack to use SSL (which is quite trivial, these days), increase logging here and there (good practice when enabling SSL, since it helps with troubleshooting), restart our services and... it all works! Great! Hold on, what is this in the HFM log?

[2015-02-06T01:11:05.087+00:00] [FM] [ERROR] [EPMHFM-65559] [oracle.FM.HSSUTIL.oracle.epm.fm.hssservice.RegistryWrapper] [tid: 10] [ecid: 0000KhSSAzEEgKYjLpqIOA1Kp1Ib000000,0] [SRC_CLASS: oracle.epm.fm.hssservice.RegistryWrapper] [SRC_METHOD: getRegistryProperty] Invalid property isSSL for HIT Registry component app24. [2015-02-06T01:11:05.132+00:00] [FM] [WARNING] [] [oracle.FM.HSX.SERVER.oracle.epm.fm.hsxserver.service.HsxServerServiceManager] [tid: 10] [ecid: 0000KhSSAzEEgKYjLpqIOA1Kp1Ib000000,0] [SRC_CLASS: oracle.epm.fm.hsxserver.service.HsxServerServiceManager] [SRC_METHOD: init] An error occurred retrieving property isSSL from EPM registry, using default value :false. [2015-02-06T01:11:05.141+00:00] [FM] [ERROR] [EPMHFM-65559] [oracle.FM.HSSUTIL.oracle.epm.fm.hssservice.RegistryWrapper] [tid: 10] [ecid: 0000KhSSAzEEgKYjLpqIOA1Kp1Ib000000,0] [SRC_CLASS: oracle.epm.fm.hssservice.RegistryWrapper] [SRC_METHOD: getRegistryProperty] Invalid property SSL_Port for HIT Registry component app24. [2015-02-06T01:11:05.141+00:00] [FM] [WARNING] [] [oracle.FM.HSX.SERVER.oracle.epm.fm.hsxserver.service.HsxServerServiceManager] [tid: 10] [ecid: 0000KhSSAzEEgKYjLpqIOA1Kp1Ib000000,0] [SRC_CLASS: oracle.epm.fm.hsxserver.service.HsxServerServiceManager] [SRC_METHOD: init] An error occurred retrieving property SSL_Port from EPM registry, using default value :9092.[...] [SRC_METHOD: init] isSSLEnabled :false

Ouch, it looks like HFM is just ignoring our settings. Old-hat EPM infrastructure hackers at this point are probably thinking "Hold on, I know those 'not found' properties; they're usually seen on web-app nodes!" and that's exactly it. The HFM server fancies itself a web-app, and looks for web-app properties in its registry node; but that node is actually a custom non-standard type with non-standard properties "isSslEnabled" and "ssl_port" (lowercase).

To verify that HFM was not encrypting anything, I dusted off my miserable Wireshark skills and logged some network traffic.

As you can see in the screenshot, data originating from the HFM application server and using ports in the range used by HFM, are sending data down the wire completely unencrypted. Malicious actors could hide somewhere in your company network and silently siphon away all your precious financial data with minimal effort. If you care about security, you'll likely want to give this HFM release a wide berth, at least until this bug is fixed.

Bonus: The Strange Case of The Masqueraded Weblogic

As I mentioned above, the HFM application server is not officially a Weblogic instance. So why is it trying to look up registry properties typical of Weblogic instances? Maybe because it is, in fact, a bastardised Weblogic. If we look at the startup parameters configured in Windows Registry, we find the following:

What's that? A Weblogic parameter? Indeed it is. And why is it there? By default, Weblogic will use the cryptographic extensions found in the JDK (or JRockit) in which is running. By setting "nojce" to True, you can tell Weblogic to actually use its internal implementation of such extensions (which were probably developed by BEA back in the days, when crypto support in the JDK was a bit shaky). If you are bastardising weblogic, I guess you can also replace some of these classes with your custom versions, which is probably what Oracle was trying to do here.

"Securing EPM" presentation at UKOUG Apps14

The slides for the "Securing EPM" presentation I've given at UKOUG Apps14 in Liverpool two weeks ago, are now available here. Merry Christmas!

If you are interested in Oracle EPM infrastructure...

... then you might want to pop in at UKOUG Apps 14 in Liverpool, on 10 December, to hear me describe the sad state of security in our little niche and how we can improve it.

I promise that I won't bamboozle you with security nerdspeak; it will mostly be an overview of "things EPM customers should ask for but somehow never do". Remember to pack your tinfoil hat!

Oracle ODBC Connection Strings - how I learnt to stop googling and RTFM

I just wasted four hours on the most idiotic thing, so I thought I'd document it here as self-reference.

Background: to connect to some Oracle db, I'm using the excellent pypyodbc module, which is a pure-Python ODBC implementation - basically a not-so-thin layer on top of your installed ODBC providers - that works great with Python 3. If you have to support multiple database vendors (in my case, Oracle, MSSQL, DB2 and maybe others), it makes sense to avoid packing a module for each product and just let ODBC work its magic.

The main problem with ODBC has always been the dark magic involved in crafting connection strings. Each driver provides different options, and when the syntax is not correct, in most cases there is precious little feedback. This is why we have sites like connectionstrings.com.

In my case, the connection string I was using worked fine with TNS names (the stuff in tnsnames.ora) like this:

Driver={Oracle in OraClient11g_home1};DBQ=myTnsServiceName; Uid=myUsername; Pwd=myPassword;

However, I did not want to rely on that particular catalog (which is often misconfigured/broken in the real world), and would rather specify the usual host, port and sid trimurti. So I went on connectionstrings.com and found the following:

Driver={Oracle in OraClient11g_home1}; Server=serverSID; Uid=myUsername; Pwd=myPassword;

... and then I spent four hours figuring out why it wasn't working. I turned on all tracing options, spent ages reading tracing logs, tried umpteen different values for SERVER... all for nought: from logs, it was clear that my SERVER option was completely disregarded and replaced with some default "orcl" values.

Desperate, I eventually thought of daring the (usually unwieldy) original driver documentation from Oracle. And lo, I've found in the FAQ doc for Oracle ODBC, on page 13, a very helpful table listing all the options you can specify in a connection string. "SERVER" was nowhere to be seen. Ouch.

It turns out the trick was to keep using "DBQ" and just replace it with the standard Oracle network syntax:

Driver={Oracle in OraClient11g_home1}; DBQ=myserver.mydomain.com:1521/mySid; Uid=myUsername; Pwd=myPassword;

In the end, I wasted 4 hours because I thought googling would have been faster than Reading The Fine Manual. Lesson learnt.

Oracle XE 11.2.0.2 + Oracle Client 11.2.0.1 = cannot "connect / as sysdba"

While having fun with some complicated (and likely illegal!) scenarios I won't go into now, I ended up this morning with an Oracle XE database to which I wanted to connect with the usual DBA routine:

sqlplus /nolog
connect / as sysdba

I kept getting the dreadful "ORA-12560: TNS:protocol adapter error" and couldn't understand why. Surely I didn't break it that much? The service was still up and I could connect regularly in every way except that.

It turns out this is what happens when you try to do too many things on the same machine. I had installed an Oracle Client on top, because I needed a few things from there (OleDB provider etc) and because other products are guaranteed to work with that client rather than XE.

The problem is that the sqlplus version that comes with the Client is older (!) than the one from XE, but because of how the Windows PATH system variable gets manipulated by the installer, the old version takes over and gives you this problem.

Solution? In your PATH, make sure the folder from the oraclexe directory comes before the one from any client you might have there. However, understand that this is likely to break your Client, so either make sure the change is temporary, or set it locally only when you need it (batch files!).

Oracle (Red Hat) Enterprise Linux 5 on VMware Workstation 5.5.x

Just wasted an hour trying to understand why VMware Tools wouldn't properly fix the X server on Oracle "Unbreakable" Enterprise Linux 5. The answer is: because the Tools coming with VMware Workstation 5.5 don't really work with such a modern distribution. So the answer was:

1. get hold of a newer release (Workstation 6.0.2 did it for me)
2. find the file "linux.iso", and copy it across to the old Workstation (you might want to rename/backup the original, just in case) in the same position.
3. perform a regular installation of VMware Tools. It should correctly detect your X.Org 7.1 and fix it properly. (Note: if you get errors coming from VFS, it means that VW was too quick in unmounting the drive. Remount it, then copy the rpm to the local filesystem before reinstalling.)

If you haven't got a newer version of VMware, try adding the following lines inside the "Monitor" section of your /etc/X11/xorg.conf:

HorizSync 1-10000
VertRefresh 1-10000

It might just be enough.