Product:
Planning Analytics 2.0.9
Microsoft Windows 2016 server

Problem:
How can i find if a TM1 TI process contain any reference to other TM1 process?

Suggested solution:
Search in DOS in all the PRO files in data folder (normally the TI process are saved as text files with pro ending in data folder) for the name of the TI process.

On your TM1 server, start a CMD prompt as a Administrator.
Change to the folder where you have your TM1 application.
Enter below command to find the word “security” in all TI process for planning sample:
find  /i  “security”  “C:\Program Files\ibm\cognos\tm1_64\samples\tm1\plansamp\*.pro”

You will get a result similar to this;

but it lists all the files it search – so a better command can be to use FINDSTR

findstr /i /m /s “plan_load_budget_asci” “C:\Program Files\ibm\cognos\tm1_64\samples\tm1\plansamp\*.pro”

Above line will search for the TI process name plan_load_budget_asci

Inside the Test1.pro i had enter a reference to the other TI process – that way it is listed.

More information:
https://www.groovypost.com/howto/find-command-search-windows/

https://ss64.com/nt/find.html

https://ss64.com/nt/findstr.html

Running a TI Process from within another Process

 

Product:
Cognos Controller 10.4.2 IF4 (CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2000.1020-0)
Cognos Analytics 11.0.13
Microsoft Windows 2012 R2 server

Problem:
How setup Controller web in the new version?
When the Windows IIS server for CA11 is using HTTPS.

Solution:
Follow the official instructions here https://www.ibm.com/support/pages/how-install-controller-web
https://www.ibm.com/support/pages/how-configure-controller-web-use-cognos-cam-authentication
https://www.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

This are the steps;
Ensure that you during setup can browse to cognos analytics iis server on both http and https.
Controller web frontend and backend are installed on the same Windows server.
Open server.env file from folder C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\
Change to: JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre
Open jvm.options file from folder C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\
Change last in file to:
-Xms4g
-Xmx8g
(depending on number of users and memory on the Windows Server)
Save the files.
Start a CMD command prompt as Administrator
Go to folder C:\Program Files\IBM\cognos\ccr_64\fcmweb
Enter command:  SyncDBConf.bat  ..\Data  wlp\usr\shared\config\datasources
In file explorer go to C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web

Open com.ibm.cognos.fcm.web.properties file in notepad++
Change to: ccrwsUrl=http://cognosserver.company.com/ibmcognos/controllerserver/ccrws.asmx
and update also;
biUrl=http://cognosserver.company.com/ibmcognos/bi/v1/disp
biDispatchEndpoint=http://cognosserver.company.com:9300/p2pd/servlet/dispatch
loginMode=CAM
logoutMode.logoutCam=true
# pdfFont=C:/windows/fonts/Arial.ttf
Save the files with this changes.
Map up the c drive of the cognos analytics server and go to folder C:\Program Files\IBM\cognos\analytics\templates\ps\portal\ and create the text file variables_CCRWeb.xml
In notepad++ enter this values:

<CRNenv c_cmd=”http://controllerwebserver.company.com:443/#!/CamLogin”>
<cookies>
<param name=”cam_passport”/>
</cookies>
</CRNenv>

Save it inside file variables_CCRWeb.xml.
Restart the IBM Cognos Controller Web Windows service.
Go to folder C:\Program Files\IBM\cognos\ccr_64\frontend
Open config.js file in Notepad++

Change first host value to “host”: “controllerwebserver.company.com“,
Change second host value to “host”: “controllerwebserver.company.com“, //interface used by Controller Web UI Service
Change the port to 443 from 9080.
Save the file.
Restart the IBM Cognos Controller Web UI Windows Service.
Try to browse to http://controllerwebserver.company.com:443/ from client laptop.
Check that the windows firewall on the server allow ports 80,443,9300
If you are using SSO modify the IIS website’s controllerserver virtual directory to use anonymous authentication, only have Windows authentication on the sso virtual directory.

