This article covers how to setup Oracle SQL Developer to use MySQL driver and connect via SSL/TLS to a MySQL server database with a PKCS12 certificate.

Motivation

I’ve been using Oracle SQL Developer on Mac OS for a couple of years and it is my preferred tool to connect to both Oracle and MySQL databases.
Recently I had to connect to a SSL/TLS enabled MySQL server and information on setting this up happened to be from very sparse to almost non-existent. I’ve spent a few hours searching and reading through official documentation, forums, comments etc without much success. Finally I managed to combine several sources and it worked out, so I decided to put all this info together and help other people looking for the same info.
Some people will jump here and say “Why don’t you use MySQL Workbench? It has SSL/TLS support out of the box.” and my answer will be – Yes you can, but you can be used to Oracle SQL Developer interface, reports, export, graphs etc and/or use Oracle Databases at the same time and then you don’t want to switch between 2 tools all the time.

Compatibility

The commands and screenshots shown here are from Mac OS X but they should work on any Linux distro and hopefully on Windows.
The proposed method was tested with Oracle SQL Developer 17.2 and Google Cloud MySQL 5 database.

Prerequisites

You have a MySQL server with enabled SSL/TLS and you have your key and certs available:

• client-key.pem – private key of the connecting client
• client-cert.pem – client certificate corresponding to the client private key and signed by the MySQL server certificate issuer.
• server-ca.pem – server certificate

You have Oracle SQL Developer installed and working (for this article ver. 17.2 was used)
You have downloaded MySQL Connector/J and extracted archive contents in any folder – we’ll refer to that path as $CONNECTOR_J_ROOT (for this article ver. 5.1.44 was used)

Convert keys and certificates in a Java friendly format
As Oracle SQL Developer and Connector/J are written in Java the keys and certificates in PEM format will not be recognised. We need to create a Truststore for the server certificate and a Keystore for the Client key and certificate. We will use JKS but PKCS12 is also an option.
To manage keystores Java comes with a tool called “keytool”. We’ll use it to convert and import our certificates and key into JKS store.

Create a truststore

Open your terminal and execute the following changing “server-ca.pem” to the name of your server ca file

keytool -import -v -trustcacerts -alias sql-server-ca -file server-ca.pem -keystore truststore.ts

Create a keystore

Combine client details

Usually client key and client certificate come into separate files but to import them in a JKS keystore they need to be combined. For that purpose we’ll use openssl library as follows:

openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -certfile server-ca.pem -out client-keys-combined.pkcs12

Import client details into a keystore

Java supports PKCS12 keystores so you can directly use it but for consistency let’s convert it to JKS by executing the following:

keytool -v -importkeystore \
    -srckeystore client-keys-combined.pkcs12 -srcstoretype PKCS12 -srcalias 1 \
    -destkeystore keystore.ks -deststoretype JKS -destalias mysql-client-details

note the “-srcalias 1” which identifies that first and only entry but it is required so that we can set “-destalias”

Check store contents

To list the entries in a JKS store use the following:

keytool -list -keystore keystore.ks

Steps to set up

MySQL connection options

MySQL Connector/J documentation describes an option to send each connection configuration properties at creation . That allows us to pass SSL related properties just before the connection is made.
Here is a list of all the properties needed when SSL/TLS is enbled with client certificate authentication:

useSSL=true
requireSSL=true
verifyServerCertificate=true
trustCertificateKeyStoreUrl=file:/path/to/truststore.ts
trustCertificateKeyStoreType=JKS
trustCertificateKeyStorePassword=mypass
clientCertificateKeyStoreUrl=file:/path/to/keystore.ks
clientCertificateKeyStoreType=JKS
clientCertificateKeyStorePassword=mypass

The standard way for passing these properties is through the connection uri as key value pairs:

jdbc:mysql:server_host:port/database?enableSSL=true&requireSSL=true&verifyServerCertificate=true....

So far so good. Let’s see how we can do that in Oracle SQL Developer

Enable MySQL support in Oracle SQL Developer

