Cognos Analytics 11.1.7
Planning Analytics 2.0.9
Microsoft Windows 2019 Server
How setup Windows Kerberos login for Cognos products?
Here describes what Kerberos is:

Setup Cognos Analytics with a IIS gateway and make it work for Single Sign On (SSO) to login.

You need to create a windows domain account, that is local administrator on the Cognos server where the Cognos Content Manager function is, and run the IBM Cognos service with this account.

The account must be added with domain\name format, without use of @.
The same service account must run the IIS server application pool used by CA11.

Go to Internet Information Service Manager, and expand Application pools. Mark ICAPool and click on Advanced Settings. Click on Identity and select Custom Account. Click Set and enter the domain\name account and password. Click OK.
Restart IIS.
The service account must have “Trust this user for delegation to any service (Kerberos only)” set in Active Directory. Ask the IT department to set this on the Windows Domain Controller.

Constrained delegation is not recommended.
Ensure that the cognos service account have NTFS read/write/execute rights on the cognos folders.
Right click on folder C:\Program Files\ibm\cognos\analytics and select properties.
Check the security tab that the local Administrator group have full rights.

Go to Computer Management in Control panel – Administrative Tools. Expand Local User and Groups – Groups. Check what groups and accounts are in the Administration group on the server.

Ensure that the cognos service account is part of a domain group that is included in the local administrator group. Does not need to be domain admins group, but must be the same group.

On the Windows Domain Controller you must run the SETSPN command to create the Service Principal Name.

Enter the webserver and the cognos bi server to the service account. In our case it is the same server.
You need to add all the ways the system connect to the server e.g. HOSTNAME and FQDN.
In our example we use setspn -s HTTP/win2019.lab.pacman LAB\cognosservice

setspn -s HTTP/websrv_aliasname  domain\cognosserviceaccount
setspn -s HTTP/appsrv_FQDN  domain\cognosserviceaccount
setspn -s HTTP/appsrv_HOSTNAME  domain\cognosserviceaccount

Use the servername in cognos configuration for the setspn command above.

Use setspn -L domain\cognosserviceaccount to see the current values in use.

Some common switches used with SetSPN:

-a    Add an entry to an account (explicitly)
-s    Add an entry to an account (only after checking for duplicates first)
-d    Delete an entry from an account
-x    Search the domain for duplicate SPNs
-q    Query the domain for a specific SPN

In IIS manager on the Cognos Gateway server; ensure that Anonymous Authentication is on IBMCOGNOS folder.

Go to \bi folder, and click on Authentication. Select Windows Authentication and click enable.
Disable Anonymous Authentication on the \bi folder.

Click on Providers for the \bi folder, and remove NTLM so you only have Negotiate.

Repeat on \sso folder, so it also only have Negotiate as Windows enabled Providers.

For \sso folder click on Configuration Editor.

Select in the drop down menu for section – system.webServer – security – authentication – windowsAuthentication.

To get this dialog up for the sso folder.

Set true to “useAppPoolCredentials” and “useKernelmode”.
Go to the \bi folder and set the same values.

Click on Configuration Editor icon – select system.webServer – security – authentication – windowsAuthentication. Set true to “useAppPoolCredentials” and “useKernelmode”.

If you use Oracle or DB2 as content store database, you are all set. But if you use Microsoft SQL server you need to add setspn for the service account that run the SQL services.

Ask the SQL DBA to ensure the service account for SQL server is using domain\account notation as above. Kerberos will not work with Local System as the service account for Microsoft SQL database.
You need to check in cognos configuration how Cognos Analytics connects to the content store database. Open Cognos Configuration on your Cognos Content Manager server.

Note down IP or HOSTNAME that is in use to connect to the SQL server. This will be used in the setspn command.  Enter in our case setspn -s MSSQLSvc/ LAB\cognosservice

setspn -s MSSQLSvc/sqlsrv_FQDN  domain\SQLServiceAccount
setspn -s MSSQLSvc/sqlsrv_FQDN:instancename  domain\SQLServiceAccount
setspn -s MSSQLSvc/sqlsrv_FQDN:1433  domain\SQLServiceAccount

You need to enter all variants of the SQL server name to the setspn command.

Restart the windows server for Cognos Analytics to ensure the domain changes have taken affect.

