SSL on MySQL datasource

I’ve looked everywhere but can’t find any mention of how to do this. I can find it in ColdFusion but this (AFAIK) is not mirrored in Lucee…

Any help welcomed!

TIA

Tony,

Can you describe what you are trying to do?

I assume you want to use a SSL connection to the MySQL DB. First you have to setup MySQL server for SSL. There are articles out there for that. For Lucee to use it, there are some options to add to the connection string (useSSL etc). I think you have to add that to your Application CFC. I don’t think you can do it in Lucee Admin. Google “MySQL ssl jdbc”

Morning Guys

I need to connect from GCP to another public cloud. Obviously I need this secure. I’ve pondered building a tunnel with SSH and going over that, but a nice SSL connection would make more sense.

This is the ColdFusion one - MySQL SSL.

My knowledge of Java is not good but I’m happy to learn the necessary. It would be useful to bring the answers together for others to use.

I’ve got everything else working. Lucee is great on GCP.

Thanks

Google cloud has an article on setting up ssl with MySQL. Do that first and verify with the MySQL client. Lucee doesn’t have the Advanced option. Easiest is to create the datasource in Lucee Admin. When you edit the datasource, at the bottom you can copy to your Application.cfc. You then need to add “useSSL=true&requireSSL=true&verifyServerCertificate=false” to the connectionString: the part I am not sure on is adding the certificate. With verifyServerCertificate false I don’t think you need it. If you change it to true you might have to add it to the java keystore.

Yes - I’m going to investigate -The SSL with MySQL was no problem on GCP. I have a replication server using SSL from GCP to the other cloud.

Its those extra lines that tell the system to use SSL and the certificate lines - This is the issue! - Also MySQL falls back to non-encrypted silently.

I found this - MySQL SSL Link with jdbc but I’m not sure where to use this code…

Yes. That goes in your datasource connection string. The Lucee Admin doesn’t let you edit the connectionString, so you have to create one in Application.cfc. Just edit your current datasouce and look at the bottom of the page for the code. Add the 3 SSL options to the connectionString part.

it also looks like you can manually edit the data-source in lucee-web.xml.cfm (web site settings) or lucee-server.xml (server settings) file. You just add the options to the data-source on the custom= line and it doesn’t seem to delete it if you make updates in Lucee Admin.

Thanks for the info - I got it to work and used a server certificate from mysql that I created from here.

So using Debian 9 (stretch) with Lucee 5.2.2.71 I finally got things working

I installed the ca.pem file into the cacerts file (/opt/lucee/tomcat/lucee-server/context/security/cacerts) using

sudo /opt/lucee/jdk/jre/bin/keytool -import -trustcacerts -file ca.pem -alias mySQL-server -keystore cacerts

Lucee uses its own keystore called cacerts. The only ssl file is the ca.pem file which must be installed into the cacerts file using the command above. Keystore password is ‘changeit’

Changing the lucee-web-xml.cfm to add the line you gave me but with verifyServerCertificate=true.

The MySQL server user was set to REQUIRE SSL and things worked - but things were not correct so here is the corrected version!

Setting up Java SSL……

These instructions assume you have created certificates for the MySQL server and have set the username and password to REQUIRE SSL. It helps to make sure that you can connect from MySQLWorkbench or similar to prove that SSL is working.

Definitions - truststore holds the ca.pem file, keystore holds the client-cert.pem and client-key.pem

Cacerts is the file containing truststore information and is supplied by lucee

/opt/lucee/tomcat/lucee-server/context/security/cacerts

This is the truststore that lucee looks in and needs the ca.pem file to be present from the host computer that you are connecting to.

Default password for truststore/keystore is ‘changeit’

Add ca.pem to this truststore -

cd /opt/lucee/tomcat/lucee-server/context/security/

sudo /opt/lucee/jdk/jre/bin/keytool -import -file /path to /ca.pem -alias mysqlServerCACert -keystore cacerts

Create keystore for client-cert.pem and client-key.pem
Convert to pkcs12

sudo openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -name my-key -out generated/client.p12

Import to keystore - this is just the name of the keystore to try to reduce confusion!

sudo /opt/lucee/jdk/jre/bin/keytool -importkeystore -srckeystore client.p12 -srcstoretype pkcs12 -destkeystore keystore

Change setenv.sh to
Tomcat memory settings
# -Xms set initial Java heap size
# -Xmx set maximum Java heap size

CATALINA_OPTS="-Xms256m -Xmx512m \
-Djavax.net.ssl.keyStore=/path to/keystore \
-Djavax.net.ssl.keyStorePassword=changeit";

#-Djavax.net.debug=all - if debugging is needed - stdout to catalina.out

# additional JVM arguments can be added to the above line as needed, such as
# custom Garbage Collection arguments.

export CATALINA_OPTS;

Next job is to force lucee to use SSL with the datasource. Its best to use the global datasource for this…

cd /opt/lucee/tomcat/lucee-server/context/lucee-server.xml

Look for data-sources and find the one you want to use. Look for custom="useUnicode=true& and add

useSSL=true&requireSSL=true

Restart lucee…… and off you go!

1 Like