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

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 Terminal Server

Problem:
On new install of TM1 Architect on a Terminal Server, the list of TM1 instances is not showing up. They show up if you run TM1 architect direct on the TM1 server.

Solution:
Check Windows Firewall one both the Terminal Server and on your TM1 (PAL) server.
Go to Run from the start menu, enter wf.msc and press OK
Add a inbound rule that opens port 5495, 5498.
Save it as Cognos TM1 rule and check if it works again.

If you get a list of TM1 instances, but can not connect to them, then you miss the higher ports you have defined in TM1S.CFG
go back into Windows firewall, and add a port range like 12345-12360 to your cognos firewall rule.
Save and try again.

You need ports 5495, 5498 for TM1 Architect. 5895, 5898 is the Admin Server REST interface port (like HTTPPort for Tm1 servers). 5898 is when using TLS1.2
Then you need the ports you specify in TM1 applications – best to set a range like 12345-12360 then update in every TM1S.CFG file to unique values;
PortNumber=12345
HTTPportnumber=12346
ClientMessagePortNumber=12347

More Information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_tm1_inst_defaultvaluesfortm1installation.html
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_reviewthedefaultsettings.html
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_install_free_ports.html
http://cogknowhow.tm1.dk/archives/2055

Port 9300 is for Cognos Analytics (BI dispatcher).
Port 9510 is for TM1WEB and PMPSVC.
Port 5495,5498,5895,5898 is for TM1 Admin service.
Port 9012 is for the TM1 Agent.
Port 80 and 443 is for PAW or CA11 web-gateway ibmcognos.
There are other ports, but they are for communications between servers.

Product:
Cognos Analytics 11.0.13 fix pack 2
Microsoft Windows Server 2016

Problem:
Only a spinning circle when i surf to http://caservername.domain.com/ibmcognos/bi/v1/disp after we have applied a fix pack to CA11.

Solution:
Clear the Internet explorer cache.
Launch IE (not chrome)
Tools – Internet Options – under ‘General’ tab click on “Settings” under ‘Browsing History’
Select ‘Every Time I visit the webpage’
Click OK
Clear out the cache by click Delete button
Remove mark for “Preserv Favorites website data”
Click Delete
Click OK
Close IE and launch IE then try again.

More information:
https://www.ibm.com/support/pages/cognos-analytics-11013-fix-pack-2

Product:
Planning Analytics Workspace 36
Microsoft Windows 2016 Server

Problem:
When open PAW and create a new book. There are no TM1 servers/applications listed under databases.

If you only miss access to the TM1 instances, then you can see a error like unable to fetch data for content tree. Session can not be established for server: Planning Sample 500 (internal server error) “code”,”Explorer”,”refID”,”message”,”Failed to get children for Server Cubes”,”Cause”…. ConnectTimeoutException….

When you validate the IBM Planning Analytics Workspace Administration Tool, and get time out on the TM1 Admin Server URI – it can be because the Windows Firewall is blocking port 5898.

Solution:
The Microsoft Windows firewall is blocking the traffic to the TM1 Admin service, and the rest api port to the TM1 instances.
Go to Run from the start menu, enter wf.msc and press OK.
This will start the Windows Firewall.
Expand Inbound Rules
Click New Rule
Select Port and click next
Enter the needed ports, can be depending on TM1 instance, below for the standard samples;
9300, 9510, 5898, 9012, 8888, 12354, 8014, 5010, 44312, 8011, 5011, 8010
Click Next
Mark Allow the connection click Next
Click Next to apply rule everywhere
Enter name to Cognos and click Finish

Try again.

If you still get 401 errors, you may need to restart the Windows server to make the Firewall changes kick in.

Port 9300 is for Cognos Analytics (BI dispatcher)
Port 9510 is for TM1WEB
Port 5898 is for TM1 Admin service
Port 9012 is for the TM1 Agent

More information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_install_free_ports.html

Product:
Planning Analytics 2.0.6
Planning Analytics Workspace version 36
Microsoft Windows 2016 server

Problem:
How add many users to the PAW?

Solution:
Ensure that SSO and CAM security is working in TM1 (planning analytics).
Check the domain name in Cognos Configuration, this need to be enter in the file.
If the cognos namespace ID is Pacman, then it must be part of the file. It is case sensitive.
Open notepad (or excel) and enter the names like this:
Pacman/Donald Duck,Donald,Duck,administrator,donald.duck@pacman.com,active
Pacman/Daisy Duck,Daisy,Duck,analyst,daisy.duck@pacman.com,active

Save the file as a comma separated list.
Surf to your PAW installation.
Click on Administration
Click on Users
Click on upload users

Click on the icon of the file to browse for the file.
Select and Open the file
Now the users should be added to your PAW, so they can start working.
Please use this to add new administrators, direct after your first setup of PAW.

One user per line, and you must separate columns with comma. Should contain Login ID, First Name, Last Name, Role, Email, Status. Only the 3 first are required.

More information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/t_paw_add_users_locally.html