Encrypting the connection to Yellowfin database used by Live Reporting

This topic describes steps for encrypting your connection to Yellowfin to secure information displayed by the Live Reporting dashboard. Depending on which database server Yellowfin runs on, perform either of the following procedures:

SQL Server database connection

Depending on your company policy, you can choose any third-party certificate authority (CA) to issue certificates for Server Authentication. Note that you cannot use self-signed certificates to encrypt a connection with an SQL Server database.

1. Ensure that you copy the third-party CA file  (typically the pubkey.cer) to any temporary location on the Yellowfin SQL database server.

2. Download the Microsoft jdbc driver from this Microsoft documentation site.
   
   

   Warning

   Important

   Ensure that you select the correct architecture for the jar files based on your Yellowfin SQL Server database architecture.

3. Unzip the file to a temporary location and copy the mssql-jdbc-9.4.0.jre16.jar file under the lib folders of the Yellowfin installation directory and the JRE instillation directory:

   	Path
   Yellowfin	<Yellowfin_Installation_Path>\appserver\lib
   JRE	C:\Program Files\BMC Software\Live_Reporting\jre\lib

   Warning

   Important

   Yellowfin should be configured to utilize the JRE installation bundled with the TrueSight Server Automation product, and it should not utilize the system's JRE installation.

4. To allow Windows to authenticate to the SQL Server database, copy the sqljdbc_auth.dll file under the bin folders of the Yellowfin installation directory and JRE installation directory.

   	Path
   Yellowfin	<Yellowfin_Installation_Path>\appserver\bin
   JRE	C:\Program Files\BMC Software\Live_Reporting\jre\bin

   Warning

   Yellowfin should be configured to utilize the JRE installation bundled with the TrueSight Server Automation product, and it should not utilize the system's JRE installation.

5. Copy the C:\Users\<userName>\Downloads\sqljdbc_9.4.0.0_enu\sqljdbc_9.4\enu\auth\x64\sqljdbc_auth.dll to C:\Program Files\BMC Software\Live_Reporting\Yellowfin\appserver\bin and C:\Program Files\BMC Software\Live_Reporting\jre\bin.
6. Import the 3rd party CA certificate (issued to DB Server) into yellowfin
   

   1. Go to the C:\Program Files\BMC Software\Live_Reporting\jre\bin to use the keytool command and import the 3rd party certificate into cacerts, using the following sample command:

      keytool -import -v -trustcacerts -alias <alias used to create certificate for DB Server> -file "C:\Users\Administrator\Documents\Third-Party.pubkey.cer" -keystore "C:\Program Files\BMC Software\Live_Reporting\jre\lib\security\cacerts"
   2. When prompted for password, provide 'changeit' <it's default java password to import any certificate to it's cacerts>
   3. Trust this certificate? [no]:  yes
   4. Restart the YellowFin Tomcat service
7. Log on to the Live Reporting dashboard with your REPORT_ADMIN credentials.
8. Go to Administration - Admin Console - Data Sources - <your data source>
9. In the connection section select 'Generic JDBC Data Source' for 'Database' field.
10. Change the JDBC driver to 'com.microsoft.sqlserver.jdbc.SQLServerDriver'.

11. Change the Connection String as follows:

    jdbc:sqlserver://<BSA_DB_SERVER_NAME>:<Port>;databaseName=<BSA_DB_NAME>;encrypt=true;trustServerCertificate=true
12. Test the connection by clicking on the link 'Click here to test the connection.'
13. The connection should be successful and should return the BSA_DB tables.

Enabling or disabling encryption in an Oracle database connection

You do not need to perform any configuration on the TrueSight Server Automation application server for enabling or disabling encryption in your Oracle database connection using Oracle Advanced Security. For detailed steps on enabling encryption using Oracle Advanced Security refer to the Oracle documentation.

See the following example for encrypting the connection to your Oracle 11g R2 database using the AES256 encryption algorithm. 

Oracle

1. For Oracle there is no specific change required at the yellowfin server.
2. Once the DB Server has been encrypted by following the steps mentioned here <link to enable Oracle encryption>, yellowfin needs to be restarted. 

Steps to Encrypt DB Server <The earlier link to BSA DB Server encryption>

Note: If DB Server has been modified to support encryption, yellowfin’s tomcat needs to be restarted for establishing fresh secure connection.