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:

$ mysql -u foo -p -h 1.1.1.1 --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem \
  --ssl-key=client-key.pem

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:

$ keytool -import -alias server -file server-ca.pem -keystore truststore.jks \
  -storepass password

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:

$ openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
  -out client.p12 -name client

The output can be imported into keystore.jks:

$ keytool -importkeystore -deststorepass password -destkeystore keystore.jks \
  -srckeystore client.p12 -srcstoretype PKCS12 -srcstorepass password \
  -alias client

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

$ export JAVA_OPTS="-server -Xms64m -Xmx512m \
  -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m -Djava.net.preferIPv4Stack=true \
  -Djboss.modules.system.pkgs=org.jboss.byteman -Djava.awt.headless=true \
  -Djavax.net.ssl.keyStore=keystore.jks -Djavax.net.ssl.keyStorePassword=password \
  -Djavax.net.ssl.trustStore=truststore.jks -Djavax.net.ssl.trustStorePassword=password"

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:

[standalone@localhost:9990 /] module add --name=com.mysql\
  --resources=mysql-connector-java.jar\
  --dependencies=javax.api,javax.transaction.api
[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=mysql:\
add(driver-name=mysql,driver-module-name=com.mysql,\
driver-xa-datasource-class-name=com.mysql.jdbc.jdbc2.optional.MysqlXADataSource)
{"outcome" => "success"}

Next, we create a datasource:

[standalone@localhost:9990 /] data-source add --name=MySqlDS --driver-name=mysql \
--jndi-name=java:jboss/datasources/MySqlDS \
--connection-url=jdbc:mysql://1.1.1.1:3306/test?useSSL=true&requireSSL=true&verifyServerCertificate=true \
--user-name=foo --password=bar --enabled=true

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:

[standalone@localhost:9990 /] data-source test-connection-in-pool --name=MySqlDS
true

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:

$ tcpdump -vv -X host 1.1.1.1
tcpdump: data link type PKTAP
tcpdump: listening on pktap, link-type PKTAP (Apple DLT_PKTAP), capture size 262144 bytes
23:31:22.697824 IP (tos 0x2,ECT(0), ttl 64, id 56259, offset 0, flags [DF], proto TCP (6), length 89)
    host.61391 > 1.1.1.1.bc.googleusercontent.com.mysql: Flags [P.], cksum 0x042f (correct), 
    seq 1486677046:1486677083, ack 1535448375, win 4096, options [nop,nop,TS val 964209410 ecr 23504386], 
    length 37
	0x0000:  0026 444e c879 acbc 32c9 f89b 0800 4502  .&DN.y..2.....E.
	0x0010:  0059 dbc3 4000 4006 5ad4 c0a8 014d 689a  .Y..@.@.Z....Mh.
	0x0020:  d975 efcf 0cea 589c e436 5b85 1537 8018  .u....X..6[..7..
	0x0030:  1000 042f 0000 0101 080a 3978 ab02 0166  .../......9x...f
	0x0040:  a602 1703 0100 2089 3816 7e9e 2262 1811  ........8.~."b..
	0x0050:  2cfe 9a18 444a 2e41 4fb2 1f7c 2137 d63c  ,...DJ.AO..|!7.<
	0x0060:  e08c bef3 bab5 04                        .......
23:31:22.697935 IP (tos 0x2,ECT(0), ttl 64, id 14471, offset 0, flags [DF], proto TCP (6), length 89)
    host.61391 > 1.1.1.1.bc.googleusercontent.com.mysql: Flags [P.], cksum 0xd036 (correct), 
    seq 37:74, ack 1, win 4096, options [nop,nop,TS val 964209410 ecr 23504386], 
    length 37
	0x0000:  0026 444e c879 acbc 32c9 f89b 0800 4502  .&DN.y..2.....E.
	0x0010:  0059 3887 4000 4006 fe10 c0a8 014d 689a  .Y8.@.@......Mh.
	0x0020:  d975 efcf 0cea 589c e45b 5b85 1537 8018  .u....X..[[..7..
	0x0030:  1000 d036 0000 0101 080a 3978 ab02 0166  ...6......9x...f
	0x0040:  a602 1703 0100 2027 01e9 d138 ad18 e2ac  .......'...8....
	0x0050:  504b df4e 2368 4c61 eeaf 9741 5b3e 9e77  PK.N#hLa...A[>.w
	0x0060:  f944 84e1 cfbe 54                        .D....T
23:31:22.698109 IP (tos 0x2,ECT(0), ttl 64, id 60299, offset 0, flags [DF], proto TCP (6), length 89)
    host.61391 > 1.1.1.1.bc.googleusercontent.com.mysql: Flags [P.], cksum 0xba9e (correct), 
    seq 74:111, ack 1, win 4096, options [nop,nop,TS val 964209410 ecr 23504386], 
    length 37
	0x0000:  0026 444e c879 acbc 32c9 f89b 0800 4502  .&DN.y..2.....E.
	0x0010:  0059 eb8b 4000 4006 4b0c c0a8 014d 689a  .Y..@.@.K....Mh.
	0x0020:  d975 efcf 0cea 589c e480 5b85 1537 8018  .u....X...[..7..
	0x0030:  1000 ba9e 0000 0101 080a 3978 ab02 0166  ..........9x...f
	0x0040:  a602 1503 0100 200b 8f02 1d8a 78af 0b26  ............x..&
	0x0050:  eaaa f0dd 21c1 7506 614e fac6 6cf1 22c8  ....!.u.aN..l.".
	0x0060:  89da d7d6 0b34 40                        .....4@
...

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