Learned By Doing: How to connect a Studio job to a MySQL Server with encrypted connections (SSL/X509)

Overview

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.

 

Environment

This article is based on the following system and environment:

  • Two virtual machines hosting Ubuntu 16.04

    • The two machines (mydb and myclient) are able to access each other
    • there is no firewall configured
  • MySQL: mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64)

    • server package is installed on mydb machine
    • client package is installed on both machines
  • JDK : java version 1.8.0_112 (build 1.8.0_112-b15)

    • is installed on the two machines (manual installation)
    • embedded default alternative Java was removed
  • Studio 6.3.1 is installed on the myclient machine

 

Securing the MySQL Server

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

Screenshot from 2017-09-01 12-03-34.png

 

The keys and certificates created are stored in /var/lib/mysql:

sudo find /var/lib/mysql/ -name *.pem -ls

Screenshot from 2017-09-01 12-08-22.png

 

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%';

Screenshot from 2017-08-31 12-20-23.png

 

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:

mysql> \s

Screenshot from 2017-09-01 14-31-50.png

 

The line SSL: Cipher in use is ... proves that your MySQL server is now secured.

 

Prepare the database for testing

 

Create secured users

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

REQUIRE SSL
  • Tells the server to permit only encrypted connections for the account.
REQUIRE X509
  • Requires that clients present a valid certificate, but the exact certificate, issuer, and subject do not matter.
  • The only requirement is that it should be possible to verify its signature with one of the CA certificates.
  • Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

To test access from a Studio job in these two cases, create two users called myuserssl and myuserx509.

  1. Connect as root:

    mysql -u root -p -h mybd
  2. To create the REQUIRE SSL user, type:

    CREATE USER 'myuserssl'@'myclient' IDENTIFIED BY 'cangetin' REQUIRE SSL;
  3. To create the REQUIRE x509 user, type:

    CREATE USER 'myuserx509'@'myclient' IDENTIFIED BY 'cangetin' REQUIRE X509;

 

Test access from the client machine

 

With the REQUIRE SSL user

  1. 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
  2. Look for the SSL line to check that you are using a secured connection:

    Screenshot from 2017-09-01 14-47-01.png

     

  3. 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:

    Screenshot at 2017-09-14 22-55-13.png

 

With the REQUIRE x509 user

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.

 

  1. Copy the following files to the client machine, for example to $HOME/myssl:

    • ca.pem
    • client-cert.pem
    • client-key.pem
  2. 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

    Screenshot from 2017-09-01 14-59-14.png

 

Create a database

The tMySQL* components need to access a specific database, so you have to create one.

  1. From the mydb machine, type:

    CREATE DATABASE testssl;
  2. 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';

    Screenshot from 2017-09-01 15-05-46.png

     

    GRANT ALL ON testssl.* TO 'myuserx509'@'myclient';
    SHOW GRANTS FOR 'myuserx509'@'myclient';

    Screenshot from 2017-08-31 15-28-59.png

 

Before you test

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:

  • Java will check against the configured truststore before it trusts the certificate that the MySQL server sends to start an SSL connection. So you have to add the CA or server certificate to the Java truststore.
  • The MySQL JDBC driver has its own specific parameters you need to add in tMySQL* components.

    • to specify that the connection requires SSL
    • to replace the MySQL client's parameters used to connect as mysuserx509

 

  1. To keep it simple, use the default Java truststore.

    cd /opt/java/jdk1.8.0_112/jre/lib/security/
    sudo $JAVA_HOME/bin/keytool -import -trustcacerts -file ~/myssl/ca.pem -alias MySQL_CA -keystore cacerts -storepass changeit
  2. 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

    Screenshot from 2017-08-31 17-46-34.png

  3. Create the client certificate keystore.

    1. 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 ">
    2. Check the content of this new keystore:

      $JAVA_HOME/bin/keytool -list -keystore client.jks -storepass cangetin -alias MYSQL_CLIENT -V

      Screenshot from 2017-08-31 18-12-03.png

       

     

    Studio testing

    You are ready to start testing!

     

    Access the database with the REQUIRE SSL user

    1. Create a simple Job to connect to your database, adding this information to the Component tab:

      • host: mydb
      • port : 3306
      • database: testssldb
      • username : myuserssl
      • password : cangetin
      • additional JDBC parameters: "noDatetimeStringSync=true&useSSL=true&requireSSL=true"

      Screenshot from 2017-08-31 17-39-15.png

       

    2. Run your Job, and it will finish successfully.

      Screenshot from 2017-08-31 17-50-15.png

     

    Access the database with the REQUIRE x509 user

    Duplicate the previous Job, then change the following settings:

    • user: myuserx509
    • additional JDBC parameters: "noDatetimeStringSync=true&useSSL=true&requireSSL=true&clientCertificateKeyStoreUrl=file:///home/usertest/myssl/client.jks&clientCertificateKeyStorePassword=cangetin&trustCertificateKeyStoreUrl=file:///opt/java/jdk1.8.0_112/jre/lib/security/cacerts&trustCertificateKeyStorePassword=changeit"

    Note: the truststore, even if it is the default one used by your JVM, must be explicitly provided.

     

    What if it doesn't work?

    Analyze traces of your Job by setting the log level to debug traces and adding the JVM option -Djavax.net.debug=ssl.

    Screenshot from 2017-08-31 18-21-53.png

     

    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.

Version history
Revision #:
13 of 13
Last update:
‎09-22-2017 02:54 PM
Updated by:
 
Tags (1)