Taming your SAP HANA Express. Hardening an SAP HANA Express 2.0 SP03 installation part 1. Getting it ready for SAP Analytics front end tools.
In a nutshell, the SAP Analytics tools (the likes of SAC, Analysis for Office, BW4H, BOE with Lumira 2.x, Analysis for OLAP or WebIntelligence) rely heavily on the secured HTTPS INA protocol to consume the SAP HANA calculation views on-the-fly via the SAP HANA EPM-MDS engine.
This is being referred to as live/on-line/remote connectivity, where the data is always current, as opposed to acquired/off-line/import option where the data needs to be acquired first and then refreshed in a scheduled manner.
I have invested quite some time and effort in order to build a viable testbed with the focus on the front-end SAP analytical applications that rely on the secured HTTP INA protocol based connectivity [either with or without SSO to the backend HANA database] and that can consume the HANA HDI views (e.i. the views which are no longer tied into the default _SYS_BIC schema).
As you may know the SAP HANA Express 2.x ready-to-deploy images implement several self-signed SSL certificates for the secured domains of HTTPS INA (XS classic engine running on the tenant database), XSA (XS Advanced) and LCM access over the web.
As aforementioned, having recently come across the necessity to implement the trusted SSL certificates signed with a trusted CA (Certificate Authority) rather than relying on self-signed ones, I have decided to share my experience in “Taming your SAP HANA Express” series of blog posts.
Let me be clear, I have been very happy with the HXE SAPCAL image deployed on AWS; It takes 45 minutes to have this image deployed and running.
It is a great HANA appliance! Regards to the entire SAPCAL team!
Putting it all together
First I got the HTTPS INA protocol up and running fine on the tenant database where both the index server and webdispatcher are running;
(This is very important as with the multi-tenanted databases there is neither index server nor webdispatcher available to run on the system database.)
This setup used to work against SAP Analytics Cloud waves 18 through 22 out of the box (aka with the self-signed SSL certificates in Chrome browser);
So what… 🙂
1. The initial problem with the HANA Express self-signed certificates.
Initially one particular problem I faced was that a number (but not all) of front-end applications require the CN certificate name to match the hostname in the HTTPS service URL;
When a mismatch between the two names is detected these applications deny any further access.
In our case the CN name is sid-hxe and in the webdispatcher HTTPS URL service name is vhcalhxedb; Obviously sid-hxe != vhcalhxedb !
Eventually, after a thought, I narrowed down the problem to the following piece of advise:
Can someone advise the steps required to replace the system certificate (sid-hxe) with the default.root.crt.pem certicificate (vhcalhxedb) in the webdispatcher’s SAPSSLS.pse store ?
Let me explain it in more details.
The below links to the HANA XS classic engine work perfectly fine on my testbed.
4390 is the SSL port number of the webdispatcher running on the HXE tenant database;
I have noticed the HXE webdispacther URL is using the OS system host sid-hxe SSL certificate as depicted below.
You may notice that the URL uses vhcalhxedb HXE appliance name [which is resolved into the elastic IP address in my laptop’s /etc/hosts file].
Still the webdispatcher/XS classic engine are configured to use the system certificate which shows the OS host name, namely sid-hxe:
Moreover, if I try to replace the appliance’s name vhcalhxedb with the hostname sid-hxe I get the 503 error as depicted below (of course it goes without saying sid-hxe has been added to the /etc/hosts file on my Mac so it resolves to the same IP address as vhcalhxedb):
in other words the name of the HANA host (vhcalhxedb) must not be replaced in the URL;
2. Further on all the XSA applications pre-installed during the appliance deployment or that I have installed myself on either SYSTEMDB or HXE tenant database use a different self-signed domain SSL certificate, namely vhcalhxedb
This vhcalhxedb certificate is in the default.root.crt.pem certicificate file found here: <installation_path>/HXE/xs/controller_data/controller/ssl-pub/router
3. I can access the webdispatcher admin console (on the HXE tenant database)
(You may need to grant specific HANA user privileges in order to get access to the webdispatcher admin console and the user needs to be defined on the tenant database)
The PSE of interest is the SAPSSLS.pse which is configured with the system certificate (CN=sid-hxe);
What I did is that I have imported the aforementioned default.root.crt.pem certificate file into the SAPSSLS.pse store (as shown below).
What I did not know is what to do to replace the Own Certificate (CN=sid-hxe) with the default.root.crt.pem certicificate (CN=vhcalhxedb). Or with any other viable certificate ?
I was trying to follow the series of relevant SAP notes, for instance: https://launchpad.support.sap.com/#/notes/2014996 in order to reconfigure the SSL config (https://help.sap.com/viewer/683d6a1797a34730a6e005d1e8de6f22/7.4.19/en-US/493db10a19341067e10000000a42189c.html)
At the same time I do not want to damage my testbed. So again I narrowed down the problem to the following piece of advise:
Can someone advise the steps to replace the system certificate with the default.root.crt.pem certificate in the webdispatcher’s SAPSSLS.pse store ? Does it make any sense at all ?
For instance the following blog explains how to replace the system certificate with a new certificate.
But let me walk you through all the steps one by one.
2. SSL system certificate with HXE tenant database web dispatcher and XS classic
Long story short: Despite the above certificate/URL names mismatch I was able to use HXE SAPCAL image with SAP Analytics Cloud for some quite time…
Until recently the discrepancy of the HXE Webdispatcher CN name in the SLL certificate and the hostname in the Webispatcher URL was still manageable ; For whatever reason SAC including the the SAC wave 2018.22 disregarded the CN name mismatch and that the certificate was self-signed (not trusted).
However with the latest release of SAP Analytics Cloud (wave 2019.01+) it is no longer case as SAC requires the SSL certificate be both valid and trusted…. (https://launchpad.support.sap.com/#/notes/2482807)
In order to find a manageable solution and a way out of this “cul-de-sac” I broke it down into three questions:
- What needs to be done to get the Webdispatcher SSL certificate right?
- Could I replace the self-signed sid-hxe certificate with the vcalhxedb certificate used elsewhere in the system ? I understand that might solve the name mismatch but would not necessarily fix the certificate trust.
- Last but not least which CA authority could I be using with the SAPCAL images to generate trusted SSL certificates in order to replace the self-signed ones ?
The answer to question 3 would deliver both valid (name match) and trusted (CA-signed) certificates.
Let me walk you through the required steps as follows:
ad 1.What needs to be done to get the Webdispatcher SSL certificate right? To fix the non secure, self-signed HTTPA INA URL ?
ad 2. Could I replace the self-signed sid-hxe system certificate with the XSA domain vcalhxedb certificate used elsewhere in the system ?
ad 3. Last but not least which CA authority could I be using with the SAPCAL images to generate trusted SSL certificates in order to replace the self-signed ones ?
as well as this excellent blog I have already quoted https://blogs.sap.com/2018/01/30/establishing-the-live-hana-on-premise-connection-from-sap-analytics-cloud-using-cors/
If you are interested in more details about the HANA PSEs the following SAP note 2009483 – PSE Management in Web Administration Interface of SAP Web Dispatcher describes the PSE management techniques
Alternatively the below SQL statement allows to retrieve the HANA webdispatcher profile(s)
SELECT KEY, VALUE, LAYER_NAME FROM SYS.M_INIFILE_CONTENTS WHERE FILE_NAME = 'webdispatcher.ini' AND SECTION = 'profile' AND KEY LIKE 'wdisp/system%’ which yields the following result: GENERATED, SID=HXE, NAME=HXE, EXTSRV=localhost:39008, SRCVHOST=vhcalhxedb
The FQDN of the webispatcher FQDN is set to be vhcalhxedb.sap.corp; but this could be any name like ateam.sap.corp etc…
Let’s make sure the FQDN can be resolved into the IP address. The below excerpt shows the edited OS hosts system file:
vi /etc/hosts # # IP-Address Full-Qualified-Hostname Short-Hostname # #127.0.0.1 localhost sid-hxe.dummy.nodomain sid-hxe 127.0.0.1 localhost 10.0.0.11 sid-hxe.dummy.nodomain sid-hxe 10.0.0.11 vhcalhxedb.sap.corp vhcalhxedb Additionally, in order to prevent the SAPCAL image to change the hostname sid-hxe:~ # cat /etc/hostname sid-hxe Prevent the appliance change the hostname at boot time sid-hxe:~ # vi /etc/init.d/updatehosts sid-hxe:~ # sid-hxe:~ # cat /etc/init.d/updatehosts if [ "$1" == "start" ]; then # Commented out the below line to prevent the hostname changes # /sbin/updatehosts.sh else echo "$0 $1 does not do anything" fi exit $? sid-hxe:~ #
3. The hardening of the XS classic domain:
Following the piece of advice from the wiki the CA-signed trusted certificate has been implemented into the PSE SAPSSLS.pse as follows. (You will notice we still use the vhcalhxedb name in the service URL):
I recreated the PSE, created the CSR, namely:
and then imported the CA response (full chain of certificates)
The end result is as follows:
Still the dispatcher parameters reveal the appliance host name as vhcalhxedb and not the FQDN vhvalhxedb.sap.corp ? What has gone wrong ?
Initially I thought that I would have to rename the HANA appliance host, namely rename vhcalhxedb into the FQDNvhvalhxedb.sap.corp .
I do reckon, eventually, this might have been the best way of getting the FQDN work in the URL once for all. And, BTW, there is an excellent post that explains how to rename the HANA host. However, I did not resort to it;
Instead I used the HANA cockpit and searched for all the occurrences of vhcalhxedb in the HANA configuration files through the XSA HANA cockpit at https://vhcalhxedb.sap.corp:51045/sap/hana/cockpit/landscape/index.html
[I have to confess I have already renamed the XSA engine’s domain from vhcalhxedb into vhcalhxedb.sap.corp initially to prove that it can be done, but more importantly that XSA is world apart from classic XS and that the HANA appliance hostname (=vhcalhxedb) is also separate from either XSA and XS engines or the OS host name]
I have found that the public_urls of the index engine (xsengine.ini) were of the form http://vhcalhxedb:8090 and https://vhcalhxedb:4390 so I edited them as follows
Then I restarted the HANA DB appliance.
In the aftermath the FQDN URL to HTTPS INA GetServerInfo can reveal both trusted (CA Signed) and valid (CN==URL hostname) certificate for the XS classic domain:
and that the short-name URL does not work any more:
Equally the FQDN URL to the webdispatcher admin console reveals the valid and trusted certificate
and that the short-name URL does not work any more:
From this moment on I was able to start using the FQDN, namely vhcalhxedb.sap.corp in the SAC connection definition, in the BOE HANA trust definition etc….
In the next instalments of this series I will explain the XSA domain hardening and the use of the HANA HDI views from the SAP HANA Shine XSA application across a variety of the SAP reporting tools….