6.9 Connecting Securely Using SSL

Connector/J can encrypt all data communicated between the JDBC driver and the server (except for the initial handshake) using SSL. There is a performance penalty for enabling connection encryption, the severity of which depends on multiple factors including (but not limited to) the size of the query, the amount of data returned, the server hardware, the SSL library used, the network bandwidth, and so on.

The system works through two Java keystore files: one file contains the certificate information for the server (truststore in the examples below), and another contains the keys and certificate for the client (keystore in the examples below). All Java keystore files are protected by the password supplied to the keytool when you created the files. You need the file names and the associated passwords to create an SSL connection.

For SSL support to work, you must have the following:

• A MySQL server that supports SSL, and compiled and configured to do so. For more information, see Using Encrypted Connections and Configuring SSL Library Support.

• A signed client certificate, if using mutual (two-way) authentication.

By default, Connector/J establishes secure connections with the MySQL servers. Note that MySQL servers 5.7 and 8.0, when compiled with OpenSSL, can automatically generate missing SSL files at startup and configure the SSL connection accordingly.

For 8.0.12 and earlier: As long as the server is correctly configured to use SSL, there is no need to configure anything on the Connector/J client to use encrypted connections (the exception is when Connector/J is connecting to very old server versions like 5.6.25 and earlier or 5.7.5 and earlier, in which case the client must set the connection property useSSL=true in order to use encrypted connections). The client can demand SSL to be used by setting the connection property requireSSL=true; the connection then fails if the server is not configured to use SSL. Without requireSSL=true, the connection just falls back to non-encrypted mode if the server is not configured to use SSL.

For 8.0.13 and later: As long as the server is correctly configured to use SSL, there is no need to configure anything on the Connector/J client to use encrypted connections. The client can demand SSL to be used by setting the connection property sslMode=REQUIRED, VERIFY_CA, or VERIFY_IDENTITY; the connection then fails if the server is not configured to use SSL. With sslMode=PREFERRED, the connection just falls back to non-encrypted mode if the server is not configured to use SSL. For X-Protocol connections, the connection property xdevapi.ssl-mode specifies the SSL Mode setting, just like sslMode does for MySQL-protocol connections (except that PREFERRED is not supported by X Protocol); if not explicitly set, xdevapi.ssl-mode takes up the value of sslMode ( if xdevapi.ssl-mode is not set and sslMode is set to PREFERRED, xdevapi.ssl-mode is set to REQUIRED).

For additional security, you can setup the client for a one-way (server or client) or two-way (server and client) SSL authentication, allowing the client or the server to authenticate each other's identity.

TLS versions: The allowable versions of TLS protocol can be restricted using the connection properties tlsVersions and, for X DevAPI connections and for release 8.0.19 and later, xdevapi.tls-versions (when xdevapi.tls-versions is not specified, it takes up the value of tlsVersions). If no such restrictions have been specified, Connector/J attempts to connect to the server with the TLSv1.2 and TLSv1.3.

Notes

• Since Connector/J 8.0.28, the connection property enabledTLSProtocols has been renamed to tlsVersions, and enabledSSLCipherSuites has been renamed to tlsCiphersuites; the original names remain as aliases.

• For Connector/J 8.0.26 and later: TLSv1 and TLSv1.1 were deprecated in Connector/J 8.0.26 and removed in release 8.0.28; the removed values are considered invalid for use with connection options and session settings. Connections can be made using the more-secure TLSv1.2 and TLSv1.3 protocols. Using TLSv1.3 requires that the server be compiled with OpenSSL 1.1.1 or higher and Connector/J be run with a JVM that supports TLSv1.3 (for example, Oracle Java 8u261 and above).

• For Connector/J 8.0.18 and earlier when connecting to MySQL Community Server 5.6 and 5.7 using the JDBC API: Due to compatibility issues with MySQL Server compiled with yaSSL, Connector/J does not enable connections with TLSv1.2 and higher by default. When connecting to servers that restrict connections to use those higher TLS versions, enable them explicitly by setting the Connector/J connection property enabledTLSProtocols (e.g., set enabledTLSProtocols=TLSv1.2,TLSv1.3).

Cipher Suites: Since release 8.0.19, the cipher suites usable by Connector/J are pre-restricted by a properties file that can be found at src/main/resources/com/mysql/cj/TlsSettings.properties inside the src folder on the source tree or in the platform-independent distribution archive (in .tar.gz or .zip format) for Connector/J. The file contains four sections, listing in each the mandatory, approved, deprecated, and unacceptable ciphers. Only suites listed in the first three sections can be used. The last section (unacceptable) defines patterns or masks that blocklist unsafe cipher suites. Practically, with the allowlist already given in the first three sections, the blocklist patterns in the forth section are redundant; but they are there as an extra safeguard against unwanted ciphers. The allowlist and blocklist of cipher suites apply to both JDBC and X DevAPI connections.

The allowable cipher suites for SSL connections can be restricted using the connection properties tlsCiphersuites and, for X DevAPI connections and for release 8.0.19 and later, xdevapi.tls-ciphersuites (when xdevapi.tls-ciphersuites is not specified, it takes up the value of tlsCiphersuites). If no such restrictions have been specified, Connector/J attempts to establish SSL connections with any allowlisted cipher suites that the server accepts.

