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
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 TLS certificate
- 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
- 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).
- Specifically, configure them to use the protocol TCPS ("TCP with SSL")
# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY =C:\ora_wallet\mywallet2)
)
)
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).
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:
-------------------------------------------------------
; 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'.
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.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
- 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'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12
Additional Information
Related Information
2004921 - ** How to ** Enable SSL / HTTPS with Cognos Controller
737041 - MS SQL - How to configure Controller to use TLS (formerly SSL) to conn…
Third Party (Oracle) - 'SSL With Oracle JDBC Thin Driver' white paper
Cognos Analytics 11.1.x - Use secure sockets layer (SSL) protocol for database …
Cognos Analytics 11.1.x - Using SSL for database connections in IBM Cognos Conf…
Third Party (Oracle) - Oracle patch 19030178
549487 - What is the correct / best OJDBC JAR file to use with Cognos Controlle…
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
27 August 2020
UID
ibm16323569