Encrypting Connections in Excel Add-in and Jedox Web Environment

Step 1: Encrypting a Standalone Installation

Step 2: Encrypting Jedox In-Memory DB

Excel Client Configuration

In Excel, the complete certificate chain is necessary to get access to the server. Make a copy of the complete certificate chain (root certificate, root CAs, and intermediate CAs) and name it client.pem. Then move the file to <Install_path>\xladdin\cert\

When creating a connection, use the correct server address that matches the CN used in the server certificate. If a wildcard certificate is used (such as *.jedox.local), the intermediate certificates must be added to the client.pem in addition to the root certificate. Root and intermediate certificates can be found in the ca_bundle.pem, as described in and Encrypting a Standalone Installation.

Note: Direct connection via TLS (port 7778) to OLAP is now supported:

• On legacy cloud systems: it is possible to set an HTTPS address, and an HTTPS port for OLAP in Excel connections. The connection then should be made only on the HTTPS interface.
• On Cloud AKS environments: the connection works the same as before.

Changes in the Jedox Web Environment

After securing the communication from the server side, some mandatory changes in the Jedox Web environment are necessary.

Jedox Web Macros

For PHP macros using SOAP calls to Jedox Integrator, the correct port and protocol of the Tomcat Service must be used to establish a connection to Jedox Integrator. For example:

$server = @new SoapClient
('https://www.example.com:8443/etlserver/services/ETL-Server?wsdl', 
array('exceptions' => true) );

Note that this change is only required if the Integrator connection is encrypted.

Jedox Connections

Jedox connections need to point to the correct DNS when OLAP is running with optional or required encryption.

1.) In the Administration area of Jedox Web, open Connections and select the connection you’d like to edit:

2.) Change the host settings to match your certificate:

Note: the address specified here must match the connection that is defined in config.php. If the IP/DNS differs from the one set in config.php, then all other connections will be treated as additional connections (which use more license seats), and the localhost connection won't work at all.

Additional OpenSSL commands

To verify the certificate chain:

openssl verify -CAfile certificate-chain.crt certificate.crt

If the response is OK, the check is valid.

To verify that the public keys contained in the private key file and the certificate are the same:

openssl x509 -in certificate.crt -noout -pubkey
openssl rsa -in certificate.key -pubout

The output of these two commands should be exactly the same.

To verify that the private key and public key are a key pair that match:

Use the command below to view the modulus of the certificate:

openssl x509 -noout -modulus -in cert.pem | openssl md5

The modulus looks something like this: a77c7953ea5283056a0c9ad75b274b96. Next, use the following command to view the modulus of the private key:

openssl rsa -noout -modulus -in priv.key | openssl md5

This modulus of the private key should be identical to the modulus of the certificate.

To check whether the date of the certificate is valid:

openssl x509 -noout -in certificate.crt -dates

To extract the content of a PKCS12 container:

openssl pkcs12 -in cert.p12 -clcerts -nokeys -out cert.pem
openssl pkcs12 -in cert.p12 -cacerts -nokeys -out root.pem
openssl pkcs12 -in cert.p12 -nocerts -out private-key.pem

To convert a PKCS7 package into a server.pem file for Jedox:

A PKCS7 contains certificates + root chain + root certificate (if selected in the extract).

openssl pkcs7 -inform DER -outform PEM -in certificate.p7b -print_certs > certificate_bundle.cer

Updated September 27, 2022