Database Connector | "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

SYMPTOM

The following error occurs when using Database Connector for Mule 4 to connect to Microsoft SQL Server:

java.sql.SQLException: Cannot get connection for URL jdbc:sqlserver://host:1433;databaseName=testDB: "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.

CAUSE

The SQL Server instance's TLS certificate is not trusted by JVM's default trust store or the trust store configured in the trustStore connection property.

SOLUTION

There are two possible solutions for this problem:

1. If the SQL Server instance has only a self-signed certificate, set the trustServerCertificate property to true so that the Microsoft JDBC Driver for SQL Server won't validate the SQL Server TLS certificate. This setting is common for allowing connections in test environments.
   
   
   
   
2. Change the trustServerCertificate property in the red rectangle highlighted in the above screenshot to false and also add the following connection properties. This setting is recommended for production environments.
   - trustStore: Specifies the path (including filename) to the certificate trustStore file, which contains the list of certificates that the client trusts.
   - trustStorePassword: Specifies the password of the trust store, which is used to check the integrity of the trustStore data.
   
   For more information, refer to the following link:
   https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16