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.

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

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