IBM Support

How to configure Controller to use TLS (formerly SSL) to connect to a DB2 database (in transit database encryption)

How To


Summary

Customer would like to configure their Controller system so that all (DB2) database network communication ("in transit") is encrypted (via TLS).

Objective

Configure all DB2 database traffic to be encrypted by TLS.

Environment

Controller databases hosted on DB2 database server.

  • TIP: For the steps required for other database platforms (MS SQL, Oracle) see separate IBM Technotes (links below).

Steps

NOTES:

  • The following steps are based on Controller 10.3.1 (installed in the default location) and DB2 11.1.
    • For other/different versions (or if installed in non-default locations) , the instructions may need to be modified slightly.
  • Naturally all changes should be done during a period of downtime (no users on the system).
  • Ensure the FQDN (for example myserver1.companyname.com, not the NetBIOS name myserver1) of the DB2 server machine is used in all places
  • If you are forcing the system to use TLS 1.2 (not the default TLS 1.0) the you will also need to perform the steps inside separate Technote #883036.

PART ONE - Test without encryption

Make sure that everything is working (in Controller) using the default unencrypted settings.

  • In other words, do not proceed until you are sure that everything is working OK without TLS.

   

PART TWO - Configure DB2 server to use a TLSimage-20191120122732-1 certificate

For information on this subject, see separate (DB2) documentation.

  

PART THREE - Configure DB2 client to use encryption (via the DB2 GSKit)

Install and configure the DB2 GSKit (Global Security Kit).
  • This must be done both on the Controller application server and also on the Cognos Analyics (BI) report server too
  • TIP: For instructions, see link at the end of this Technote.
  
TIP: For the purpose of this Technote, we shall assume:
  • the DB2 server is configured to communicate securely over port:   50001
  • the DB2 keystore location is:    E:\db2keystore\mykeystore2.p12
  • the DB2 keystore stash file location is:    E:\db2keystore\mykeystore2.sth
  • the DB2 keystore was imported into a Java Key Store (JKS) located at:   E:\db2keystore\keystore.jks
  

PART FOUR - Configure Cognos Analytics (Content Manager) to use encryption

For instructions, see Cognos Analytics documentation such as "Using SSL for database connections in IBM Cognos Configuration for an IBM Db2, Informix® database" (link at the end of this Technote).

    
PART FIVE - Configure Controller 'main' systems

On your Controller application server convert the UDL to use encryption by:

1. Browse to your UDL files

  • TIP: By default, these are located here: C:\Program Files\ibm\cognos\ccr_64\data

2. Then open the relevant UDL file (for example 'test.udl') in NOTEPAD.EXE

3. Add the following text (which allows .NET components to use SSL):

Security=SSL;SSLClientKeystoredb={keystoreLocation};SSLClientKeystash={KeyStoreStashFileLocation}

4. Add the following text (which database conversion to work):

ccrSSLClientKeystoredb={JKSFileLocation};ccrSSLClientKeystorePassword={JKSFilePassword}

For example, the finished UDL file might look similar to:

-------------------------------------------------------

[oledb]
; Everything after this line is an OLE DB initstring
Location=rodb2encrypt:50001;Persist Security Info=True;Data Source=CCR01;Password=MyPassword;User ID=fastnet;Provider=IBMDADB2.DB2COPY1;Security=SSL;SSLClientKeystoredb=E:\db2keystore\mykeystore2.p12;SSLClientKeystash=E:\db2keystore\mykeystore2.sth;ccrSSLClientKeystoredb=E:\\db2keystore\\keystore.jks;ccrSSLClientKeystorePassword=changeit

-------------------------------------------------------

IMPORTANT: If you make any changes inside 'Database Connections' (inside Controller Configuration) then these manul edits (customisations) will be lost! Therefore you must remember to manually edit the UDL file after any future change you make inside 'Database Connections'.

   

NOTE: If using Controller 10.3.1 then (inside Controller Configuration) if you click on the tick ("check connection") button then the connection will show a failure.

  • However, if using a later version of Controller (for example 10.4.1) then it will show success.

PART SIX - Configure Controller 'JAVA' components to use encryption

On your Controller application server:

1. Locate the file:  ccr-dbTypes.properties 

  • By default this is located here: C:\Program Files\ibm\cognos\ccr_64\server\integration)
  • As a precaution, create a backup of the file
  • Use NOTEPAD to edit the file, and add the following lines at the end:

DB2.name            = DB2
DB2.driver            = com.ibm.db2.jcc.DB2Driver
DB2.url                = jdbc:db2://%s%s/%s:sslConnection=true;sslTrustStoreLocation={JKSLocation};sslTrustStorePassword={JKSPassword};
  