Setting up Server Authentication

For 8.0.12 and earlier: Server authentication via server certificate verification is enabled when the Connector/J connection properties useSSL AND verifyServerCertificate are both true. Hostname verification is not supported—host authentication is by certificates only.

For 8.0.13 and later: Server authentication via server certificate verification is enabled when the Connector/J connection property sslMode is set to VERIFY_CA or VERIFY_IDENTITY. If sslMode is not set, server authentication via server certificate verification is enabled when the legacy properties useSSL AND verifyServerCertificate are both true.

Certificates signed by a trusted CA.  When server authentication via server certificate verification is enabled, if no additional configurations are made regarding server authentication, Java verifies the server certificate using its default trusted CA certificates, usually from $JAVA_HOME/lib/security/cacerts.

Using self-signed certificates.  It is pretty common though for MySQL server certificates to be self-signed or signed by a self-signed CA certificate; the auto-generated certificates and keys created by the MySQL server are based on the latter—that is, the server generates all required keys and a self-signed CA certificate that is used to sign a server and a client certificate. The server then configures itself to use the CA certificate and the server certificate. Although the client certificate file is placed in the same directory, it is not used by the server.

To verify the server certificate, Connector/J needs to be able to read the certificate that signed it, that is, the server certificate that signed itself or the self-signed CA certificate. This can be accomplished by either importing the certificate (ca.pem or any other certificate) into the Java default truststore (although tampering the default truststore is not recommended) or by importing it into a custom Java truststore file and configuring the Connector/J driver accordingly. Use Java's keytool (typically located in the bin subdirectory of your JDK or JRE installation) to import the server certificates:

$> keytool -importcert -alias MySQLCACert -file ca.pem \
    -keystore truststore -storepass mypassword

Supply the proper arguments for the command options. If the truststore file does not already exist, a new one will be created; otherwise the certificate will be added to the existing file. Interaction with keytool looks like this:

Owner: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate
Issuer: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate
Serial number: 1
Valid from: Thu Feb 16 11:42:43 EST 2017 until: Sun Feb 14 11:42:43 EST 2027
Certificate fingerprints:
	 MD5:  18:87:97:37:EA:CB:0B:5A:24:AB:27:76:45:A4:78:C1
	 SHA1: 2B:0D:D9:69:2C:99:BF:1E:2A:25:4E:8D:2D:38:B8:70:66:47:FA:ED
	 SHA256: C3:29:67:1B:E5:37:06:F7:A9:93:DF:C7:B3:27:5E:09:C7:FD:EE:2D:18:86:F4:9C:40:D8:26:CB:DA:95:A0:24
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 2048-bit RSA key
Version: 1
Trust this certificate? [no]:  yes
Certificate was added to keystore

The output of the command shows all details about the imported certificate. Make sure you remember the password you have supplied. Also, be mindful that the password will have to be written as plain text in your Connector/J configuration file or application source code.

The next step is to configure Java or Connector/J to read the truststore you just created or modified. This can be done by using one of the following three methods:

1. Using the Java command line arguments:

   -Djavax.net.ssl.trustStore=path_to_truststore_file 
   -Djavax.net.ssl.trustStorePassword=mypassword

2. Setting the system properties directly in the client code:

   System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file"); 
   System.setProperty("javax.net.ssl.trustStorePassword","mypassword");

3. Setting the Connector/J connection properties:

   trustCertificateKeyStoreUrl=file:path_to_truststore_file 
   trustCertificateKeyStorePassword=mypassword

Notice that when used together, the connection properties override the values set by the other two methods. Also, whatever values set with connection properties are used in that connection only, while values set using the system-wide values are used for all connections (unless overridden by the connection properties). For Connector/J 8.0.22 and later: Setting the connection property fallbackToSystemTrustStore to false prevents Connector/J from falling back to the system-wide truststore setup you created using method (1) or (2) when method (3) is not used.

With the above setup and the server authentication enabled, all connections established are going to be SSL-encrypted, with the server being authenticated in the SSL handshake process, and the client can now safely trust the server it is connecting to.

For X-Protocol connections, the connection properties xdevapi.ssl-truststore, xdevapi.ssl-truststore-type, xdevapi.ssl-truststore-password, and xdevapi.ssl-fallbackToSystemTrustStore specify the truststore settings, just like trustCertificateKeyStoreUrl, trustCertificateKeyStoreType, trustCertificateKeyStorePasswordamd fallbackToSystemTrustStore do for MySQL-protocol connections; if not explicitly set, xdevapi.ssl-truststore, xdevapi.ssl-truststore-type, xdevapi.ssl-truststore-password, and xdevapi.ssl-fallbackToSystemTrustStore take up the values of trustCertificateKeyStoreUrl, trustCertificateKeyStoreType, trustCertificateKeyStorePassword, and fallbackToSystemTrustStore respectively.

