Encrypting Connections in Excel Add-in and Jedox Web Environment

image_pdfimage_print
Step 1: Encrypting Jedox Web
Step 2: Encrypting Jedox In-Memory DB
 
Excel Client Configuration

In Excel, only the root certificate is necessary to get access to the server. Make a copy of the root certificate 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 Encrypting Jedox Web and SSL Configuration for a Standalone Installation.

 

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

Clustering:

Jedox can be configured to split the load between two or more hosts, one of which is running the OLAP component and the other is running the Web, Spreadsheet, and Integrator components. For more information, see Scaling by Clustering.

image_pdfimage_print