If you get a blank page in web browser when browse to Controller web, check
the Internet Explorer settings, to disable Compatability View mode.

 

Get the Certificates for you controllerwebserver.company.com servers DNS alias from the company Certificate Authority. You need the root cert (root1.cer) and the intermediate cert (intermediate2.cer), also the pfx file for the servername and chain (servername3.pfx) with a password.
During setup use the same password for all keys and keystores, in our example we will use changeit.
Start a CMD command prompt as Administrator
Go to folder c:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin
Update this command with your certificate files, before you run it:
keytool -keystore key.jks -importcert -file C:\temp\intermediate2.cer
Enter the password (must be the same as the password you got with the pfx file)
On question ‘Trust this certificate’ enter yes
Import the root1.cer file with command:
keytool -import -trustcacerts -alias issue -file c:\temp\root1.cer -keystore key.jks
Add the server-certificate with command:
keytool -importkeystore -destkeystore key.jks -srckeystore C:\temp\servername3.pfx -srcstoretype PKCS12 -srcstorepass changeit
(replace the changeit with the password you got with the pfx file and you used before)
Copy the file key.jks to folder C:\Program Files\IBM\cognos\ccr_64\fcmweb\jre\lib\security
Open C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\server.xml in Notepad++
Replace the line <!– <feature>ssl-1.0</feature> –> with  <feature>ssl-1.0</feature>
Add a line at the bottom, like this:
</webApplication>
<keyStore id=”defaultKeyStore” password=”changeit” sslProtocol=”SSL_TLS” />
</server>
Save the file.
Restart the ‘IBM Cognos Controller Web UI’ and ‘IBM Cognos Controller Web
Copy the file key.jks to folder C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security
Open C:\Program Files\IBM\cognos\ccr_64\analytics\templates\ps\portal\variables_CCRWeb.xml in notepad++ and change to https;

<CRNenv c_cmd=”https://controllerwebserver.company.com:443/#!/CamLogin”>
<cookies>
<param name=”cam_passport”/>
</cookies>
</CRNenv>

Open C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\com.ibm.cognos.fcm.web.properties in notepad++

Change line ccrwsUrl=https://cognosserver.company.com/ibmcognos/controllerserver/ccrws.asmx and biUrl=https://cognosserver.company.com/ibmcognos/bi/v1/disp to use https if you only have SSL on the IIS server for CA11.
Save the file.
Delete the key.p12 file from folder C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security

Reboot the controllerweb windows server.

Check filer C:\Program files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs\messages.log for , if no error go one with next step.

One your laptop install openssl from here 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.

Copy your servername3.pfx file to c:\workarea folder. Start a CMD command prompt as Administrator.
Run this command:

“C:\Program Files\git\mingw64\bin\openssl.exe” pkcs12 -in C:\workarea\servername3.pfx -clcerts -nokeys -out cert.crt

Enter the password you got with the pfx file.
“C:\Program Files\git\mingw64\bin\openssl.exe” pkcs12 -in C:\workarea\servername3.pfx -nocerts -out keyfile.key

Enter the password you got with the pfx file. Then you have to give a new password for the keyfile.key – for simplicity use the same password.
Copy cert.crt , keyfile.key files to C:\Program Files\ibm\cognos\ccr_64\frontend folder on your Controller Web server.

Open C:\Program Files\IBM\cognos\ccr_64\frontend\config.js file in Notepad++

Change the first port to “port”: 3443,
Change the protocol to “protocol”: “https:” //set https: for SSL
Change secure to “secure”: false //set this to false if Controller Web Backend is using Self Signed certificates
Remove /*   */   to uncomment the SSL section.
Change the “passphrase”:”changeit” to your password that you set before.
Save the file.
Restart the ‘IBM Cognos Controller Web UI’ and ‘IBM Cognos Controller Web
Try to browse to https://controllerwebserver.company.com/ from client laptop.

Check the log files in folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs

This page can’t be displayed. Turn on TLS 1.0, TLS 1.1, and TLS 1.2 in Advanced settings and try connecting to https://hostname again. If this error persists, it is possible that this site uses an unsupported protocol or cipher suite such as RC4 (link for the details), which is not considered secure. Please contact your site administrator.
If you get above error, please try first with different web browser, like Firefox or Edge.

Error:

00000022 com.ibm.ws.ssl.provider.AbstractJSSEProvider                 E CWPKI0813E: Error while trying to initialize the keymanager for the keystore [C:/Program Files/ibm/cognos/ccr_64/fcmweb/wlp/usr/servers/fcm.web/resources/security/key.jks]. The private key password is not correct or the keystore has multiple private keys with different passwords.  This keystore can not be used for SSL.  Exception message is: [Cannot recover key].

00000022 com.ibm.ws.logging.internal.impl.IncidentImpl                I FFDC1015I: An FFDC Incident has been created: “java.security.UnrecoverableKeyException: Cannot recover key: invalid password for key in file ‘C:/Program Files/ibm/cognos/ccr_64/fcmweb/wlp/usr/servers/fcm.web/resources/security/key.jks’ com.ibm.ws.ssl.provider.IBMJSSEProvider getKeyTrustManagers” at

Solution:

The pfx file password was not used in creation of the key.jks file

Error:

00000022 com.ibm.ws.ssl.config.WSKeyStore                             E CWPKI0033E: The keystore located at C:/Program Files/ibm/cognos/ccr_64/fcmweb/wlp/usr/servers/fcm.web/resources/security/key.jks did not load because of the following error: Keystore was tampered with, or password was incorrect

00000022 com.ibm.ws.ssl.config.WSKeyStore                             W CWPKI0809W: There is a failure loading the defaultKeyStore keystore. If an SSL configuration references the defaultKeyStore keystore, then the SSL configuration will fail to initialize.

Solution:

The password in the server.xml file is not the same as the key.jks file.

Error:

00000022 com.ibm.ws.logging.internal.impl.IncidentImpl                I FFDC1015I: An FFDC Incident has been created: “java.security.UnrecoverableKeyException: Cannot recover key: invalid password for key in file ‘C:/Program Files/ibm/cognos/ccr_64/fcmweb/wlp/usr/servers/fcm.web/resources/security/key.jks’ com.ibm.ws.ssl.provider.IBMJSSEProvider getKeyTrustManagers” at

Solution:

Maybe you use different password in servername3.pfx and key.jks file

Error:

00000022 com.ibm.ws.ssl.config.WSKeyStore                             E CWPKI0033E: The keystore located at C:/Program Files/ibm/cognos/ccr_64/fcmweb/wlp/usr/servers/fcm.web/resources/security/key.jks did not load because of the following error: Keystore was tampered with, or password was incorrect

00000022 com.ibm.ws.ssl.config.WSKeyStore                             W CWPKI0809W: There is a failure loading the defaultKeyStore keystore. If an SSL configuration references the defaultKeyStore keystore, then the SSL configuration will fail to initialize

Solution:

The key.jks file is not copied to C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security after update.

If you get “Not authorized” when logon to Controller Web when SQL server is setup to only allow TLS 1.2 communication.

1. Go to the folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\etc\
2. Open the jvm.options file in NOTEPAD++
3. Add the following lines (at the end):
-Dcom.ibm.jsse2.overrideDefaultTLS=true
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12
4. Save changes
Restart the Controller web service.

More Information:

https://www.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

https://www.ibm.com/support/pages/node/563063

https://www.ibm.com/support/pages/node/156485

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/t_configuresslforcognos8.html

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

https://www.ibm.com/support/pages/how-configure-controller-use-tls-formerly-ssl-connect-microsoft-sql-database-transit-database-encryption?lnk=hm

https://support.microsoft.com/en-us/help/17479/windows-internet-explorer-11-change-security-privacy-settings