To check that Kerberos is in use, activate AAA tracing for a short period in Cognos Analytics.

Login to CA11 as administrator and click on Manage – Configuration.

Click on Diagnostic Logging.

Click on AAA and Apply.
Logout from CA11 and close the browser.
Start the web browser again and go to http://win2019.lab.pacman/ibmcognos/
after the sso have let you in, go to the Cognos Analytics Content Manager server.
Open the C:\Program Files\ibm\cognos\analytics\logs\cognosserver.log file in notepad++

Go to the end of the file and from search menu select find and enter AUTH_TYPE.
Scroll to the right, and if kerberos is used it should say:
<value xsi:type=”xsd:string”>Negotiate</value>

Close the log file.
Go back into CA11 portal.
Go to manage – configuration – diagnostic logging.

Select Default Logging and click Apply. This is important as the logging can make the cognos system slower.

Planning Analytics (TM1) will use kerberos now too, as long they are setup to use CAM security.

More information:

Overview of Service Principal Name and Kerberos authentication in SQL Server

Cognos Analytics 11.1.3
Microsoft Windows 2016 server
Login dialog when user try to access CA11 website

Check that the server name is in local intranet sites or trusted sites in internet options.

At most company’s this is controlled by group policy in the network, ask the IT department to add the CA server name and DNS alias to the local intranet site.

The new Edge that use chromium, will only allow SSO for servers in Local Intranet zone. But Internet Explorer on the same computer will allow SSO for servers both in Local Intranet Zone and Trusted Zone.

In Windows only, if the AuthServerWhitelist setting is not specified, the permitted list consists of those servers allowed by the Windows Zones Security Manager (queried for URLACTION_CREDENTIALS_USE). By default, this includes servers in the Local Machine or Local Intranet security zones. For example, when the host in the URL includes a “.” character, by default it is outside the Local Intranet security zone). This behavior matches Internet Explorer and other Windows components.

You have to search the internet to find where you can set the Edge Zone security in the local windows.

There is also granular settings in Custom level there you should uncheck “automatic logon only in intranet zone”.

Then you can have the cognos analytics site in Trusted tab instead.

Steps for Adding Trusted Sites in old Browser

Google Chrome > Adding Trusted Sites

  1. Click the Chrome Menu icon on the far right of the Address bar.
  2. Click on Settings, scroll to the bottom and click the Show Advanced Settings link.
  3. Click on Change proxy settings (under Network)
  4. Click the Security tab > Trusted Sites icon, then click Sites.
  5. Enter the URL of your Trusted Site, then click Add.
  6. Click Close > OK.

Mozilla Firefox > Adding Trusted Sites

  1. Click the menu icon in the upper right-hand corner of the browser.
  2. Click Options.
  3. Click Privacy and Security.
  4. Scroll down to the “Permissions” section, and click on Exceptions to the right of “Warn you when websites try to install add-ons.”
  5. Type the trusted sites into the “Address of website” field.
  6. Click Allow.
  7. Click Save Changes.

 Safari > Adding Trusted Sites

  1. At the top of the screen, click Bookmarks.
  2. Click “Add Bookmark…”
  3. Click “Top Sites” from the dropdown menu.
  4. Click Add.

 Internet Explorer 9, 10 and 11 > Adding Trusted Sites

  1. Click Tools, click Internet Options, and then click the Security tab.
  2. In the Select a Web content zone to specify its current security settings box, click Trusted Sites, and then click Sites.
  3. If you want to add sites that do not require an encrypted channel, click to clear the Require server verification (https:) for all sites in this zone check box.
  4. In the  Add this Web site to the zone box, type the URL of a site that you trust, and then click Add.
  5. Repeat these steps for each site that you want to add to the zone.
  6. Click OK two times to accept the changes and return to Internet Explorer.

Microsoft Edge > Adding Trusted Sites

  1. Search in the Start Menu for the Control Panel.
  2. Click or double-click the Internet Options icon.
  3. In the Internet Properties window, click the Security tab.
  4. Select the Trusted sites entry and click the Sites button.
  5. Enter the address for the trusted website in the Add this website to the zone text field.
  6. Click the Add button, then click OK to save the website addition.

More information:

Security Zones in Edge