Service Identity Verification.  For 8.0.13 and later: Beyond server authentication via server certificate verification, when sslMode is set to VERIFY_IDENTITY, Connector/J also performs host name identity verification by checking whether the host name that it uses for connecting matches the Common Name value in the server certificate.

Setting up Client Authentication

The server may want to authenticate a client and require the client to provide an SSL certificate to it, which it verifies against its known certificate authorities or performs additional checks on the client identity if needed (see CREATE USER SSL/TLS Options for details). In that case, Connector/J needs to have access to the client certificate, so it can be sent to the server while establishing new database connections. This is done using the Java keystore files.

To allow client authentication, the client connecting to the server must have its own set of keys and an SSL certificate. The client certificate must be signed so that the server can verify it. While you can have the client certificates signed by official certificate authorities, it is more common to use an intermediate, private, CA certificate to sign client certificates. Such an intermediate CA certificate may be self-signed or signed by a trusted root CA. The requirement is that the server knows a CA certificate that is capable of validating the client certificate.

Some MySQL server builds are able to generate SSL keys and certificates for communication encryption, including a certificate and a private key (contained in the client-cert.pem and client-key.pem files), which can be used by any client. This SSL certificate is already signed by the self-signed CA certificate ca.pem, which the server may have already been configured to use.

If you do not want to use the client keys and certificate files generated by the server, you can also generate new ones using the procedures described in Creating SSL and RSA Certificates and Keys. Notice that, according to the setup of the server, you may have to reuse the already existing CA certificate the server is configured to work with to sign the new client certificate, instead of creating a new one.

Once you have the client private key and certificate files you want to use, you need to import them into a Java keystore so that they can be used by the Java SSL library and Connector/J. The following instructions explain how to create the keystore file:

• Convert the client key and certificate files to a PKCS #12 archive:

  $> openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
    -name "mysqlclient" -passout pass:mypassword -out client-keystore.p12

• Import the client key and certificate into a Java keystore:

  $> keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 \
   -srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword

  Supply the proper arguments for the command options. If the keystore file does not already exist, a new one will be created; otherwise the certificate will be added to the existing file. Output by keytool looks like this:

  Entry for alias mysqlclient successfully imported.
  Import command completed:  1 entries successfully imported, 0 entries failed or cancelled

  Make sure you remember the password you have chosen. Also, be mindful that the password will have to be written as plain text in your Connector/J configuration file or application source code.

After the step, you can delete the PKCS #12 archive (client-keystore.p12 in the example).

The next step is to configure Java or Connector/J so that it reads the keystore you just created or modified. This can be done by using one of the following three methods:

1. Using the Java command line arguments:

   -Djavax.net.ssl.keyStore=path_to_keystore_file
   -Djavax.net.ssl.keyStorePassword=mypassword

2. Setting the system properties directly in the client code:

   System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file"); 
   System.setProperty("javax.net.ssl.keyStorePassword","mypassword");

3. Through Connector/J connection properties:

   clientCertificateKeyStoreUrl=file:path_to_truststore_file 
   clientCertificateKeyStorePassword=mypassword

Notice that when used together, the connection properties override the values set by the other two methods. Also, whatever values set with connection properties are used in that connection only, while values set using the system-wide values are used for all connections (unless overridden by the connection properties). For Connector/J 8.0.22 and later: Setting the connection property fallbackToSystemKeyStore to false prevents Connector/J from falling back to the system-wide keystore setup you created using method (1) or (2) when method (3) is not used.

With the above setups, all connections established are going to be SSL-encrypted with the client being authenticated in the SSL handshake process, and the server can now safely trust the client that is requesting a connection to it.

For Connector/J 8.0.22 and later: For X-Protocol connections, the connection properties xdevapi.ssl-keystore, xdevapi.ssl-keystore-type, xdevapi.ssl-keystore-password, and xdevapi.ssl-fallbackToSystemKeyStore specify the keystore settings, just like trustCertificateKeyStoreUrl, trustCertificateKeyStoreType, trustCertificateKeyStorePassword, and fallbackToSystemTKeyStore do for MySQL-protocol connections; if not explicitly set, xdevapi.ssl-keystore, xdevapi.ssl-keystore-type, xdevapi.ssl-keystore-password, and xdevapi.ssl-fallbackToSystemKeyStore take up the values of clientCertificateKeyStoreUrl, clientCertificateKeyStoreType, clientCertificateKeyStorePassword, and fallbackToSystemKeyStore respectively.

Setting up 2-Way Authentication

Apply the steps outlined in both Setting up Server Authentication and Setting up Client Authentication to set up a mutual, two-way authentication process in which the server and the client authenticate each other before establishing a connection.

Although the typical setup described above uses the same CA certificate in both ends for mutual authentication, it does not have to be the case. The only requirements are that the CA certificate configured in the server must be able to validate the client certificate and the CA certificate imported into the client truststore must be able to validate the server certificate; the two CA certificates used on the two ends can be distinct.

Debugging an SSL Connection

JSSE provides debugging information to stdout when you set the system property -Djavax.net.debug=all. Java then tells you what keystores and truststores are being used, as well as what is going on during the SSL handshake and certificate exchange. That will be helpful when you are trying to debug a failed SSL connection.