MySQL supports both encryption and secure authentication. This solution describes how to connect using DbVisualizer to a secured mySQL server. The description is based on connecting to MySQL 8. Make sure to read related MySQL documentation for the MySQL version you are using. 


Related MySQL documentation


Make the client trust the server

To verify the server certificate, the JDBC Driver 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 driver accordingly. Use Java's keytool (typically located in the bin subdirectory of your JDK or JRE installation) to import the server certificates. The server certificate is typically obtained from your DBA. 

keytool -importcert -alias MySQLCACert -file ca.pem -keystore mytruststore -storepass mypassword

Connect using DbVisualizer

Open the connection Properties Tab for the MySQL connection and select the Driver Properties category. Edit the following driver properties:

sslMode=VERIFY_CA 
trustCertificateKeyStoreUrl=file:/somedirectory/mytruststore 
trustCertificateKeyStorePassword=mypassword

Observe that for the Driver (Connector/J) version 8.0.12 and earlier other properties than sslMode is used. Please see the Driver Documentation for details. 


An alternative (not recommended) to setting the trust store on a per connection basis is to set it globally on the java/application level. This is done by setting the following java properties in the Java VM Properties setup in Tools->Tool Properties and in the General top level category. 

-Djavax.net.ssl.trustStore=/somedirectory/mytruststore 
-Djavax.net.ssl.trustStorePassword=mypassword

Note that the Driver Property sslMode still need to be set to VERIFY_CA in this case.   


Note that setting the trust store on a java/application level may affect other functionality of DbVisualizer if other needed certificates are missing in the referenced trust store. 

E.g. if the certificate of dbvis.com cannot be verified neither Help->Contact Support nor Help->Check for Update will work. When creating a custom trust store to be used on a Java/application level you should always start with a trust store containing the needed certificates and add/import your custom certificate to it. An example of such a trust store is the default java trust store. The location of this trust store is dependant of the Java used. In most cases its location is at <Java Home>/lib/security/cacerts.


Following is a set of commands to copy the trust store included in java and the add the server certificate to it:

cp <Java Home>/lib/security/cacerts /somedirectory/mytruststore

keytool -importcert -alias MySQLCACert -file ca.pem -keystore /somedirectory/mytruststore -storepass changeit

Replace "<Java Home>/lib/security/cacerts" with the actual path of the java trust store. 


Setting up client authentication

Apart from making the communication from the client to the server encrypted and establishing secure authentication of the server identity, on the client, it is also possible to configure the MySQL server to require the user to authenticate by means of a certificate. This can be done by creating the user using the REQUIRE X509 clause as follows.

CREATE USER 'testssluser'@'%' identified by 'testsslpassword' REQUIRE X509;

This defines the database userid and password to be used as authentication when connecting to the dababase. But as the user has been created with REQUIRE X509, a client key and a client certificate must also be used when connecting to the dababase.


The client key and certificate file you should obtain from your DBA (typically named client-cert.pem and client-key.pem). 


Convert the keys using openssl

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

Import the key and certificate into a java keystore.

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

Change the password mypassword in the examples above to something of your own choice. 


Connect using DbVisualizer

Open the connection Properties Tab for the MySQL connection and select the Driver Properties category. Edit the following driver properties:

clientCertificateKeyStorePassword=mypassword
clientCertificateKeyStoreUrl=file:/somedirectory/mykeystore