1. Create user in mysql that will connect via ssl via the following commands,
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.
... 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…
8. We need to configure EASA machines to connect via TLS/SSL via the following files:
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