When connections to a database are not secured (encrypted), someone may inspect the traffic and make a copy of the data on the fly. Or worse, they may change the data before it is received by the client or before it is stored in the database. That's why most database vendors provide, and most enterprise-class companies put into place, ways to secure access to their databases. MySQL relies on the Transport Layer Security (TLS) protocol. For details, see the MySQL encrypted connections documentation.
In the most common scenario, developers need to use encrypted connections to connect their Talend jobs to a database on which they have no (or limited) admin privileges.
This article describes the steps to configure a simple test environment (MySQL Server + Studio job) and explains some basic concepts you need to understand to achieve your first goal: connecting your Studio Job to a MySQL server using a secure connection.
This article is based on the following system and environment:
Two virtual machines hosting Ubuntu 16.04
MySQL: mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64)
JDK : java version 1.8.0_112 (build 1.8.0_112-b15)
To secure the MySQL server, you need to generate a private key and a certificate, so you need a certificate authority for signing the certificate. The certificate contains the public key anyone who want to communicate with your server has to use to encrypt a message. Then your secured MySQL server will use its private key to decrypt the message (this private key is the only way to decrypt the message).
Your goal is to access data in the database, not to pass a master class in cryptography, so the good news is that, starting with 5.7, MySQL provides the utility tool mysql-ssl-rsa-setup. This tool will do all the work for you, you just need to give the UNIX user you used to install the MySQL Server:
sudo mysql_ssl_rsa_setup --uid=mysql
The keys and certificates created are stored in /var/lib/mysql:
sudo find /var/lib/mysql/ -name *.pem -ls
Why are they here and why are they named like that? Because the latest versions of MySQL automatically check this folder for certificates named as they are during the starting sequence. So there is no need to change anything in your MySQL Server configuration file to start using encrypted connections—a simple restart of the service and the server is able to use secured connections!
sudo service mysql restart
To check if SSL mode is configured, use the MySQL client to connect as root (the only MySQL user account you have for the moment):
mysql -u root -p -h mydb
Then list the system variables related to SSL:
mysql> SHOW VARIABLES LIKE '%ssl%';
The openssl and ssl options are set and the server key/certificate pair you created is in use.
But when will MySQL use connections over SSL? According to the MySQL encrypted connections documentation: "By default, MySQL programs attempt to connect using encryption if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established."
Are you already using a secured connection? Check if your current connection is using SSL:
The line SSL: Cipher in use is ... proves that your MySQL server is now secured.
Most of the time, to force secured communication, DB admins restrict the way a MySQL user can communicate with the MySQL Server. According to the MySQL Reference Manual, there are two type of "secured" users):
To test access from a Studio job in these two cases, create two users called myuserssl and myuserx509.
Connect as root:
mysql -u root -p -h mybd
To create the REQUIRE SSL user, type:
CREATE USER 'myuserssl'@'myclient' IDENTIFIED BY 'cangetin' REQUIRE SSL;
To create the REQUIRE x509 user, type:
CREATE USER 'myuserx509'@'myclient' IDENTIFIED BY 'cangetin' REQUIRE X509;
To test using the REQUIRE SSL user, type the following command on the myclient machine to connect as myuserssl:
mysql -u "myuserssl" -p -h mydb
Look for the SSL line to check that you are using a secured connection:
To see the result you would get if connection was not secured, you can force a non-SSL connection:
mysql -u myssluser -p --ssl-mode=disabled
The error message appears immediately:
According to the definition, your REQUIRE x509 user must provide a certificate when trying to connect, so another key/certificate pair is needed. But the utility mysql-ssl-rsa-setup can help. If you look at the list of the key/certificates pairs created, a certificate/key pair was also created with the prefix client. You already have all you need to test the x509 connection.
Copy the following files to the client machine, for example to $HOME/myssl:
Mention them when connecting using the MySQL client:
mysql -u myuserx509 -p -h mydb --ssl-ca=~/myssl/ca.pem --ssl-cert=~/myssl/client-cert.pem --ssl-key=~/myssl/client-key.pem
The tMySQL* components need to access a specific database, so you have to create one.
From the mydb machine, type:
CREATE DATABASE testssl;
To access the database, the DB admin has to grant privileges for the users you just created:
GRANT ALL ON testssl.* TO 'myuserssl'@'myclient';
SHOW GRANTS FOR 'myuserssl'@'myclient';
GRANT ALL ON testssl.* TO 'myuserx509'@'myclient';
SHOW GRANTS FOR 'myuserx509'@'myclient';
Before you start testing with Studio, remember that when using Studio jobs, you are using Java and a JDBC driver to connect. It might be obvious, but this means that:
The MySQL JDBC driver has its own specific parameters you need to add in tMySQL* components.
To keep it simple, use the default Java truststore.
sudo $JAVA_HOME/bin/keytool -import -trustcacerts -file ~/myssl/ca.pem -alias MySQL_CA -keystore cacerts -storepass changeit
Acknowledge that you trust this certificate, and then check that it is correctly added:
sudo $JAVA_HOME/bin/keytool -list -keystore cacerts -storepass changeit -alias MYSQL_CA -V
Create the client certificate keystore.
This is required by the driver when a secured connection is created:
openssl pkcs12 -export -in ~/myssl/client-cert.pem -inkey ~/myssl/client-key.pem -out ~/myssl/client.jks -name ">
Check the content of this new keystore:
$JAVA_HOME/bin/keytool -list -keystore client.jks -storepass cangetin -alias MYSQL_CLIENT -V
You are ready to start testing!
Create a simple Job to connect to your database, adding this information to the Component tab:
Run your Job, and it will finish successfully.
Duplicate the previous Job, then change the following settings:
Note: the truststore, even if it is the default one used by your JVM, must be explicitly provided.
Analyze traces of your Job by setting the log level to debug traces and adding the JVM option -Djavax.net.debug=ssl.
With this setting you get a verbose output that records all the events related to SSL connections. If you are unable to debug further, our professional service experts can help you.