https://www.ibm.com/support/pages/troubleshooting-controller-web-page-blankwhite

https://www.ibm.com/support/pages/not-authorized-when-logon-controller-web-caused-missing-tls-encryption-options-jvmoptions-configuration-file

Product:
Cognos Analytics 11.0.13
Microsoft Windows 2012 server

Problem:
Problem to login in Cognos Controller after TLS 1.2 demand on the CA11 server is implemented.

Error:
The underlying connection was closed: An unexpected error occurred on a send.
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Cognos.Controller.ControllerSoapHttpClientProtocol.Invoke(String methodname, Object[] parameters)
at Cognos.Controller.Proxy.Light.WSLight.CheckServerConnection(String sGuid, String sUser)
at Cognos.Controller.Direct.ServerInfoD.CheckServerConnection()
at CCR.AppContext.CheckConnectionToServer()

Solution:
On cognos controller client computer
start a command prompt as administrator
Run below commands (to update the registry)

REG ADD HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1

REG ADD HKLM\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1

More Information:
https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12
https://www.ibm.com/support/pages/error-occurred-while-trying-object-reference-not-set-repnetgetuserinfo-error-launching-controller-client-caused-tls-1011-disabled-ca-server

Product:
Cognos Controller 10.4.2
Cognos Analytics 11.0.13
Microsoft Windows 2012 server

Problem:
On a CA installation where the IIS web server is using HTTPS for IBMCOGNOS.
How update the certificate on the IIS server when it expire after some years.

Suggested Solution:
Get a new certificate from the company’s internal Certificate Authority.
You get a pfx file and a cer.pem file.
You also get a password to the pfx file – save it in notepad.
Save them in a separate folder on the server (c:\temp\cert)

Go to the IIS Manager
Select the server name in the tree
Click on Server Certificates icon

Click on Import link at the right
Click on … to find the pfx file.
Enter the password and press OK

Click on Default web site
Click on Bindings

Select HTTPS
Click Edit

Click on drop down and select the new cert
Click OK

Start your Cognos Controller client and check that you can login.

You may also need to update the CACERTS file in the cognos controller client installation to get the Java menus to work (like maintain – jobs – define).

Export the certificate from IIS with use of IE:
Surf to your IBMCOGNOS site with https
Click on the lock icon in IE toolbar and click “View certificates”
Click on Details tab

Click Copy to file button

Click next

Select Base-64 encoded X.509 and click next

Enter path and name and click next

Click finish
Repeat above for the Root certificate and any intermediate certificates.

You must first view the certificate before you export it from the details tab.

Import the cert with the IKEYMAN:
If you have Cognos Analytic on the same server as you have installed Cognos Controller client, you can use it to import the cer files to the cacert file.
Before change the cacerts file make a backup of the file to other folder.

Go to C:\Program Files\ibm\cognos\analytics\jre\bin
Right click ikeyman.exe and select run as administrator

click open and select your cacerts file in folder C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security

Click ok

Enter password changeit
Click ok

Click drop down list and select Signer Certificates

Click on Add button

Click on browse and select your cer file.
Click OK

Enter a name e.g. Cognos

Repeat the ADD steps for Root and other company needed certificates.

Changes are save direct, so only select exit to end the program.

The update cacerts file can be made part of any Cognos Controller client installation package the company uses (so not every user need to do this) .

Or import the cert with the command line, if you do not have CA11 on the server:
“C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\bin\keytool” -import -alias CognosController -file “C:\temp\cert\CognosController.cer” -keystore “C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security\cacerts”

You should manage with only have the Company Root certificate and any intermediated in the file;

“C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\bin\keytool” -import -alias root1 -file “C:\temp\cert\root1.cer” -keystore “C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security\cacerts”

“C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\bin\keytool” -import -alias intermediated2 -file “C:\temp\cert\intermediated2.cer” -keystore “C:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security\cacerts”

More Information:
To add certificates to the Trusted Root Certification Authorities store for a local computer

