SQL variable, connection, job return The driver could not establish a secure connection

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

The following error might occur when attempting to use a SQL or Database connection object in some way that connects to SQL Server:

• When clicking on the "Test" button in a connection object:
  
  
  U02012064 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 re..
  
  
• When attempting to preview a SQL variable using the connection object:
  
  Query could not be executed: '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 re..'
  
  
• When attempting to run a script that reads the variable, the details will show:
  
  U02012053 RunID: '1104099/1': Failed to create a connection to the database.
  
  The agent log will show something like this for each of these:
  20221115/163321.542 - U02012035 Start Agent query with RunID '1101034'.
  20221115/163321.542 - U02012041 Query a table without waiting for a database. RunID: '1101034'
  20221115/163321.542 - U02012045 The database returned an error for RunID '1101034/1':
  20221115/163321.542 -           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". ClientConnectionId:9b98d21c-e91e-4e74-aabb-b1dd91de1cc5
  20221115/163321.542 - U02012037 Query with RunID '1101034' ended abnormally.
  
  
• JOB trying to run against the database will end with ENDED_NOT_OK and the Details will show:
  
  Remote status - Waiting for Database
  
  Turning on "Agent Log" in the job's SQL tab -> Job Report section:
  
  will show the following in the Agent log (PLOG) report:
  
  2022-11-15 16:47:00 - U02012031 The database driver returned the following error message:
  2022-11-15 16:47:00 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". ClientConnectionId:0180bbf9-b757-430b-b23a-4b1a63f3a62a
  2022-11-15 16:47:00 - U02004025 Job-script execution was aborted.

Another variation of this error could be:

U02012064 '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.securi..

Resolution

SQL Server of a certain version requires TrustServerCertificate=true for the connection properties.  This can be set on the connection object by:

1. going to the Database tab and then the Advanced Settings sub-tab. 
2. Add the TrustServerCertificate Property and a value of true
   
3. Click the "Add" button and save the changes

Now the connection object will test successfully to the database

PLEASE NOTE

Using the TrustServerCertificate does require the use of a connection object.  If it's mandatory to NOT use connection objects on database jobs, it might be possible to use a very old sql server jdbc jar file (like jdbc 6.4) and bypass the need to have the TrustServerCertificate property

Additional Information

MS article on this: https://techcommunity.microsoft.com/t5/azure-database-support-blog/pkix-path-building-failed-unable-to-find-valid-certification/ba-p/2591304