How To
Summary
Customer would like to configure their Controller system so that all database network communication ("in transit") is encrypted (via TLS).
Objective
Configure all database traffic to be encrypted by TLS.
Environment
Controller databases hosted on Microsoft SQL database server.
- Oracle = 6323569
- DB2 =
Steps
NOTES:
- The following steps are based on Controller 10.3.1 (installed in the default location) and Microsoft SQL 2016.
- 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 SQL 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 MS SQL server to use a TLS certificate
1. Create/obtain a TLS certificate
- TIP: One method is to create a self-signed certificate. See Appendix #1 inside separate IBM Technote #563063 for more details
- However, it is likely that your I.T. department will use a different method
2. Install ('provision') that TLS certificate onto the SQL server
3. From the start menu, launch: SQL Server Configuration Manager
4. Expand SQL Server Network Configuration
5. Right-click Protocols for <server instance>, and then select Properties:
6. On the Certificate tab, select the desired certificate from the Certificate drop-down menu, and then click OK:
7. On the Flags tab, select Yes in the ForceEncryption box:
- Then click OK (to close the dialog box).
8. Restart the SQL Server service.
PART THREE - Configure Cognos Analytics (Content Manager) to use encryption
TIP: For more information on this subject, see documentation such as ''Using SSL for database connections in IBM Cognos Configuration for Microsoft SQL Server.
PART FOUR - Configure main portion of Controller (.NET and database conversion) to use encryption
1. On your Controller application server, add the TLS (SSL) certificate (which is being used by SQL) to the Windows certificate trust store
2. Create a JAVA keystore file (for example in a folder C:\UTILS) by launching a command prompt, and running commands similar to:
keytool -import -file C:\UTILS\self_signed_cowhand1.castle.fyre.ibm.com_2027.cer -keystore C:\UTILS\javaKeystore.jks
3. Browse to your UDL files
- TIP: By default, these are located here: C:\Program Files\ibm\cognos\ccr_64\data
4. Open the relevant UDL file (for example 'test.udl') in NOTEPAD.EXE
5. Add the following text to the end of the final line: ;Encrypt=true;Use Encryption for Data=true;ccrSSLClientKeystoredb=C:\UTILS\javaKeystore.jks;ccrSSLClientKeystorePassword=changeit
- TIP: The first two entries allow the main (.NET) Controller system to connect. The next two entries allow the 'Database conversion' utility to work OK.
For example, the finished UDL file might look similar to:
-------------------------------------------------------
; Everything after this line is an OLE DB initstring
Persist Security Info=True;Data Source=cowhand1.castle.fyre.ibm.com;Initial Catalog=MyDB;Password=fastnet;User ID=fastnet;Provider=SQLNCLI11.1;Encrypt=true;Use Encryption for Data=true;ccrSSLClientKeystoredb=C:\javaKeystore.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'.
PART FIVE - JAVA proxy functionality
1. On your Controller application server, browse to here: C:\Program Files\ibm\cognos\ccr_64\server\integration
2. As a precaution, create a backup copy of: ccr-dbTypes.properties
3. Open that file using NOTEPAD, and modify the relevant lines (remembering to remove the preceding #) so that it looks similar to:
SQL_SERVER.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
SQL_SERVER.url = jdbc:sqlserver://%s%s;databaseName=%s;encrypt=true;trustStore=C:\\UTILS\\javaKeystore.jks;trustStorePassword=changeit
- Modify the 'trustStore' value with the location of the Java keystore containing the SQL server certificate
- NOTE: This must be double escaped, for example: C:\\javaKeystore.jks
- Modify the 'trustStorePassword' value with the password of the respective keystore.
4. Restart the Windows service 'IBM Cognos Controller Java Proxy'
PART SIX - 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, by performing the following:
- encrypt="true"
- trustStore="{location of trustore jks file}"
- trustStorePassword="{trustStore password}"
<dataSource id="SQLEncrypt" jdbcDriverRef="mssql" jndiName="jdbc/SQLEncrypt" type="javax.sql.DataSource">
<properties.microsoft.sqlserver databaseName="MyDB" instanceName="k16_cp1_ci_as" password="fastnet" portNumber="0" sendStringParametersAsUnicode="false" serverName="cowhand1.castle.fyre.ibm.com" user="fastnet" encrypt="true" trustStore="C:\UTILS\javaKeystore.jks" trustStorePassword="changeit"/>
- For older versions of Controller, this should be set to true
PART SEVEN - ODBC (Consolidations, optimisations)
- Create one for each and every database connection, for example:
- Use the SQL Native client 11.0 provider
- Choose SQL Server authentication
- Select the default database to be the relevant Controller database
- IMPORTANT: Tick the ‘Use Strong encryption for data’ checkbox:
PART EIGHT - Standard Reports
Before continuing, you need to know if your Cognos Analytics FM package has been configured to use CQE or DQM.
- CQE = Cognos Query Engine
- older engine
- Sometimes known as "Compatible Query Mode"
- DQM = Dynamic Query Mode
- newer engine
Inside your Cognos Analytics (CA) website, you can open 'Team content', then right-click on 'Controller' and choose 'properties'. Inside 'General - Advanced' you can find the 'Query mode'.
- For more details, see separate IBM Technote #742743.
The following steps assume that you are using the default settings (CQE):
1. On the CA report server, import the SQL Server certificate into the Trusted Root Certification authorities
- Edit it, and append the following Optional Connection Parameters: Use Encryption for Data = true
3. Optionally, if you also want to force use of protocols above TLS 1.0, then follow the steps inside separate IBM Technote #728129.
PART NINE - 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. Add a line similar to:
db.extended.properties=Provider=SQLNCLI;Encrypt=true;ccrSSLClientKeystoredb={LocationToJKSFileContainingTheSQLServer’sCertificate};ccrSSLClientKeystorePassword={JSKPassword}
4. Also, add a line for each and every one of your FAP sources, similar to:
source. {SourceName}.connection.property.ccrSSLClientKeystoredb={LocationToJKSFile}
source. {SourceName}.connection.property.ccrSSLClientKeystorePassword={JSKPassword}
For example:
PART TEN - FAP Client
1. On every device that is using the FAP client, add the public TLS (SSL) certificate (which is being used by SQL) to the Trusted Root Certification authorities (in the Windows certificate trust store)
2. Launch the FAP client, and use the following in the logon screen: ;Use Encryption for Data=true
- If using Controller 10.3.1 then you add this after the database name
- 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)
- Highlight the relevant source
- Click 'Edit'
- Modify the value for 'port' and append the following: ;Use Encryption for Data=true
- 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 ELEVEN - Planning Analytics (TM1) server
1. On the PA server, edit the ODBC connection 'FAP'
2. Tick the option 'Use Strong Encryption for Data':
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12
FROM sys.dm_exec_connections
SUM(CASE WHEN encrypt_option='FALSE' THEN 1 ELSE 0 END) AS [Total number of unencrypted connections]
,SUM(CASE WHEN encrypt_option='TRUE' THEN 1 ELSE 0 END) AS [Total number of encrypted connections]
FROM sys.dm_exec_connections
Additional Information
For more information on how to secure other parts of the Controller architecture (using SSL) see separate IBM Technote #2004921.
Related Information
2004921 - ** How to ** Enable SSL / HTTPS with Cognos Controller
Third Party (Microsoft) - Enable Encrypted Connections to the Database Engine
563063 - How to configure main Controller client (CCR.EXE) to use HTTPS (SSL)
CA 11.1 - Using SSL for database connections in IBM Cognos Configuration for Mi…
289719 - Missing database connection choices when using Controller Web
742743 - How to Check if a Package is Dynamic Query Mode (DQM) or Compatibility…
728129 - "QE-DEF-0285 ... QE-DEF-0323 ... SSL Security error. (SQLSTATE=08001, …
883036 - How to configure (force) Controller to use TLS 1.2
6323569 - ORACLE - How to configure Controller to use TLS (formerly SSL) to con…
6323587 - DB2 - How to configure Controller to use TLS (formerly SSL) to connec…
Was this topic helpful?
Document Information
Modified date:
27 August 2020
UID
ibm10737041