Oracle SQL Developer supports loading 3rd party JDBC drivers. We’ll use that to enable MySQL support.

1. Open Oracle SQL Developer
2. Go to Preferences (Command+, on Mac) from window menus.
3. Navigate to Database -> Third Party JDBC Drivers
4. Click on Add Entry and browse to your $CONNECTOR_J_ROOT selecting the only .jar file inside.
5. Click on OK to confirm and close the Preferences box

Create a new connection

Now that we have loaded the MySQL driver let’s create a new connection

1. Go to File -> New and select a Database Connection
2. Enter Connection name
3. Enter Username
4. Enter Password
5. Enter Database hostname or ip address
6. Enter Database port
7. Leave database empty

As there is no field for connection properties and Oracle SQL Developer will construct a connection uri like $USER@//$HOST:$PORT/$DATABASE  we will leave the database field empty and abuse the port field to introduce our connection properties by filling in the following:

3306/mydatabase?enableSSL=true&requireSSL=true&verifyServerCertificate=true...the_rest_of_ssl_properties...&1=1

Note the &1=1 at the end. It is a workaround of the fact that Oracle SQL Developer puts /null or /$database at the end and break the value of our last parameter. If we add that string it will be ignored by the connection uri parser in Connector/J, because 1=1/null  refer to a property with name “1” which does not exist.

Test and Go

Hit the “Test” button and you should see Status: Success! Don’t forget to Save.

This works and we can stop here but the properties string is quite long for the small port field and that makes it very hard to manage especially if you have many connections with different settings i.e. connecting to microservices databases.

So what else can be done?

Connector/J documentation describes a few predefined profiles (sets of properties) which are designed to fine-tune the driver for specific databases and are passed to each connection during initialisation. They can be enabled by passing a parameter in the connection uri as described above.

jdbc:mysql:server_host:port/database?useProfiles=profile_name

This can be quite helpful as we can pack our properties into different files and load them with one property in the connection uri. The downside is these profiles are available only inside the Connector/J jar file and can be loaded from there because the classpath inside a jar defaults to its root.
So we are going to unpack the jar, put our profiles and pack it again. Then when the jar is loaded into Oracle SQL Developer profiles can be used as a value in the port field i.e. 3306/mydatabase?useProfiles=myprofile1&1=1 which is much more clear but hides the configuration a bit.

Packing profiles in the jar

Create a profile file

The file must be named with extension .properties and must be following the Java properties file rules
Open your favourite text editor and enter the following changing the paths and passwords accordingly:

useSSL=true
requireSSL=true
verifyServerCertificate=true
trustCertificateKeyStoreUrl=file:/path/to/truststore.ts
trustCertificateKeyStoreType=JKS
trustCertificateKeyStorePassword=mypass
clientCertificateKeyStoreUrl=file:/path/to/keystore.ks
clientCertificateKeyStoreType=JKS
clientCertificateKeyStorePassword=mypass

Save as e.g. myprofile1.properties

Repack the Connector/J jar

1. Open a Terminal window and navigate to $CONNECTOR_J_ROOT
2. Create a new folder named unpacked and go inside

   mkdir unpacked; cd unpacked

3. Unpack the jar

   jar -xf ../mysql-connector-java-5.1.44-bin.jar

4. Copy myprofile1 (or as many profiles you need) to configs folder

   cp /path/to/myprofile1.propertis com/mysql/jdbc/configs

5. Make a new jar

   jar -cf ../mysql-connector-java-5.1.44-bin-with-myprofiles.jar *

Setup Oracle SQL Developer

Now go to Oracle SQL Developer and replace the original Connector/J jar file with your new jar.
Edit your connection and change the Port value to look like

3306/mydatabase?useProfiles=myprofile1&1=1

Test, Save and Enjoy!

Notes

If you upgrade Connector/J to a new version, you will have to repeat only the repacking steps and reload the driver in Oracle SQL Developer.

I hope I managed to save someone a few hours of searching and reading partial information.