Importing CA-signed SSL certificates for Cognos and DWH (Insight 7.3.2 and earlier)

You can add SSL certificates to enable enhanced authentication and encryption for your Data Warehouse and Cognos environment.

Before you begin

This procedure is for systems running OnCommnand Insight 7.3.2 and earlier.

You must have admin privileges to perform this procedure.

Steps

  1. Access the Data Warehouse using a remote desktop
  2. Back up the ..\SANscreen\cognos\c10_64\configuration\cogstartup.xml file.
  3. Back up the certs and csk folders in ..\SANscreen\cognos\c10_64\configuration
  4. List the contents of the Cognos CAMKeystore to see if both signing and encryption certificates are signed by the same CA: "D:\Program Files\SANscreen\java\bin\keytool.exe" -keystore "D:\Program Files\SANscreen\cognos\c10_64\configuration\certs\CAMKeystore" -storetype PKCS12 -storepass NoPassWordSet -list -v
    If the issuer (CA) SHA1 is not the same for both the signing and encryption certificate, perform the following steps:
    1. Stop the Cognos services.
    2. Close the IBM Cognos Configuration.
    3. Delete the CAMKeystore and CAMKeystore.lock files from the ..\SANscreen\cognos\c10_64\configuration\certs folder.
    4. Launch the IBM Cognos Configuration tool as administrator.SANscreen\cognos\c10_64\bin\cogconfig.sh
    5. Save the configuration.
      This will generate a new CAMKeystore keystore.
  5. Generate a Certificate Signing Request from Cognos. In the Admin CMD prompt window, enter the following: D: cd “Program Files\SANscreen\cognos\c10_64\bin”ThirdPartyCertificateTool.bat -java:local -c -e -d “CN=FQDN,O=orgname,C=US” -r c:\temp\encryptrequest.csr
    If this command results in a "NoClassFound" error perform the following steps and then repeat the command.
    1. Modify ..\SANscreen\cognos\c10_64\bin\ThirdPartyCertificateTool.bat .
    2. Under "uselocal section (line 49)" Change J_HOME to ..\bin64\jre\7.0.
    3. Save the file.
    4. Generate a Certificate Signing Request from Cognos again.
  6. Open the c:\temp\encryptrequest.csr file and copy the generated content.
  7. Input encryptRequest.csr content and generate certificates using the CA signing portal.
  8. Download the chain certificates, including the root certificate using PKCS7 format. This will download fqdn.p7b.
  9. Obtain a certificate in .p7b format from your CA. Use a name that identifies it as the certificate for the Cognos Web server. For example, name it CGWEB001.Cognos.
  10. Upload the file to D: on the DWH server.
  11. Perform the following steps using the Admin UI:
    1. Open the .p7b certificate in "Crypto Shell Extensions".
    2. Click Certificates in the left pane.
    3. Right click on your certificate.
    4. Click All Tasks > Export.
    5. Click Base-64 encoded X.509 (.CER).
    6. Specify the name of the file you want to export (D:\root cert.cer).
    Repeat these steps to export all of the certificates separately into .cer files. Name them intermediate.cer and cognos.cer.
  12. Merge the root.cer and the intermediate.cer into one file.
    1. Open the intermediate.cer with NotePad and copy all of the content.
    2. Open the root.cer with NotePad and add the content of the intermediate.cer file.
    3. Save the file as CA.cer.
  13. Import the certificates into Cognos. In the Admin command prompt, enter the following command: D: cd "Program Files\sanscreen\cognos\c10-64\binThirdPartyCertificateTool.bat -java:local -i -e -r c:\temp.cognos.cer -t c:\temp\CA.cer
  14. Access the Data Warehouse using a remote desktop.
  15. Open the IBM Cognos Configuration as an Administrator.
    1. Select Local > Security > Cryptography> Cognos.
    2. Change "Use third party CA?" to True.
    3. Save the configuration.
    4. Restart Cognos.
  16. Export the latest Cognos certificate from the Cognos trust store to the following file: c:\tmp\cognosssl.crt. "D:\Program Files\SANscreen\java\bin\keytool.exe" -keystore "D:\Program Files\SANscreen\cognos\c10_64\configuration\certs\CAMKeystore" -storetype PKCS12 -storepass NoPassWordSet -exportcert -file “c:\temp\cognosssl.crt” -alias encryption
  17. Import the latest Cognos certificate (c:\temp\cognosssl.crt) into the DWH truststore to establish SSL communication between the DWH and Cognos servers. "D:\Program Files\SANscreen\java\bin\keytool.exe" -importcert -file “c:\temp\cognosssl.crt” -keystore "D:\Program Files\SANscreen\wildfly\standalone\configuration\server.truststore" -alias cognosssl
  18. Clear the existing Java path variable as it might cause problems during this procedure:SET PATH=.
  19. Navigate to ..\SANscreen\cognos\c10_64\bin
  20. Enter the following command: ThirdPartyCertificateTool.bat -java:local -i -e -r OCIDWH.cer -t chain.p7b
    Where OCIDWH.cer is the OCI DWH certificate, and the chain.p7b is the chain of certificates including the root certificate.
  21. Use the following command to make Cognos trust the Root certificate: ThirdPartyCertificateTool.bat -java:local -i -T -r ca.cer
    In the example, ca.cer is your organization's root certificate. This step is required even though the previous certificate import steps seemed to log messages about trusting certificate authorities.
  22. Open the IBM Cognos configuration UI and enable third party certificates: Certificate Authority setting > Use a 3rd party CA? > True
  23. Save the changes
  24. Restart the Cognos services
  25. Enter the following command in the Admin command prompt window to export the latest Cognos certificate to a file: c:\temp\cognosssl.crt. "D:\Program Files\SANscreen\java\bin\keytool.exe" -keystore "D:\Program Files\SANscreen\cognos\c10_64\configuration\certs\CAMKeystore" -storetype PKCS12 -storepass NoPassWordSet -exportcert -file “c:\temp\cognosssl.crt” -alias encryption
  26. Enter the following command in the Admin command prompt window to import the latest Cognos certificate (c:\temp\cognosssl.crt )"D:\Program Files\SANscreen\java\bin\keytool.exe" -importcert -file “c:\temp\cognosssl.crt” -keystore "D:\Program Files\SANscreen\wildfly\standalone\configuration\server.truststore" -alias cognosssl
    1. Backup \SANscreen\wildfly\standalone\configuration\server.truststore
    2. Import the new certs into the trust store: C:\Program Files\SANscreen\java64\bin>keytool -importcert -file C:\ocidwhcert.cer -keystore D:\Program Files\SANscreen\wildfly\standalone\configuration\server.truststore"
      c:\ocidwhcert.cer is the public certificate of your OCI DWH
  27. Restart the Insight server.
  28. Log in to the Data Warehouse server and perform a backup.
    The backup should complete successfully, including backing up the Cognos content store.