Cognos Analytics 11.0.13
Microsoft Windows 2016 server

After change of custom certificate on IIS and in CA11 dispatcher level, in file CAMkeystore. The still show wrong certificate.
When you examine the camkeystore.jks file with ikeyman.exe you find that the root certificate is used instead of the server certificate.

Possible solution:
When using custom certificate for SSL (TLS) communication on port 9300, you need to only add this certificate to the CAMkeystore file.
First you set HTTPS in cognos configuration, then when you press save inside Cognos Configuration for CA11, the keystores files are created.
For example IBM Cognos Configuration > Security > Cryptography > Cognos > Certificate lifetime in days. This value will set the cognos server certificate (encryption) in the keystore to last this long. The internal CA certificate is created to last a year longer.
After the cognos keystore files are created, you can add the custom certificates to the file with ikeyman.exe.

You must add the certificate in correct order:
Root – first
Intermediate – second
Server Cert – last

Make a backup of the C:\Program Files\ibm\cognos\analytics\configuration\certs folder before you start.

Go to C:\Program Files\ibm\cognos\analytics\jre\bin
Launch ikeyman.exe as administrator ( by right click and select run as administrator)
Open the following file C:\Program Files\ibm\cognos\analytics\configuration\certs\CAMkeystore
Type: PKCS12
File name:CAMKeystore
Location:  C:\Program Files\ibm\cognos\analytics\configuration\certs
Password: NoPassWordSet (default)

Select Signer Certificates from the drop down list.
Click on Add.
Import your root.cer first.
Then import your intermediate.cer second.
Then go back to Personal Certificates from the drop down list.
Mark encryption, and click on Rename. Change the name to old-encryption.
Click on Import button. Select Import key.

Select you certificate file with your server certificate, that contain the DNS alias for your server.
Enter your password when you import the file.
Set the name of the server cert to encryption.
Exit/Close the ikeyman program. Any changes are saved directly to the CAMkeystore file.

Now go into Cognos Configuration and click save. Then start the Cognos service from inside Cognos Configuration. Now the file CAMkeystore.jks is created/update with the custom certificates.
Test to browse to the

You may need to also add the custom certificate to other places, depending on you system setup.

(Internal CA)
It is Cognos specific certificate authority.  You can check the content with ikeyman tool.

View ‘ca’ certificate under Personal Certificates.  Double click to see the values of the certificate.
When ‘encryption’ certificate is expired, you cannot log in to Cognos Analytics.

If you use PA, you need to add the Planning Analytics certificate to the CA11 key store.

More information:

Planning Analytics 2.0.6
Microsoft Windows 2016 server

After change of Certificate for Cognos Analytics 11 dispatcher level. The user can not login in TM1 Architect.  This when you use CAM security (IntegratedSecurityMode=5).
You get error message like: SystemServerClientNotFound

When you update the CA11 Websphere (dispatcher) with a custom certificate, you need to add the root and intermediate certificate to the other parts like TM1 servers (planning analytics).

Download the root and intermediate certificate to BASE-64 cer files.
Copy the files to the TM1 server.
Go to a COMMAND prompt as administrator.
Go to folder C:\Program Files\ibm\cognos\tm1_64\bin64
Run a command similar to this:

gsk8capicmd_64 -cert -add -db “D:\Program Files\ibm\cognos\tm1_64\bin64\ssl\ibmtm1.kdb” -stashed -label caRoot -file “C:\temp\rootcert.cer” -format ascii -trust enable

gsk8capicmd_64 -cert -add -db “D:\Program Files\ibm\cognos\tm1_64\bin64\ssl\ibmtm1.kdb” -stashed -label caIntermediate -file “C:\temp\intercert.cer” -format ascii -trust enable

Then you need to restart the TM1 service instances, for the change to take effect.

More Information:

Planning Analytics
Microsoft Windows 2019 server

How setup SSL (TLS) in Planning Analytics Spreadsheet Services?


Get a custom pfx file from your certification authority for your server.
Go to your PA TM1WEB server and place the file in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl

Stop the IBM Planning Analytics Spreadsheet Service.
Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\ in notepad++

