User Tools

Site Tools


Configure TLS/SSL between a MySQL database and EASA

1. Create user in mysql that will connect via ssl via the following commands,

  • mysql> GRANT ALL PRIVILEGES ON *.* TO '<ssl-user-here>'@'localhost' IDENTIFIED BY '<ssl-password-here>' REQUIRE SSL;
  • mysql> FLUSH PRIVILEGES;

2. Install openssl and configure the environment variables OPENSSL_CONF and PATH

3. Create an empty folder on the mysql machine. For example: C:\mysqlCerts\

4. 'cd' to the mysql installation bin folder (eg. C:\Program Files\MySQL\MySQL Server 5.7\bin)

5. In order to create the .pem files and certificates run the command: mysql_ssl_rsa_setup.exe --datadir C:\mysqlCerts\

6. Edit: C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Add the following lines:

ssl-ca = C:\mysqlCerts\ca.pem
ssl-cert = C:\mysqlCerts\\server-cert.pem
ssl-key = C:\mysqlCerts\\server-key.pem
require_secure_transport = ON

We show these additions below. Please note the double backslash to avoid a simple ' \s ' being replaced by a space.

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]
ssl-ca = C:\mysqlCerts\ca.pem
ssl-cert = C:\mysqlCerts\\server-cert.pem
ssl-key = C:\mysqlCerts\\server-key.pem
require_secure_transport = ON

'require_secure_transport = ON' will disallow any non-SSL connection attempts.

7. Restart mysql server service. Confirm the restart in a mysql client.

  • Type: mysql> status;
  • Confirm the response appears similar to the following,
...
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.7.26-log MySQL Community Server (GPL)
...

If the response appears substantially different, it is possible that…

  • The .pem files have not been properly created
  • my.ini has not been setup properly
  • Also log in in with another mysql client (not workbench) using the command:
    mysql.exe -h <hostname-here> -u <ssl-user-here> -p –ssl-mode=REQUIRED
  • Confirm a connection is made

8. We need to configure EASA machines to connect via TLS/SSL via the following files:

  • <SERVERDATA>\easa-share\admin\config\
    easa.cfg.xml
    excel.cfg.xml
    excelTesting.cfg.xml
    signer.cfg.xml

Update hibernate.connection.username and hibernate.connection.password with the actual username and password for each database.

In step 1. we set them all to ssl-user-here and ssl-password-here

Update hibernate.connection.url with the actual IP address of the database.

Add a new property, hibernate.connection.useSSL and set it to true

Verify hibernate.connection.verifyServerCertificate is set to false as we are using a self-signed certificate (ie. otherwise set this to true to use a trusted authority certificate in the keystore).

Below is an example of a portion of each modified .cfg.xml config file.

<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://192.168.12.56:3306/easa?autoReconnect=true</property>
<property name="hibernate.connection.verifyServerCertificate">false</property>
<property name="hibernate.connection.useSSL">true</property>
<property name="hibernate.connection.username">ssluser</property>
<property name="hibernate.connection.password">sslpassword</property>

9. Restart the EASA Tomcat8 service. Login as admin and run a test query for each database from: EASA > Configure > Databases > Configure Databases > Query tester