I am trying to figure out how to connect to a local SQL Server Express 2019 database using the latest SQL Server extension 12.2.0jre11 and am hitting the following error:
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:658bcdb4-cee9-4a2f-a67a-bcd16b9a5de7
Google points towards adding certificates to the JVM keystore or setting TrustServerCertificate to true:
I have tried adding the certificate as outlined in the article above. Restarting Lucee gives me the same error. I presume because I cannot see a way to add the trustStore location to the connection. I would set the TrustServerCertificate value, but I cannot see a way to do that in Lucee admin - the option is not presented.
I presume that I can add these parameters if I create the datasource via code, but I want to do this via the admin if possible.
Has anybody else managed to use the latest drivers to connect to MSSQL?
Don’t forget to tell us about your stack!
OS: Windows Server 2019 Java Version: 16.0.2 - is this supported?!?! Tomcat Version: 9.0.71 Lucee Version: 188.8.131.52
Just filling this in, it looks like I am running Java 16.0.2. I did not think that this was supported. It looks like the host (Viviotech) has swapped out the default JVM. I have just tried tried it with v11.0.18, but get the same error message.
Thanks for the reply @Terry_Whitney, but as I suspected, I can specify a DSN using code in the application.cfc and set the connection string to include either trustServerCertificate=true or encrypt=false and get the connection to work.
I tried to look at the extension in github, but I either could not find the right version, or simply do not understand how these are built. The one in the master branch is for version 8.4.1.jre8. I wanted to see if I could edit the admin interface to include missing options in the connection string for the more recent drivers.
I presume that this affects all the versions 10+ of the driver as that is when encrypt=true was set as the default:
How are others using the more recent MSSQL JDBC drivers and how can I try to update the extension to include additional attributes?