Update this row to set your https port
<httpEndpoint id=”defaultHttpEndpoint” httpPort=”-1″ httpsPort=”9510″ host=”*” removeServerHeader=”true”>
Add this row to point out the certificate pfx file to use
<keyStore id=”defaultKeyStore” location=”${wlp.user.dir}/../../bin64/ssl/cert.pfx” password=”cognos” />
Change cognos to your password.
Save the file as server.xml
In a command prompt go to folder C:\Program Files\ibm\cognos\tm1web\jre\bin
Enter this command to import the standard TM1 server cert to the new keystore
keytool -importcert -keystore ..\..\bin64\ssl\cert.pfx -storepass cognos -storetype pkcs12 -noprompt -alias ibmtm1 -file ..\..\bin64\ssl\ibmtm1.arm

Start IBM Planning Analytics Spreadsheet Services

Update the C:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web\tm1web.html file on your Cognos Analytics server to have the new HTTPS value:

Save the file.
If you miss above step you get the error:

The TM1Web service parameter was not specified or is not one of the configured locations

Test from Chrome web browser by go to

If it works, you have done a good job.

If you use the self sign test certificate you get below screen, as the certificate is not trusted by the browser. Self signed certificate works best with TM1 native security.

Do this to get away from above error in testing.
To encrypt the password in the server.xml file do this steps:

Ensure that the cert.pfx file is in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl

Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\ in Notepad++
Add the line (with your own key password):
Start a command prompt as administrator.

Run set JAVA_HOME=C:\Program Files\ibm\cognos\tm1web\jre\ to temporary set the JAVA_HOME for next command
Move to folder C:\Program Files\ibm\cognos\tm1web\wlp\bin
Run command (to encrypt the value in key-store)
securityUtility.bat encode –encoding=aes –key=VeryStrongandSecurePasswordKey cognos
(you add the -key password you defined in bootstrap file, and then the password used today to access the cert.pfx file)

Copy the response to notepad
Open C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\server.xml in notepad++
Update the line (to include the new password)
<keyStore id=”defaultKeyStore” location=”${wlp.user.dir}/../../bin64/ssl/cert.pfx” password=”{aes}AIm6d2W+Hk0JBXaWVrJSvq+AGyBDkec/kdUiXAu5nKoI” />

Save the file and restart Planning Analytics Spreadsheet Services.

Now the password to the keystore (pfx) is not in cleartext in the server.xml file.

You can check for errors in file C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\logs\console.log

Launching tm1web (WebSphere Application Server on IBM J9 VM, version – pwa6480sr6fp15-20200724_01(SR6 FP15) (sv_SE)
[AUDIT ] CWWKE0001I: The server tm1web has been launched.
[err] log4j:WARN No appenders could be found for logger (org.apache.axis.transport.http.AxisServlet).
[err] log4j:WARN Please initialize the log4j system properly.
[err] log4j:WARN See for more info.

How to create a keystore for testing:
One your laptop install openssl from here – get the file Git-2.23.0-64-bit.exe. Run the installation with all default values.

Create a new folder (c:\workarea)

Create text file with above content, replace with your servername and location.
Save the file in c:\workarea folder.
Start a command prompt as administrator. Go to folder C:\Program Files\Git\mingw64\bin
Enter to create the self signed certificate:
openssl.exe req -x509 -nodes -days 1095 -newkey rsa:2048 -keyout C:\workarea\cert.pem -out C:\workarea\cert.pem -config C:\workarea\san.txt

Enter to create the pfx file:
openssl.exe pkcs12 -export -out C:\workarea\cert.pfx -in C:\workarea\cert.pem -name “win2019pa” -passout pass:cognos

Replace win2019pa with your servername, and cognos with your password of choice.

Copy the cert.pfx file to your PA server and place in folder C:\Program Files\ibm\cognos\tm1web\bin64\ssl, then do the rest at top of this page.


This option outputs a self signed certificate instead of a certificate request.


Enter this to check a pfx for its content:

keytool -v -list -storetype pkcs12 -keystore cert.pfx

More information:

TM1s.cfg & How to Create a TM1 Model – A Best Practice Guide

Planning Analytics 2.0.9
Planning Analytics Workspace 55
Microsoft Windows 2016 server

What to add cube security for new cognos groups from file.
You have created some cognos groups – GroupA and GroupB and filled them with Active Directory users.

You have added the group into TM1 Architect, to see that they are visible. This is done by right click on the Tm1 application and select security – clients/groups.

You have tested to add manually in TM1 architect, the values in the security cube.


This can be solved in many ways, this is one example.
You have a text file with the groups and the new values. Here you add the other groups and there values you want to be setup.

Columns are cube to update, cube, cognos group, access rights.

Go to PAW. Login to your TM1 Instance. Go to Processes and right click and select Create Process.

Enter a name, in our example ImportSecurityTI.

Click Create.

Click on file.

Drag you text file to the drop area, to load the file into the system.
This will copy the file to a folder under your data folder.

Click Next.  Select the delimiter you have in your file. Here we use comma.
Click preview.

Here we have a simple file, all columns are strings and we keep the default variables values of V1 to V4.
Click validate and save. Click on script.

Now enter code similar to this to make it populate the cube;

#Section Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****

# setup the file to import

# ASCII for comma is 44
# ASCII for quates is 34

# place the file in below folder and paw will find the file
# full path to the file and name – this is for Tm1 architect to find the file
DatasourceNameForClient=’C:\Program Files\ibm\cognos\tm1_64\samples\tm1\24Retail_CAM\data\model_upload\CubeSecurity3.txt’;

# set default values
sEND = ‘”)’;
sCUBE= ‘}CubeSecurity’;