NOTE: 

  • Replace JavaKeyStoreLocation with the location of the Java keystore containing the DB2 certificate
    • This must be double escaped, for example:         C:\\javaKeystore.jks
  • Replace JavaKeyStorePassword with the password of the respective keystore.

2. Restart the Windows service 'IBM Cognos Controller Java Proxy'

    

PART SEVEN - Controller Web

The 'SyncDB' utility (see Technote 289719) will only create unencrypted entries (inside datasources.xml). Therefore after using that utility you must always remember to manually re-edit it.

There are two methods you can use to configure encryption for Controller Web:

Method #1 - Add the DB2 certificate to the keystore holding the private key that allows Webpshere to expose the services  via HTTPS 

1. Use a command similar to the one below to import the DB2 client certificate to the java keystore. 
keytool -importkeystore -deststorepass changeit -destkeystore "C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security\key.jks" -srckeystore "E:\db2keystore\mykeystore2.p12" -srcstorepass changeit -srcstoretype PKCS12
NOTE: The default keystore is located at “…fcmweb\wlp\usr\servers\fcm.web\resources\security\key.jks" and created the first time the server is started up having the ssl-1.0 feature enabled
2. Use NOTEPAD to open the following file:   ..\ccr_64\fcmweb\wlp\usr\shared\config\datasources\datasources.xml
3. Add the following to the properties tag of each datasource:     sslConnection="true" 
  •  NOTE: If you re-run the SyncDB utility, then this will overwrite this parameter (so you will need to repeat the change)
4.  Edit your file:   server.xml
  • ensure the following is enabled (not commented):    <feature>ssl-1.0</feature>
  • ensure that the keystore is defined, for example:   <keyStore id="defaultKeyStore" password="changeit" /> 
    
     

Method #2

1. Browse to here: C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\shared\config\datasources
2. Open the following in NOTEPAD:    datasources.xml
3. Add the following attributes to each of the Database Connnections
  • sslConnection="true"
  • sslTrustStoreLocation="{JKSLocation}"
  • sslTrustStorePassword="{JKSPassword}"/>
For example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<dataSource id="DB2_Encrypt" jdbcDriverRef="db2" jndiName="jdbc/DB2_Encrypt" type="javax.sql.DataSource">
<properties.db2.jcc databaseName="CCR01" deferPrepares="false" password="MyPassword" portNumber="50001" serverName="MYDB2ENCRYPT" user="fastnet" sslConnection="true" sslTrustStoreLocation="E:\db2keystore\keystore.jks" sslTrustStorePassword="changeit"/>
</dataSource>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   

PART EIGHT - ODBC (Consolidations, optimisations)

1. Launch REGEDIT
2. Navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\VB and VBA Program Settings
3. Create new registry subkey:   CognosController
4. Create subkey:   Settings
5. Add  new String key DynamicODBC with the value NO
image-20191121092653-1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The instructions in this section may vary depending on the DB2 client type you have installed on the Controller application server.
6.  Configure the DB2 client to trust the server certificate:
  • Open a command prompt as administrator
  • type the command:    db2cmd
  • In the DB2 Command window (that has opened) type commands similar to the following:
    update dbm cfg using SSL_CLNT_KEYDB {PATH_TO_P12_CERTIFICATE}
    update dbm cfg using SSL_CLNT_STASH {PATH_TO_STASH_FILE }
 
Example:
    Update dbm cfg using SSL_CLNT_KEYDB E:\db2keystore\mykeystore2.p12
    Update dbm cfg using SSL_CLNT_STASH E:\db2keystore\mykeystore2.sth
 
7.  Catalog the DB2 server as a node, using the SSL protocol and the port used for Secure communication, similar to:
db2 catalog tcpip node {nodeName} remote {DB2Server_IP/Or_machine_name} server {SSL_PORT} security ssl 
     
Then map the Controller databases on the node. Each of the Controller databases defined need to be mapped, similar to:
    db2 catalog db {Controller_DB2_database} as {Alias} at node {nodeName}
Example:
    db2 catalog tcpip node SSLNODE remote mydb2encrypt server 50001 security ssl
    db2 catalog database CCR01 as CCR01SSL at node SSLNODE
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Then define a 32 bit System DSN (using the previously mapped databases) by performing the following steps:
8. Run the following:     C:\WINDOWS\SysWOW64\odbcad32.exe
7. Inside the 'ODBC Data Source Administrator (32 bit)' open the tab:   System DSN
8. Click 'Add' and create new System DSN
  • Choose the DB2 ODBC driver
  • Configure the name as:   DSN{UDLName}
  • Create one for each and every alias (previously created), for example:
image 5733
9. Select the previously-created Data Source and then:
  • Click Configure
  • Open tab:  Advanced Setting
  • Add the following parameters:
    • Patch1 = 1024
    • MapDecimalFloatDescribe = SQL_C_DOUBLE ( 3 )
  • Click Apply
  • Click OK.
