EVENT.DB cannot use 'SQL query' option with MSSQL 2019 and superior - PKIX path building failed

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The Database Events with newer MS SQL JDBC drivers don't work when the database uses a self signed certificate or as there's no way to use a Connection Object where the extended properties such as trustServerCertificate=true are set, only the hostname:port and database name can be introduced.
Whenever we use the option 'SQL query' for the parameter 'Source' this will always end up  in an error:

U02012033 Query could not be executed: 'com.microsoft.sqlserver.jdbc.SQLServerException:"encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

Environment

Agent SQL all versions

 

Resolution

Workaround:

Use an older MSSQL JDBC  jar file (9.4.1 or inferior).

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Agent SQL
Will be fixed in Automation.Engine 21.0.11 - Planned release end July 2024
Will be fixed in Automation.Engine 24.2.0 - Planned release end September 2024

Additional Information

Defect ID: 

Public Description: A behavior has been improved where it was not possible to use Database Events with newer MS SQL JDBC drivers when the database used a self signed certificate. The EVENT.DB object did not provide an option to specify the trustServerCertificate parameter.
With this fix, a new parameter called "trustServerCertificate=0/1" has been added to the Agent INI file.