IBM Support

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

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.

TIP: For the steps required for other database platforms see separate IBM Technotes (links below):
  • 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 TLSimage-20191120122732-1 certificate

TIP: For more information on this subject, see third-party (Microsoft) official documentation such as the link below

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:

image-20181113121124-1
6. On the Certificate tab, select the desired certificate from the Certificate drop-down menu, and then click OK:

image-20191120133422-1
7. On the Flags tab, select Yes in the ForceEncryption box:

image-20181113132542-1

  • 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:

cd  C:\Program Files\ibm\cognos\ccr_64\bin\jre\8.0\bin
keytool -import -file C:\UTILS\self_signed_cowhand1.castle.fyre.ibm.com_2027.cer -keystore C:\UTILS\javaKeystore.jks
You need to set a password for the keystore, for example:   changeit
image-20191120194834-1
Then press “y” to trust the server certificate:
image-20191120194951-2

  

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:

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

[oledb]
; 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.name        = MS SQLServer
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
  
IMPORTANT:
  • 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:

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 to the properties tag of each datasource:
  • encrypt="true"
  • trustStore="{location of trustore jks file}"
  • trustStorePassword="{trustStore password}"
For example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<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"/>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
NOTE: If using modern versions of Controller (10.3.1 IF8 or later 10.3.x, or 10.4.0 IF1 and above) then you must use: sendStringParametersAsUnicode = false
  • For older versions of Controller, this should be set to true

PART SEVEN - 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
6. 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 having the name DSN{UDLName}
  • Create one for each and every database connection, for example:
image-20191121093140-2
TIP: During the creation:
  • 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:
image-20191121105407-1
   

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

2. Launch the CA website, locate the relevant data source
  • Edit it, and append the following Optional Connection Parameters: Use Encryption for Data = true
image-20191121111710-2
 
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.Encrypt=true
source. {SourceName}.connection.property.ccrSSLClientKeystoredb={LocationToJKSFile}
source. {SourceName}.connection.property.ccrSSLClientKeystorePassword={JSKPassword}

For example:

image-20191121112821-1

  

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)
3. Click on the tab 'Sources'
  • Highlight the relevant source
  • Click 'Edit'
  • Modify the value for 'port' and append the following:                    ;Use Encryption for Data=true
image-20191121113848-1
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 ELEVEN - 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 TWELVE - 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
=================================
Appendix - How to check if the connections to Microsoft SQL are encrypted
There are two simple methods:
(A) Run this SQL script:
SELECT  session_id,encrypt_option
FROM    sys.dm_exec_connections
You should get a result similar to:
image-20200320135201-1
(B) Run this SQL script:
SELECT
  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
You will get a result similar to:
image-20200320135353-2
=================================

Additional Information

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

[{"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":"PF033","label":"Windows"}],"Version":"All Version(s)"}]

Document Information

Modified date:
27 August 2020

UID

ibm10737041

Page Feedback