Configure Planning Analytics Local SSL Certificate

When you have Planning Analytics Spreadsheet Service configured to use an SSL Certificate, you need to configure Planning Analytics to use SSL certificate as well.

Prerequisites

Before you can configure Planning Analytics Local to use SSL, you would need the following files:

• SSL Certificate with Password (PFX Format)
• Private Key

Import SSL Certificate to Planning Analytics

Do the following step if you were only given pfx file and not the individual files of the certificate chain ( root certificate, intermediate certificate and end-user certificate).

1. Select Microsoft Management Console (MMC) and right-click Run as Administrator.
2. Under File select Add or Remove Snap-ins

3. Select Certificate and click on Add>

4. Select Computer Account.

5. Select Local Computer and click Finish and then OK.

6. Under Trusted Root Certificate Authorities, right-click on Certificate and click on All Task -> Import and click Next.

7. Click on Browse… and select the location of the .pfx file (Note: Make sure to change the type to Personal Information Exchange) and then click Next.

8. Enter the password of the pfx file and click Next.

9. In the MMC, Under Trusted Root Certificate Authority -> Certificate select the server certificate. The default view is shown as below. Make sure the certificate is issue for the correct server.

Export Root, Intermediate and End-User Certificate from PFX File

1. Under Trusted Root Certificate Authorities, click on the imported certificate.

2. Go under Certificate Path, in this page you should be able to verify the certificate chain. The top is the root certificate, underneath it is the intermediate certificate and the last is the end-user certificate.

3. Under Details, click on Copy to File… and click Next.

4. Click on No, do not export the private key. (Note: Selecting Yes, export the private key will only allow you to export the file in pfx file)

5. Select Browse… and browse to the directory where you want to keep you certificates.

6. Save the end-user certificate as cacert, intermediate certificate as caint and root certificate as caroot.
7. Repeat the same for all the other certificates.

Import SSL Certificate to Planning Analytics’ Keystore

1. Shut down all TM1 Services.
2. Create a backup of ssl folder in the <%PA Installation Path%>\ibm\cognos\tm1_64\bin64.
3. To recreate the keystore used by PA, delete the following files from the ssl folder in the <%PA Installation Path%>\ibm\cognos\tm1_64\bin64.
   • ibmtm1.kdb
   • ibmtm1.arm
   • ibmt1.crl
   • ibmtm1.rdb
   • ibmtm1.sth

4. Recreate ibmtm1.kdb file by opening Command Prompt as Administrator and running the following command.

gsk8capicmd_64 -keydb -convert -db “<%Location of Server PFX File%>.pfx =pw “<%Password of PFX File%>” -stashed -old_format pkcs12 -new_db <%Planning Analytics Installation directory%>\ibm\cognos\tm1_64\bin64\ssl\ibmtm1.kdb” -new_pw “<%Password of PFX File%>” -new_format -kdb – stash

5. To check if the ibmtm1.kdb file was created, run the following command.

gsk8capicmd_64.exe -cert -list -db “.\ssl\ibmtm1.kdb” -stashed

6. Rename the custom label to ibmtm1_server. The custom label in this case would “[email protected], CN=aus-tm1prod.dc01.fujixero.net, 0=FUJIFILM Business Innovation Australia Pty Ltd, L=Macquarie Park, ST=New South Wales, C=AU”.

gsk8capicmd_64 -cert -name -db “.\ssl\ibmtm1.kdb” -label <%Custom Label Name %> -new_label ibmtm1_server

7. To check if the rename was successful, run the command below.

gsk8capicmd_64.exe -cert -list -db “.\ssl\ibmtm1.kdb” -stashed

8. To set the default certificate, run the following command.

gsk8capicmd_64 -cert -setdefault -db “.\ssl\ibmtm1.kdb” – stashed” -label “ibmtm1_server”

9. To check if the ibmtm1.kdb has been set as the default, run the command below.  You will notice an asterisk next to the name, this indicates that this has been set as default.

gsk8capicmd_64.exe -cert -list -db “.\ssl\ibmtm1.kdb” -stashed

10. Run validation on ibmtm1.kdb file by running this command. The error shows there is no certificate chain.

gsk8capicmd_64 -cert -validate -db “.\ssl\ibmtm1.kdb” -stashed -label ibmtm1_server -fips

11. Import the caint (intermediate certificate) and caroot (root certificate) files using the same script above changing the label and the file name.

12. Check if the import has been done successfully using the following script gsk8capicmd_64 -cert -list -db .sslibmtm1.kdb -stashed. The labels indicate during the previous steps should show on the list.

Update tms1.cfg Configuration

1. Open tm1s.cfg and update the the file to include UseSSL=T.

You can now start the TM1 Admin Server and all your other TM1 Instances.

Need Help?

If you have any questions about installing Planning Analytics and configuring SSL Certificate, please reach out to us. We’d be delighted to help.