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 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
For a cluster environment, refer to the following guides: