Enabling database connection encryption for MS SQL for BigFix Inventory

Configure the MS SQL Server with BigFix Inventory database to apply SSL-based encryption.

Before you begin

MS SQL database must be configured to enable the connection encryption. For more information on configuring SQL Server Database engine for encrypting connections, refer to https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16.

About this task

Export the public certificate in the

.CER
                Base-64

encoded X.509 format using Certmgr (Manage Computer Certificates).

Perform the below steps to enable the encryption for MS SQL database:

Run the Certmgr tool on Windows system hosting MS SQL database.
In the left panel of the program, go to Personal > Certificates.
In the right panel, right click on the database server certificate and go to All Tasks > Export.
Click Next in the window that appears.
Choose No, do not export the private key and click Next.
Choose Base-64 encoded X.509 (.CER) format and click Next.
Select the path name and file name to save the certificate.
Review specified settings and click on Finish.
Certificate is exported to the specified location.

Once you export the certificate, enable the connection encryption by performing below steps:

Procedure

Copy the MS SQL server certificate to BigFix Inventory machine.
Open command line and use the command, cd <BFI_INSTALL_DIR>\jre\jre\bin.
Create a <keystore_extension> type TrustStore and import the public certificate into it using the command, keytool -importcert -file <.cer file> -keystore <BFI_INSTALL_DIR>\wlp\usr\servers\server1\resources\security\key_bfi_db.<keystore_extension> -alias "bfi_db_cert" -storetype <type>. Provide a TrustStore password of your choice for this TrustStore and enter Yes to trust the certificate.

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.
Stop BigFix Inventory service.
Update the existing attributes and add missing attributes in the server.xml file.
Important: Create a backup of server.xml and database.yml files before you make any change.

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

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.microsoft.sqlserver 
      databaseName='<your BFI database name>' 
      lockTimeout='180000' 
      encrypt='true' 
      serverName='<host name - the same that certificate is issued to>' 
      trustServerCertificate='false' 
      trustStore='<BFI_INSTALL_DIR>/wlp/usr/servers/server1/resources/security/key_bfi_db.<keystore_extension>' 
      trustStorePassword='<your TrustStore password encrypted in in 'aes' encoding>' 
      user='<your BFI database user>'
      password='<your encrypted BFI database password>' /> 
</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. If you use Windows authentication for BigFix Inventory database, instead of user and password parameters, it will be integratedSecurity="true".

Update the existing attributes and add missing attributes in 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.

---
production:
  host: <host name - the same that certificate is issued to> 
  database: <your BFI database name>
  username: <your BFI database user>
  database_type: mssql
  windows_authenticated: false 
  port: '50000'
  encrypted_password:host: "<your encrypted BFI database password>" 
  encrypt: true 
  trustServerCertificate: false 
  trustStore: <<BFI_INSTALL_DIR>/wlp/usr/servers/server1/resources/security/key_bfi_db.<keystore_extension>> 
  trustStorePassword: "<your Truststore password encrypted in in 'aes' encoding>"

Note: If you use Windows authentication for BigFix Inventory database, username and encrypted_password parameters will be missing and windows_authenticated will be set to true.

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, WebSphere creates a FFDC log.

[3/10/21 11:28:03:118 UTC] 00000026 com.ibm.ws.recoverylog.spi.RecoveryDirectorImpl I CWRLS0010I: Performing recovery processing for local WebSphere server (server1). 
[3/10/21 11:28:03:180 UTC] 00000026 com.ibm.ws.recoverylog.spi.RecoveryDirectorImpl I CWRLS0012I: All persistent services have been directed to perform recovery processing for this WebSphere server (server1). 
[3/10/21 11:28:03:180 UTC] 00000041 com.ibm.tx.jta.impl.RecoveryManager I WTRN0135I: Transaction service recovering no transactions. 
[3/10/21 11:28:03:352 UTC] 00000026 com.ibm.ws.logging.internal.impl.IncidentImpl I FFDC1015I: An FFDC Incident has been created: "com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path validation failed: java.security.cert.CertPathValidatorException: signature check failed". ClientConnectionId:a89039f3-b6ce-4de6-8d10-8c205c44243e com.ibm.ws.rsadapter.impl.MicrosoftSQLServerHelper 1298" at ffdc_21.03.10_11.28.03.0.log 
[3/10/21 11:28:03:477 UTC] 00000026 com.ibm.ws.logging.internal.impl.IncidentImpl I FFDC1015I: An FFDC Incident has been created: "com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException: DSRA8100E: Unable to get a PooledConnection from the DataSource. with SQL State : 08S01 SQL Code : 0 com.ibm.ejs.j2c.poolmanager.FreePool.createManagedConnectionWithMCWrapper 199" at ffdc_21.03.10_11.28.03.1.log 
[3/10/21 11:28:03:524 UTC] 00000026 com.ibm.ws.logging.internal.impl.IncidentImpl I FFDC1015I: An FFDC Incident has been created: "javax.resource.spi.ResourceAllocationException: DSRA8100E: Unable to get a PooledConnection from the DataSource. with SQL State : 08S01 SQL Code : 0 com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection 299" at ffdc_21.03.10_11.28.03.2.log 
[3/10/21 11:28:03:524 UTC] 00000026 SystemOut O [JAVA] [ERROR] [ILMTCore] (Default Executor-thread-3) com.ibm.ilmt.common.dao.util.DBDefaultPoolerManager::init:Unable to initialize datasource jdbc/ilmtDatabaseConnection