Click Start, click Start Search, type mmc, and then press ENTER.

On the File menu, click Add/Remove Snap-in.

Under Available snap-ins, click Certificates,and then click Add.

Under This snap-in will always manage certificates for, click Computer account, and then click Next.

Click Local computer, and click Finish.

If you have no more snap-ins to add to the console, click OK.

In the console tree, double-click Certificates.

Right-click the Trusted Root Certification Authorities store.

Click Import to import the certificates and follow the steps in the Certificate Import Wizard.

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc754841(v=ws.11)?redirectedfrom=MSDN#BKMK_addlocal

https://www.ibm.com/support/pages/node/372873

https://www.ibm.com/support/pages/node/563063

https://www.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

https://en.wikipedia.org/wiki/Root_certificate
https://www.thesslstore.com/blog/root-certificates-intermediate/
https://comodosslstore.com/resources/what-is-a-root-ca-certificate-and-how-do-i-download-it/

Product:
Cognos Controller 10.4.2
Cognos Analytics 11.0.13
Microsoft Windows 2012 server

Problem:
When run a standard report you get a error inside Cognos Controller only on a new database.

Error:
RQP-DEF-0177 An error occurred while performing operation
‘sqlPrepareWithOptions’ status=’-56′.
UDA-SQL-0115 inapproproeate SQL request
UDA-SQL-0564
invalid column name ‘lookupwebgrpename’

Possible Solution:
That you have setup the data source manually in Cognos Connection and are missing information.

1 Click Manage and go to the IBM Cognos Analytics Administration Console
2 Click on the Configuration Tab
3 Click on data source name and then on more… icon
4 Click Set properties
5 Click on Connection Tab
6 Click the pencil icon for Edit the connection string
7 Check that the sever name to database is correct.
8 If database name is missing – enter the correct database name.


9 Click OK
10 Click OK
11 Click Test the connection icon, far to the right.
12 Click Test
13 Click Close
14 Click Close
15 Click OK
16 Click OK

More information:
https://www.ibm.com/support/pages/error-rqp-def-0177-sqlpreparewithoptions-status-56-when-running-some-standard-reports
https://www.ibm.com/support/pages/error-rqp-def-0326-user-defined-sql-not-permitted-user-who-has-identity-when-running-controller-standard-reports
https://www.ibm.com/support/pages/how-manually-create-cognos-data-source-use-controller-standard-reports

Product:
Cognos Analytics 11.1.3
Planning Analytics Tm1 Perspective

Problem:
After adding more namespaces in Cognos Analytics to be able to select from two domains (active directory) the user of TM1 Perspective get a blank drop down page in the CA11 login.
Only a few users are affected, user with different version of Microsoft Excel got it working.

Suggested Solution:
On the client computer or on the citrix server where TM1 Perspective Excel plug in is installed and used, take a backup of the users windows registry.

Enter REGEDIT in the START search field.
From inside REGEDIT expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\MAIN\FeatureControl and right click and select export;

Save the file, and repeat the backup for the other branches listed below.

Then apply this values in REGEDIT to solve the issue.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\MAIN\FeatureControl\FEATURE_BROWSER_EMULATION]
“Excel.exe”=dword:00002af8
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\InternetExplorer\MAIN\FeatureControl\FEATURE_BROWSER_EMULATION]
“Excel.exe”=dword:00002af8
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\MAIN\FeatureControl\FEATURE_USE_WINDOWEDSELECTCONTROL]
“Excel.exe”=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\InternetExplorer\MAIN\FeatureControl\FEATURE_USE_WINDOWEDSELECTCONTROL]
“Excel.exe”=dword:00000000

More Information:
https://www.ibm.com/support/pages/namespace-drop-down-appears-blank-cognos-analyticscognos-bi-login-page-while-attempting-login-planning-analytics-cam-authenticated-server-perspectives
https://www.ibm.com/support/pages/custom-sign-view-could-not-be-found