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. In the SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
  2. Select the Certificate tab, click View..., then Details, and Copy to File.
  3. Click Next in the window that appears.
  4. Choose No, do not export the private key and click Next.
  5. Choose Base-64 encoded X.509 (.CER) format and click Next.
  6. Select the path name and file name to save the certificate.
  7. Review specified settings and click on Finish.
  8. 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. If this is not the initial configuration, but the BigFix Inventory server is already running, stop the BigFix Inventory server, back up the database.yml and server.xml files, and remove the original database.yml. Then start the BigFix Inventory server and enter the connection details with the encryption information.

Troubleshooting

About this task

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.

Procedure

  1. Make sure that the certificate extracted from MSSQL and certificate imported to key_bfi_db.p12 truststore are the same. You can extract both certificates to compare them. Steps to extract the MSSQL certificate are described here. To extract certificate from key_bfi_db.p12 truststore you following command: 
    'keytool -exportcert -keystore "<BFI_INSTALL_DIR>\wlp\usr\servers\server1\resources\security\key_bfi_db.p12" -alias "bfi_db_cert" > exported.cer'
  2. Make sure that the certificate is valid—check 'valid_from' and 'valid_to' dates.
  3. Make sure that the certificate has a valid Subject Name and a valid Subject Alternative Name—they must include the same name that is used in the BigFix Inventory UI.