IBM Support

How to configure Controller to use TLS (formerly SSL) to connect to an Oracle (in transit database encryption)

How To


Summary

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

Objective

Configure all Oracle database traffic to be encrypted by TLS.

Environment

Controller databases hosted on Oracle database server.

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

Steps

NOTES:

  • Controller FAP functionality is currently unsupported with Oracle encryption
  • The following steps are based on Controller 10.3.1 (installed in the default location) and Oracle 12.1.0.2.0.
    • 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 cowhand1.castle.fyre.ibm.com, not the NetBIOS name cowhand1) of the Oracle 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 Oracle server to use a TLSimage-20191120122732-1 certificate

For information on this subject, see third-party (Oracle) official documentation.
  • For example, see the 'SSL With Oracle JDBC Thin Driver' white paper (www.oracle.com/technetwork/database/enterprise-edition/wp-oracle-jdbc-thin-ssl-130128.pdf) published by Oracle provides information for configuring SSL for the database server and client.

  

PART THREE - Configure Oracle client (via the tnsnames.ora and sqlnet.ora files, plus the correct JAR file) to use encryption

NOTE:
  • The following must be done both on the Controller application server and also on the Cognos Analyics (BI) report server too
  • It must be done inside both the Oracle 64-bit client installation (which is used for most Controller/CA functionality) and also the Oracle 32-bit client too (which is used for Controller consolidations, and CA standard report functionality via the CQE).
The connection encryption is handled mostly via the tnsnames.ora and sqlnet.ora files (inside the Oracle client installation on the application server(s).
  • Specifically, configure them to use the protocol TCPS ("TCP with SSL")
For example:
sqlnet.ora
# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY =C:\ora_wallet\mywallet2)
    )
   )
tnsnames.ora
OR12R102Sec.FRANGONET.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 9.20.210.107)(PORT = 1522))
    (CONNECT_DATA =
     (SERVICE_NAME = OR12R102)
    )
   )

  

Next, ensure that your Oracle client's JAR file is able to support TLS 1.2:

  • Ensure you have the required Oracle patches installed.
    • For example, when using Oracle 12.1.0.2 you need to apply Oracle patch 19030178
  • After applying the patch, use the relevant (patched) Oracle JAR file. 
    • For example, you cannot use ojdbc14.jar. Instead, use a compatible JAR file such as a patched version of OJDBC6.jar.

  

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 Oracle database" (link at the end of this Technote).

    
PART FIVE - Configure Controller 'Database Conversion' to use encryption

On your Controller application server:

1. Convert the Oracle wallet to java keystores by launching a command prompt, and running a command similar to:

orapki wallet pkcs12_to_jks -wallet {walletLocation} -pwd {walletPassword} -jksKeyStoreLoc {DestinationJKSLocation} -jksKeyStorepwd {DestinationJKSPassword}

2. Convert the UDL to use encryption by first browsing to your UDL files

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

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

5. Add the following text to the end of the final line:

  • ccrSSLClientTruststoredb={path_to_jks_file}
  • ccrSSLClientTrustStoreType=JKS
  • ccrSSLClientTruststorePassword={jks_password}
  • ccrSSLClientKeystoredb={path_to_jks_file}
  • ccrSSLClientKeystoreType=JKS
  • ccrSSLClientKeystorePassword={jks_password}

  

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

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

[oledb]
; Everything after this line is an OLE DB initstring
Provider=OraOLEDB.Oracle.1;Password=THISISMYPASSWORD;Persist Security Info=True;User ID=MYUSERNAME;Data Source=OR12R102Sec.FRANGONET.COM;ccrSSLClientTruststoredb=C:\\ora_wallet\\mywallet2\\keys.jks;ccrSSLClientTrustStoreType=JKS;ccrSSLClientTruststorePassword=manager1;ccrSSLClientKeystoredb=C:\\ora_wallet\\mywallet2\\keys.jks;ccrSSLClientKeystoreType=JKS;ccrSSLClientKeystorePassword=manager1

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

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'.

   

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:

ORACLETHIN.name        = Oracle thin
ORACLETHIN.driver        = oracle.jdbc.driver.OracleDriver
ORACLETHIN.url        = jdbc:oracle:thin:@%s%s:%
  

2. Assuming using OJDBC6.JAR (or later) then locate the file:  ccr-system-properties.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 line at the end:

oracle.jdbc.autoCommitSpecCompliant=false

3. Locate the file:  CCRProxy.options

  • By default this is located here: C:\Program Files\ibm\cognos\ccr_64\server\)
  • As a precaution, create a backup of the file
  • Use NOTEPAD to edit the file, and add the following lines (including the hyphen - )
-Djavax.net.ssl.keyStore={JKSLocation}
-Djavax.net.ssl.keyStoreType=JKS
-Djavax.net.ssl.keyStorePassword={JKSPassword}
-Djavax.net.ssl.trustStore=C:\\ora_wallet\\mywallet2\\keys.jks
-Djavax.net.ssl.trustStoreType=JKS
-Djavax.net.ssl.trustStorePassword=manager1
  
 NOTE: Naturally:
  • Replace {JKSLocation} with the location of the JKS keystore
    • NOTE: This must be double escaped, for example:         C:\\javaKeystore.jks
  • Replace {JKSPassword} with the password of the keystore

  

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

    

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Optional - PART SEVEN - 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

ibm16323569

Page Feedback