Encrypting your database connection

This topic describes the steps for encrypting your database connection to TrueSight Smart Reporting:

• Enabling encryption in SQL Server database connection
• Enabling encryption in Oracle database connection

TrueSight Smart Reporting does not support using an encrypted Microsoft SQL Server database or Oracle database connection at the time of installation or upgrade. However, after the product is installed or upgraded successfully, TrueSight Smart Reporting can use an encrypted connection to communicate with the Microsoft SQL Server database or Oracle database.

Enabling encryption in SQL Server database connection

Enabling encryption in a SQL Server database connection using third-party CA certificates

Depending on your company policy, you can choose any third-party certificate authority (CA) to issue certificates for Server Authentication.

Step 1: Install certificate and configure the SQL Server to accept encrypted connectionsRefer to Microsoft documentation for detail steps on enabling encrypted connections to the Microsoft SQL Server Database Engine, click here.

Step 2: Configure the TrueSight Smart Reporting server

Perform the following steps on all your TrueSight Smart Reporting servers to communicate with the encrypted database:

1. Obtain the third-party CA certificate of Database Server and copy to the TrueSight Smart Reporting server in a temporary location.

2. Run the following command to import the public key certificate file into the TrueSight smart reporting keystore:
   keytool -import -v -trustcacerts -alias <alias_name> -file <path_where_3rd_party_cert_is_copied> -keystore <tssr_jre_home> \lib\security\cacerts

   Example

   keytool -import -v -trustcacerts -alias tcpstsr -file "C:\temp\clm-pun-t0v35f.bmc.com.cer" -keystore "C:\Program Files\Java\jre1.8.0_201\lib\security\cacerts"

1. When prompted for a password, enter the password that is set for JRE cacerts keystore in TrueSight Smart Reporting. The default password set for cacerts is typically changeit.
2. Do the following to establish the encrypted connection to TSSA-DW data source.
   1. Navigate to <tssadw_install_dir>\bsa\bi\model and open datasource_sqlserver.xml file.
   2. Search for SSLOPTION parameter in datasource_sqlserver.xml file.
   3. Change the parameterValue to ENABLED.
   4. Save the changes.
3. Restart the TrueSight Smart Reporting - Platform service.

Enabling encryption in Oracle database connection

Perform the following steps for encrypting the connection to your Oracle database.

Step 1: Configure Oracle database to accept encrypted connections

For detailed steps on enabling encryption on your Oracle database server, using Oracle Advanced Security, refer to the Oracle Database documentation.

Step 2: Configure TrueSight Smart Reporting – Platform

Perform the following steps on TrueSight Smart Reporting – Platform to communicate with TCPS encrypted database.

1. Navigate to the <install_dir>\appserver\bin and run one of the following commands to stop the TrueSight Smart Reporting - Platform service:
   • (Windows) shutdown.bat
   • (Linux) ./shutdown.sh
2. Create a keystore certificate for the TLS-enabled database host, and do the following:
3. 
   
   1. Obtain the keystore certificate of the TLS-enabled database host.
   2. Save the certificate in the \tmp directory.
   3. Open the command prompt, and navigate to the <install_dir>\appserver\conf directory.

   4. Run the following command:
      keytool -importcert -file <certificate file name> -keystore tsr.keystore -alias "newtsrkey"

      Example

      keytool -importcert -file "/tmp/ExportedCertificateFromServer.txt" -keystore "/data1/bmc/TrueSightSmartReporting/appserver/conf/tsr.keystore" -alias "DBSERVCERT"
   5. Enter the default password when prompted, and enter y when asked Trust this certificate. The default password is changeit.
4. Open the <install_dir>\appserver\webapps\ROOT\WEB-INF\web.xml file.

5. Change the value of the JDBCUrl parameter to the following:
   jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = <Fully Qualified Domain name of the TLS Enabled Database Host>)(PORT = <Port Number of the TLS Enabled Database(Default:2484)>)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <Service Name of the TLS Enabled Database on which user has been created>)))

   Example

   jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = P0APPSERVER3)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19C.bmc.com)))

6. Run the following command to import the public key certificate file into the TrueSight smart reporting jre cacerts.
   keytool -import -v -trustcacerts -alias <alias_name> -file <certificate of the TLS-enabled database host> -keystore <tssr_jre_home> \lib\security\cacerts

   Example

   keytool -import -v -trustcacerts -alias tcpstsr -file "/tmp/ ExportedCertificateFromServer.txt" -keystore " /usr/java/jdk1.8.0_201-amd64/jre/lib/security/cacerts"

   In upgrade scenarios, you might not have to import the certificate, instead you must use the name as specified as the alias for importing the certificates in cacerts file.

7. Navigate to <tssadw_install_dir>/bsa/bi/model and backup the datasource_oracle.xml.
8. Replace the existing datasource_oracle.xml file with the attached datasource_oracle.xml file.
9. Do the following to update the datasource_oracle.xml details with actual database server details.
   1. Open the datasource_oracle.xml file.
   2. Search for jdbc:oracle:thin
   3. Change the values for HOST, PORT and SERVICE_NAME in the JDBC connection URL to the following:
      1. HOST = <TCPS_ENBALED_DB_SERVER_HOST>
      2. PORT = <TCPS_ENABLED_PORT>
      3. SERVICE_NAME = <SERVICE_NAME>
   4. Save the datasource_oracle.xml file.
10. Navigate to the <install_dir>\appserver\bin and run one of the following commands to start the TrueSight Smart Reporting - Platform service:
    • (Windows) startup.bat
    • (Linux) ./startup.sh