Security should be a priority for any application and there is rarely a reason not to encrypt the connection between the server and the database. Unfortunately, seeing an encrypted setup in practice is more the exception that the rule, probably partially due to the complexity associated with the setup.

With an unencrypted connection, anyone with access to the network can eavesdrop and potentially tamper with the data being transferred. TLS/SSL is expensive, but turning it off for performance reasons is seldom a good idea. If you do so, you should take other security precautions, such as make sure there are no other processes running in the network with your application and database servers. If your app talks to a remote database, encryption is an absolute must.

In this post, we’ll show how to set up a TLS-protected connection between WildFly and MySQL. We’ll actually use Cloud SQL (second generation) in this example, but the setup is essentially the same for any JDBC connection.

Step 1: Set up a database server

Let’s start by configuring a database instance to require TLS connections. The process is very simple – just follow Cloud SQL’s guide to creating and managing instance, or MySQL’s reference manual for an unmanaged solution.

Under normal circumstances, you should consider using Cloud SQL Proxy for accessing your Cloud SQL instance securely. We will not use this setup for the matter discussed in this post in order to demonstrate access with plain TLS, which is usable for standard MySQL as well as other databases.

Make sure you whitelist your IP address in the configuration of your Cloud SQL instance and check the Allow only SSL connection option. Also make sure you create a database and set up a user – we’ll use this data for our datasource. In this post, we’ll use credentials foo/bar, a database called test, and a database instance listening on a made-up address 1.1.1.1. If you follow the guide correctly, you’ll end up with three files – server-ca.pem (self-signed server certificate), client-cert.pem (client certificate), and client-key.pem (client’s private key).

To verify that the database server is configured correctly, try connecting to it with your standard MySQL client:

Step 2: Prepare certificates

In order to use the certificates obtained in the previous step in Java, we’ll create two Java truststore files – trustore.jks containing the server certificate, and keystore.jks with the client certificate. This step requires a JDK with JSSE (Java Secure Sockets Extension). All the modern JDKs (since 1.4.1) contain the extension.

To create a truststore, we need Java’s keytool utility. All truststore files are password protected – we’ll use password in our examples, and assume keytool (typically located in the bin subdirectory of the JDK) is on our path.

We create trustore.jks and import server-ca.pem from Step 1 by running the following command in the directory with our certificates:

To create keystore.jks, we’ll use our two client files to generate a single PKCS#12 file. For this, we’ll need OpenSSL command line tool:

The output can be imported into keystore.jks:

The two truststore files are ready to be consumed by a Java application. To be picked up by JSSE, we need to pass a few system properties to the JVM: javax.net.ssl.keyStore, javax.net.ssl.keyStorePassword, javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword. In the case of WildFly, properties can be supplied through the JAVA_OPTS environment variable (see $JBOSS_HOME/bin/standalone.conf). Combined with WildFly’s default options, as printed during startup, we can set up JAVA_OPTS like this (as of WildFly 10.1.0.Final):

The example assumes the trustore files are in the directory where we run WildFly from, e.g. $JBOSS_HOME.

Step 3: Configure datasource

Now that we’ve set up the certificates, it’s time to set up a datasource in WildFly. To do so, we’ll download the JDBC driver, start the application server and follow Configuring a datasource on WildFly. The driver can be installed with the following CLI commands:

Next, we create a datasource:

Note the useSSL=true, requireSSL=true, and verifyServerCertificate=true flags in the connection URL indicating a secure connection.

Step 4: Test the connection

In order to verify that everything is set up correctly, we need to check that we can connect to the database from WildFly, and that the connection is indeed encrypted.

While still in the CLI, we can use the following command and check we get a positive response:

Since we set up the database to require a secure connection, our connection is indeed encrypted. To double check, we can use a tool like tcpdump. Just start the tool and watch the output as you test the connection via the CLI:

You want the traffic to be garbled, like in the output above.