search cancel

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

book

Article ID: 254389

calendar_today

Updated On:

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

Attachments