Enabling database connection encryption for DB2 for BigFix Inventory

Configure the DB2 database to apply SSL based encryption.

Before you begin

DB2 database is configured to enable the connection encryption. For information on IBM TLS configuration process for DB2, refer to https://www.ibm.com/docs/en/db2/11.5?topic=transit-tls-configuration-db2.
The extracted public trust certificate should be available in 64-bit encoded .CER format. It can be done by using the command, gsk8capicmd_64 -cert -extract -db "<Server Keystore .kdb file>" -pw "<Server Keystore password>" -label "<label name>" -target "<.cer file name>" -format ascii.

About this task

Once you export the certificate, enable the connection encryption.

Perform the below steps to enable the encryption for DB2:

Procedure

Create <keystore_extension> type TrustStore and import the public certificate into it:
- Go to BigFix Inventory java directory, BFI_INSTALL_DIR>/jre/jre/bin
- Import the certificate into the TrustStore using the command, ./keytool -importcert -file <.cer file location> -keystore <BFI_INSTALL_DIR>/wlp/usr/servers/server1/resources/security/key_bfi_db.<keystore_extension> -alias "bfi_db_cert"
- Provide a password for this TrustStore.
- Enter Yes to trust the certificate.
Stop BigFix Inventory service.
Update the existing attributes and add missing attributes in the server.xml file.

Important: For the database, use the same server name as provided in the certificate. Update the value in the serverName attribute.
```
<dataSource id='DatabaseConnection' jndiName='jdbc/ilmtDatabaseConnection'> 
<jdbcDriver libraryRef='DatabaseLib'/> 
<properties.db2.jcc databaseName='temadb' driverType='4' 
enableExtendedIndicators='2' password='<password encrypted in in ‘aes’ encoding>' 
portNumber='<TLS port number>' serverName='<host name - the same that certificate is issued to>'
user='db2inst1' sslConnection='true' 
sslTrustStoreLocation='<BFI_INSTALL_DIR>/wlp/usr/servers/server1/resources/security/key_bfi_db.<keystore_extension>' 
sslTrustStorePassword='<password encrypted in in ‘aes’ encoding>'/> 
</dataSource>
```
Note: Encrypting the password in ‘aes’ encoding. Refer to Updating the database password for more information. If you choose to encrypt the database connection while upgrading the BigFix Inventory server, you must define the password encryption attribute in the data source.

Update the existing attributes and add missing attributes in the database.yml file.

Tip: Follow the instruction mentioned here while editing the database.yml file.

Important: For the database, use the same hostname as provided in the certificate. Update the value in the host name attribute.

host: <host name - the same that certificate is issued to>  
database: temadb 
username: db2inst1 
database_type: db2 
port: ‘<TLS port number>' 
encrypted_password: "<password encrypted in in ‘aes’ encoding>" 
sslConnection: true 
sslTrustStoreLocation: '<<BFI_INSTALL_DIR>/wlp/usr/servers/server1/resources/security/key_bfi_db.<keystore_extension>>’ 
sslTrustStorePassword: "<password encrypted in in ‘aes’ encoding>"

Start BigFix Inventory service.

Monitor tema.log to make sure that the connection is established.

If the application does not start, review the tema.log from the top. In case of configuration issues, the log shows error messages that helps in troubleshooting. For example:

[3/11/21 14:43:42:658 UTC] 0000002a SystemOut                                                    
O 2021-03-11 14:43:42 ERROR: Cannot connect to the database: Java::ComIbmDb2JccAm::DisconnectNonTransientConnectionException: [jcc]
t4][2043][11550][4.28.11] Exception java.io.FileNotFoundException: Error opening socket to server ***/*** on port 51,001 with message: ***<keystore_extension> (No such file or directory). 
ERRORCODE=-4499, SQLSTATE=08001. 
The next try for 60 seconds. 

[3/11/21 17:02:45:608 UTC] 0000002b SystemOut                                                    
O 2021-03-11 17:02:45 ERROR: Cannot connect to the database: Java::ComIbmDb2JccAm::DisconnectNonTransientConnectionException: [jcc]
[t4][2043][11550][4.28.11] Exception java.io.IOException: Error opening socket to server ***/*** on port 51,001 with message: 
Keystore was tampered with, or password was incorrect. ERRORCODE=-4499, SQLSTATE=08001. 
The next try for 60 seconds.

Note: The <type> and <keystore_extension> parameters that apply to the above commands specifies the following:

<type>: Starting from version 10.0.8.0, the type of the certificate is PKCS12. For earlier versions, it is JCEKS.

<keystore_extension>: Starting from version 10.0.8.0, the name of the keystore file is .p12. For earlier versions, it is .jceks.