NOTE: A System DSN must be created for each separate Controller database (defined in Controller configuration)
 
​ 
   

PART NINE - Standard Reports

On your Cognos Analytics server, perform similar tasks to what you did earlier on the Controller application server:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The instructions in this section may vary depending on the DB2 client type you have installed on the Controller application server.
1.  Configure the DB2 client to trust the server certificate:
  • Open a command prompt as administrator
  • type the command:    db2cmd
  • In the DB2 Command window (that has opened) type commands similar to the following:
    update dbm cfg using SSL_CLNT_KEYDB {PATH_TO_P12_CERTIFICATE}
    update dbm cfg using SSL_CLNT_STASH {PATH_TO_STASH_FILE }
 
Example:
    Update dbm cfg using SSL_CLNT_KEYDB E:\db2keystore\mykeystore2.p12
    Update dbm cfg using SSL_CLNT_STASH E:\db2keystore\mykeystore2.sth
 
2.  Catalog the DB2 server as a node, using the SSL protocol and the port used for Secure communication, similar to:
db2 catalog tcpip node {nodeName} remote {DB2Server_IP/Or_machine_name} server {SSL_PORT} security ssl 
     
Then map the Controller databases on the node. Each of the Controller databases defined need to be mapped, similar to:
    db2 catalog db {Controller_DB2_database} as {Alias} at node {nodeName}
Example:
    db2 catalog tcpip node SSLNODE remote mydb2encrypt server 50001 security ssl
    db2 catalog database CCR01 as CCR01SSL at node SSLNODE
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Then modify the connection string for each Controller database by doing the following:

3. Launch the CA website, locate the relevant data source

4. Edit it

5. Remove the existing DB2 Connection String and put the Alias (used to map the Controller database through a node configured with SSL) in the DB2 database name

Example
Before:
 image 5739
    
After:

image 5740

 

PART TEN - FAP Server

If using FAP, then you must also perform the following:

1. On the Controller application server (where the Windows service 'IBM Cognos FAP Service' is running), browse to here: C:\Program Files\IBM\cognos\ccr_64\server\FAP\

2. Edit this file in NOTEPAD:    FAPService.properties

3. To enable secure connection to the FAP database, add a line similar to:

db.extended.properties=Provider=IBMDADB2.DB2COPY1;Security=SSL;ccrSSLClientKeystoredb={JKSLocation};ccrSSLClientKeystorePassword={JKSPassword}

4. Also, add a line for each and every one of your FAP sources, similar to:

source.test.connection.property.Security=SSL
source.test.connection.property.ccrSSLClientKeystoredb={JKSLocation}
source.test.connection.property.ccrSSLClientKeystorePassword={JKSPassword}

For example:

image 5736

   

PART ELEVEN - FAP Client

1. On every device that is using the FAP client, you will need to modify the DB2 client (in a similar way to described above).

2. Launch the FAP client

3. In the Server field, after the Server name add the TCP port (onto which DB2 is listening for encrypted connections) preceded by a colon

4. In the Database field add the following parameters (leave the first semicolon as in the caption below)

Security=SSL;SSLClientKeystoredb={DB2_p12_certificate_location};SSLClientKeystash={P12_Stash_File_location}
 

NOTE:

  • If using Controller 10.3.1 then you add the above after the database name, for example:
image 5737
  • However, if using Controller 10.4.0 (or later) then use the separate field (where the encryption parameters can be put, without the need to append them to the database field)
5. Click on the tab 'Sources'
  • Highlight the relevant source
  • Click 'Edit'
  • Modify the value for 'port' to use similar settings as chosen above (in steps 4 and 5), for example:
image 5738
NOTE:
  • If using Controller 10.3.1, then (after adding this new parameter and closing the window) this new/additional parameter will be saved in the database. HOWEVER, the new parameter will not be visible when the window is opened a second time! This is a known limitation in Controller 10.3.1
  • If using Controller 10.4.0 (or later) then this limitation is no longer present: the encryption parameters can be put in a separate field and will be visible when the window is re-opened.

 

PART TWELVE - Planning Analytics (TM1) server

1. On the PA server, edit the ODBC connection 'FAP'

2. Tick the option 'Use Strong Encryption for Data':

image-20191121115406-1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optional - PART THIRTEEN - Using TLS 1.2
This part is optional.
By default, IBM Java has TLS 1.0 enabled. To enable and force TLS 1.2 you must append two java runtime arguments:
-Dcom.ibm.jsse2.overrideDefaultTLS=true
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12
 
TIP: For more information, see separate IBM Technote #883036.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information

For more information on how to secure other parts of the Controller architecture (using SSL) see separate IBM Technote #2004921.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z0000000AxnAAE","label":"Documentation"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
27 August 2020

UID

ibm16323587

Page Feedback