Connecting to MSSQL Server using the latest drivers

dev support
1

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: 5.3.10.120

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.

Hope you can help,

Martin

2

Use Java 11 not 16…
Make sure have enabled Network Services and opened up the Firewall as well as the ports to Allow communication for ipv4 and ipv6 port 1433 under sql server manager (network ports)

3

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.

this.datasources["test"] = {
	  class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
	, bundleName: 'org.lucee.mssql'
	, bundleVersion: '12.2.0.jre11'
	, connectionString: 'jdbc:sqlserver://localhost:1433;DATABASENAME=mydatabasename;SelectMethod=direct;trustServerCertificate=true'
	, username: 'myusername'
	, password: "mypassword"
	
	// optional settings
	, clob:true // default: false
	, connectionLimit:-1 // default:-1
	, liveTimeout:15 // default: -1; unit: minutes
	, validate:false // default: false
};

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?

4

Just curious if a solution was found. I ended up rolling back the diver to an older version to get the connections to work, but SSL connections would obviously be preferred.

5

I am afraid not. I also ended up using the older v9 driver to connect.

I could not find out how/where to edit the extension either.

I believe that Adobe CF has a field that lets you specify connection string parameters manually. This would probably be useful in the Lucee extension.

There must be a way to use the more recent drivers - I just can’t figure it out at the moment.

6

Thanks @martin, it does seem like that would be the obvious solution.