Product:
Planning Analytics 2.0.6 Workspace
Microsoft Windows 2016 Server
Problem:
How to use OPENSSL to convert certificates from company CA pfx file to the pem format needed by PAW?
Solution:
Download OPENSSL from http://slproweb.com/products/Win32OpenSSL.html
or from https://github.com/git-for-windows/git/releases/tag/v2.23.0.windows.1 – get the file Git-2.23.0-64-bit.exe Run the installation with all default values.
When installing GIT you will get a local openssl tool, that you can access from the command line:
“c:\program files\git\mingw64\bin\openssl.exe”
If you get a certificatechainfile.pfx file that you should use, you can convert it with the following command in CMD:
openssl pkcs12 -in c:\temp\your.pfx -out c:\temp\good.pem -nodes
Then you need to open good.pem in Notepad++ and remove the lines not needed, and save it as pa-workspace.pem. It should have this certs in the file;
—–BEGIN RSA PRIVATE KEY—–
(Your Private Key: your_domain_name.key)
—–END RSA PRIVATE KEY—–
—–BEGIN CERTIFICATE—–
(Your Server certificate: your_domain_name.crt)
—–END CERTIFICATE—–
—–BEGIN CERTIFICATE—–
(Your Intermediate certificate: IntermediateCA.crt)
—–END CERTIFICATE—–
—–BEGIN CERTIFICATE—–
(Your Root certificate: TrustedRoot.crt)
—–END CERTIFICATE—–
How setup TLS (SSL) for PAW:
(Do the steps in your TEST environment first, to ensure they work for you.)
Export the root and intermediate certificates first.
Start Internet Explorer and surf to company internal site.
Click on the PAD lock icon.
Click view certificates.
Click Certificate Path tab.
Mark root cert and click view certificate.
Click Details tab.
Click “copy to file” button.
Click Next.
Select Base-64 encoded X.509 (.CER) and click Next.
Browse to your c:\temp folder and enter a name.
Click next and finish.
Repeat above steps for the intermediate cert.
Copy this two cer files to the d:\ibm\paw\config\certs folder.
Rename the cer files to pem.
Start Powershell as administrator.
Go to folder d:\ibm\paw\scripts.
Run .\process_certs.ps1 to include the root cert in the cacerts file.
Stop the paw with command d:\ibm\paw\scripts\paw.ps1 stop.
Go to the d:\ibm\paw\config\ssl folder.
Rename pa-workspace.pem to pa-workspace.pem.org.
If you got a .pfx file from the company that include the privatekey, servercert and intermediate and root certs. You convert it with this command:
openssl pkcs12 -in your.pfx -out good.pem -nodes
Open good.pem in notepad, and remove lines above the —-BEGIN CERTIFICATE—- but after the —- END line.
Save the file. Now only with the cryptic binary text.
Copy the good.pem file to folder d:\ibm\paw\config\ssl and rename it to pa-workspace.pem
Open d:\ibm\paw\config\paw.ps1 file in notepad++.
Change all HTTP to HTTPS.
Add last in the file, this two lines:
$env:EnableSSL=”true”
$env:ServerName=”yourPAWservername”
Save the file.
Go to the d:\ibm\paw\ folder.
Run ./Start.ps1 to start the PAW administration.
Click on Validate button. Ensure all URL are correct, does they point to correct CA11 or TM1 servers?
Click on the Update button.
Restart PAW, can also be done from powershell with commando d:\ibm\paw\scripts\paw.ps1
You must add ibmtm1.arm cert to your CA11 servers:
https://www.ibm.com/support/pages/configure-datasource-ibm-planning-analytics-20x-ibm-cognos-analytics-1106
You must add SSL (TLS) cert to your TM1WEB servers:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_pa_use_ibmkeymgmt.html
https://www.ibm.com/support/pages/how-obtain-planning-analytics-tm1-server-certificate
More Information:
https://knowledge.digicert.com/solution/SO26449.html
https://www.feistyduck.com/
https://www.ibm.com/support/pages/how-transform-pem-and-pfx-keystore-public-key-cryptography-standard-12-pkcs12-keystore
https://www.freecodecamp.org/news/openssl-command-cheatsheet-b441be1e8c4a/
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_paw_enable_ssl.html
https://www.sslshopper.com/article-most-common-openssl-commands.html
Common OPENSSL commands (from SSL Shopper):
These commands allow you to generate CSRs, Certificates, Private Keys and do other miscellaneous tasks.
- Generate a new private key and Certificate Signing Request
openssl req -out CSR.csr -new -newkey rsa:2048 -nodes -keyout privateKey.key
- Generate a self-signed certificate (see How to Create and Install an Apache Self Signed Certificate for more info)
openssl req -x509 -sha256 -nodes -days 365 -newkey rsa:2048 -keyout privateKey.key -out certificate.crt
- Generate a certificate signing request (CSR) for an existing private key
openssl req -out CSR.csr -key privateKey.key -new
- Generate a certificate signing request based on an existing certificate
openssl x509 -x509toreq -in certificate.crt -out CSR.csr -signkey privateKey.key
- Remove a passphrase from a private key
openssl rsa -in privateKey.pem -out newPrivateKey.pem
Checking Using OpenSSL
If you need to check the information within a Certificate, CSR or Private Key, use these commands. You can also check CSRs and check certificates using SSL Shopper online tools.
- Check a Certificate Signing Request (CSR)
openssl req -text -noout -verify -in CSR.csr
- Check a private key
openssl rsa -in privateKey.key -check
- Check a certificate
openssl x509 -in certificate.crt -text -noout
- Check a PKCS#12 file (.pfx or .p12)
openssl pkcs12 -info -in keyStore.p12
Debugging Using OpenSSL
If you are receiving an error that the private doesn’t match the certificate or that a certificate that you installed to a site is not trusted, try one of these commands. If you are trying to verify that an SSL certificate is installed correctly, be sure to check out the SSL Shopper SSL Checker.
- Check an MD5 hash of the public key to ensure that it matches with what is in a CSR or private key
openssl x509 -noout -modulus -in certificate.crt | openssl md5
openssl rsa -noout -modulus -in privateKey.key | openssl md5
openssl req -noout -modulus -in CSR.csr | openssl md5
- Check an SSL connection. All the certificates (including Intermediates) should be displayed
openssl s_client -connect www.paypal.com:443
Converting Using OpenSSL
These commands allow you to convert certificates and keys to different formats to make them compatible with specific types of servers or software. For example, you can convert a normal PEM file that would work with Apache to a PFX (PKCS#12) file and use it with Tomcat or IIS. Use SSL Shopper SSL Converter to convert certificates without messing with OpenSSL.
- Convert a DER file (.crt .cer .der) to PEM
openssl x509 -inform der -in certificate.cer -out certificate.pem
- Convert a PEM file to DER
openssl x509 -outform der -in certificate.pem -out certificate.der
- Convert a PKCS#12 file (.pfx .p12) containing a private key and certificates to PEM
openssl pkcs12 -in keyStore.pfx -out keyStore.pem -nodes
You can add -nocerts to only output the private key or add -nokeys to only output the certificates.
- Convert a PEM certificate file and a private key to PKCS#12 (.pfx .p12)
openssl pkcs12 -export -out certificate.pfx -inkey privateKey.key -in certificate.crt -certfile CAC
https://www.sslshopper.com/ssl-faq.html
Now it should work.