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.
Note: Before you "Enforce Encryption" on MS SQL configure BigFix Inventory first to verify that connection works.

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:

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

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

Procedure

  1. Copy the MS SQL server certificate to BigFix Inventory machine.
  2. Open command line and use the command, cd <BFI_INSTALL_DIR>\jre\jre\bin.
  3. Create a <keystore_extension> type TrustStore and import the public certificate into it using the command, keytool -importcert -file <MS SQL Server.cer file> -keystore <BFI_INSTALL_DIR>\wlp\usr\servers\server1\resources\security\key_bfi_db.p12 -alias "bfi_db_cert" -storetype PKCS12. Provide a TrustStore password of your choice for this TrustStore and enter Yes to trust the certificate.
  4. Encrypt the password used in step 3 in ‘aes’ encoding. Run the following command and enter the password you used when creating key_bfi_db.p12 truststore: <BFI_INSTALL_DIR>/wlp/bin/securityUtility encode --encoding=aes.As a result you will get TrustStore password encrypted in 'aes' encoding. Save it to use in the next steps.
  5. 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".
  6. 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.p12
  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.
  7. 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.p12" 
      trustStorePassword="<your TrustStore password encrypted in 'aes' encoding>"
      user="<your BFI database user>"
      password="<your encrypted BFI database password>" />
</dataSource>
    Note: If you use Windows authentication for BigFix Inventory database, instead of user and password parameters, it will be integratedSecurity="true".
  8. Start BigFix Inventory service.
  9. Confirm that BigFix Inventory is operational by logging into application.
  10. Check the next Data Import against any issues with connectivity.

Results

Once BigFix Inventory application is accesible and Data Import successful you can harden your enviroment and enforce connection encryption on MS SQL database side.
Troubleshooting:
Start from reviewing the tema.log from the top. In case of configuration issues, WebSphere might create 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
Temporary you can switch the flag trustServerCertificate to true to allow BigFix Inventory to accept any certificate.
You might need to review MS SQL Server Logs as well as not all details might be reported back to BigFix Inventory. Especially when using Windows Integrated security check those logs and confirm that used account to run BigFix Inventory Service is trusted on the MS SQL Server side. Some issues might be visible once the encryption is in use allowing normal operation without it.