It’s been a long few days. A customer who wanted not just to integrate IBM Cognos 10.1 Business Intelligence with TM1 9.5.2, but also wanted to do it using SSL / HTTPS rather than the out of the box HTTP protocol. Nice.
I would have said that what follows is not for the faint hearted however with the right steps set out in-front of you its a lot easier than it seemed at the time!
This is a fairly complex task which will need you to have a pretty good level of understanding of a lot of technologies. I have tried to write this in a simple way but I think I will need to refine this over time based on comments made. Please feel free to add any feedback at the end so that I can improve this article as required
We will assume a few things before we start:
- You have your own SSL certificate either purchased or created for IIS.
- The SSL certificate is configured and is working (able to serve up a basic HTML page via HTTPS)
- You have basic installations of IBM Cognos 10.1 BI and TM1 9.5.2 already integrated (help on integration here)
Getting Cognos BI working via HTTPS
With IIS already configured to work on HTTPS its a simple task to get the Cognos Gateway components up and running.
First of all you must get all of your dispatchers up and running using the HTTPS protocol and the secured port 9343. See the screenshot below for an example.
Now that this is done, navigate to the Cryptography -> Cognos section of the tree-view. Here we need to make sure that the server name is listed for the Server Common Name property. As below this should be set to the exact name of the server as specified in your dispatcher URI fields.
Whilst you are there, make sure you set a password for each of the key stores. It is advisable to use a single, strong password here or else you may find it difficult later on to know which password to use.
The server common name value above is used in the certificate creation, if this does not match the name of the server specified in the dispatcher URI’s then you will get a prompt due to an invalid certificate during certain operations. This could stop the integration between TM1 and BI from working correctly.
With IBM Cognos Configuration updated its now time to save and restart the services. If you have just changed an existing installation from HTTP to HTTPS you will find that the IBM Cognos 10 service is re-registered and so you will have to input the service account credentials again to get things to start up properly.
Saving will take some time as the cryptography settings will be recreated.
Now is a good time to check that IBM Cognos 10 is up and running on HTTPS. Check IBM Cognos Connection by visiting your gateway URI which should be in the form of
https://bi101.planitcpm.local/ibmcognos
This should return the familiar IBM Cognos portal screen.
If all is well, now try connecting to the dispatcher direct. This is best done on the server its self as we can then ensure that the SSL certificate is imported at the same time.
The URI to enter is similar to this:
https://bi101.planitcpm.local:9343/p2pd/servlet/dispatch
Because the self-created certificate is not from a trusted root authority you will get the following screen in Internet Explorer. For now click “Continue to this website (not recommended)”.
You will now see the bare dispatcher login screen. Notice that the address bar is in pink and you have a certificate error to the right.
Click on “Certificate Error” to view and install the certificate.
When the pop-up screen shows, click on “View Certificate”
You will now see the certificate, click “Install Certificate”.
On the Certificate Store selection screen select “Place all certificates in the following store” and make sure Trusted Root Certification Authorities is selected.
Complete the Wizard. You will be warned that you are about to install the certificate. Click “Yes” to complete the process.
Now on the Certificate view, switch to the “Certification Path” tab and complete the same process for the CA Root Certificate.
Now with both certificates installed and trusted, exit from Internet Explorer and visit the Dispatcher URI again. This time there should be no warnings etc.
IBM Cognos 10.1 is now working using SSL at both the Gateway and the Dispatcher levels.
Enabling TM1 to connect to IBM Cognos 10.1 via SSL
TM1 will need to trust and access the certificate used in the IBM Cognos 10.1 dispatchers, for this to work we must export the certificate from the CA Keystore. IBM provides a tool for this ThirdPartyCertificateTool.bat (or on Linux / Unix ThirdPartyCetificateTool.sh).
Open a Command Prompt and navigate to your IBM Cognos 10.1 installation directory / bin folder (in my case c:\Program Files\IBM\Cognos\c10\bin).
You will need to set the JAVA_HOME variable so that it can use the IBM Cognos supplied JRE. Enter the command:
set JAVA_HOME=c:\program files\ibm\cognos\c10\bin\jre\6.0\
Now run the following command to export the keys. You will need to substitute my paths for your own. I am exporting the certificate file to the root of c:\ to a file called cognos.cer.
ThirdPartyCertificateTool.bat -E -T -r c:\cognos.cer -k "c:\Program Files\ibm\cognos\c10\configuration\signkeypair\jCAKeystore" -p password
Where “password” is the password you set for your keys tores in IBM Cognos Configuration.
You will get a response similar to the one below.
Note: If you get an error message with number CAM-CRP-1201 it seems to indicate that the password specified is incorrect. Strangely if you actually omit the password you get an error that tells you that the password is wrong.
The file cognos.cer should now be in your output location. Locate this file and copy it to a secure location on your TM1 Server.
With the file copied to your TM1 Server you will now need to edit the TM1s.cfg file to ensure that it knows where to find the certificate file.
You should have the following lines, edited for your installation.
ServerCAMURI=https://bi101.planitcpm.local:9343/p2pd/servlet/dispatch
ClientCAMURI=https://bi101.planitcpm.local/ibmcognos/cgi-bin/cognosisapi.dll
ClientPingCAMPassport=900
#Optional CAM parameters
CAMSSLCertificate=c:\cognos.cer
#CAMSSLCertRevList=
SkipSSLCAMHostCheck=TRUE
Once that has been updated you will need to save the file and restart your TM1 Services to allow the changes to be picked up.
Now, to ensure that TM1 and BI can integrate correctly there are a few more steps to complete, these have been discussed in another blog article which can be found here:
There are a few differences now however, importantly, these are that the URL’s specified in the various configuration files must contain the HTTPS protocol not HTTP and that you must reference the new port 9343 in place of 9300.
The files to update are on the BI / Gateway Server:
- variables_TM1.xml
Which should now contain the following lines instead:
https://bi101.planitcpm.local/TM1Web/TM1WebMiniLogin.aspx
https://bi101.planitcpm.local/TM1Web/TM1WebLoginHandler.aspx
https://bi101.planitcpm.local/TM1Web/TM1WebMain.aspx
https://bi101/TM1Web/TM1WebMiniLogin.aspx
https://bi101/TM1Web/TM1WebLoginHandler.aspx
https://bi101/TM1Web/TM1WebMain.aspx
- planning.html
Which should now contain the following line
// Update the following to point to the location of the planning service(s)
var planningServices = ["https://bi101.planitcpm.local:9343"];
Allowing TM1 Web to trust the BI Certificate
If you now run the initialize.jsp script from your installation of TM1 Contributor you will find that you get an error.
You are getting this error because the SSL Certificate that was applied to your IIS servers not trusted by the IBM Cognos supplied JAVA environment. We must run a command line application to import this file.
On the BI Server open a command prompt and navigate to c:\Program Files\IBM\Cognos\c10\bin\jre\6.0\bin and execute the following command:
keytool -import -alias caWeb -keystore "c:/program files/ibm/cognos/c10/bin/jre/6.0/lib/security/cacerts” -trustcacerts -file c:\CertificateName.cer
You will be prompted for a password, this is the default password used by the keytool app – enter “changeit” in lower-case.
We also need to run the command to import the certificate created by IBM Cognos 10.1 or we will see an error stating:
We must run a command line application to import the cognos.cer file.
On the BI Server open a command prompt and navigate to c:\Program Files\IBM\Cognos\c10\bin\jre\6.0\bin and execute the following command:
keytool -import -alias caRoot -keystore "c:/program files/ibm/cognos/c10/bin/jre/6.0/lib/security/cacerts” -trustcacerts -file c:\cognos.cer
You will be prompted for a password, this is the default password used by the keytool app – enter “changeit” in lower-case.
With this all completed you should now find that you can access TM1 Contributor using HTTPS as shown below:
As stated at the beginning of the article, please do comment or ask questions. I know this article needs some refinement but I really did want to keep it as short and to the point as possible whilst still explaining the steps involved to get this working.
I would also like to thank Keith Faulkner from IBM Support who helped me get this working and organised the relevant people behind the scenes at IBM to get the right information out to me.