#Section Metadata
#****Begin: Generated Statements***
#****End: Generated Statements****

# remove the ### for the debug lines to write variables to text file

### ASCIIOutput (‘c:\temp\debugout1.txt’, v1, v2, v3, v4 );
# check if string contain : (colon)
# SCAN(find , in string)
nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);
# remove all before
# SUBST(string, beginning, length)
v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));

# add CAMID to the group (column 3)
# check that it does not already have : (colon)
nSTART= scan ( ‘:’,v3);
if (nSTART = 0);
# add value before to look like this “CAMID(“:GroupA”)”
# SUBST(string, beginning, length)
v3 = sNAMESPACE | v3 | sEND;

### ASCIIOutput (‘c:\temp\debugout2.txt’, v1, v2, v3, v4 );
# write values to the cube
# CellPutS (String, Cube, element1, element2, elementn )

#Section Data
#****Begin: Generated Statements***
#****End: Generated Statements****

#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****

Click on validate – save – run buttons.

If all apostrophes are correct it should work fine.

More information:

nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);

This will find the position in variable v1 where there are a colon. If there is none, then the value in nSTART is zero. At if we test that if not zero then do next line.

v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));

Here we replace variable v1 with a part of its content, we take one character to the right from the nSTART position and until end of string ( length of sting minus the start position).

v3 = sNAMESPACE | v3 | sEND;

The pipe character is to add strings together in TI processes. We add the predefined variables sNAMESPACE and sEND around the variable v3, to get it to look correct.

Concatenating Data in TM1 – How to Concatenate Variables in a TI or Rule

If you do not add the groups in security dialog before you run the script you get this error:

Process completed with errors
“24Retail_CAM:}CubeSecurity”,”Capital”,”GroupA”,”WRITE”,Data Source line (1) Error: MetaData procedure line (26): Invalid key: Dimension Name: “}Groups”, Element Name (Key): “CAMID(“:GroupA”)”

Planning Analytics 2.0.6
Microsoft Windows 2016 server

How check the SSL certificate in TM1WEB keystore?

On the PA tm1WEB servern, start ikeyman.exe as administrator, from folder C:\Program Files\ibm\cognos\tm1web\jre\bin.
Click on open icon.

Open C:\Program Files\ibm\cognos\tm1_64\configuration\certs\CAMkeystore file.
Click OK and enter password: NoPassWordSet
Double click on the line encryption under Personal Certificates.

Here you can see when the server certificate expires. Click OK to close the dialog.
Select Signer Certificates in the Key database content drop-down list.
Find you company root certificate in the list, name can be anything you named it at setup.

When your find your company’s certificate, double click to see how long it is valid.
You can here use the ADD button to add new certificates if needed.

Important that the personal certificate is named encryption.

You can turn the use of SSL on and off with settings in Cognos Configuration. See more at this link:

More information:

Planning Analytics and newer version have a different key-store for TM1WEB certificates.

IBM Planning Analytics: New Features