Enable SSL Connections for MySQL

Enable SSL connections for MySQL server and configure Gateway to communicate using secure connections. This is ideal where MySQL is offbox and located in a different network, and environments where extra security or encryption is required.
2
To enable SSL connections for your external MySQL server, you'll need to append three properties to the jdbcURL connection string in the database section of the values.yaml file for Helm Chart deployments, as shown in the following example:
database: enabled: true create: false jdbcURL: jdbc:mysql://myprimaryserver:3306,mysecondaryserver:3306/ssg?useSSL=true&requireSSL=true&verifyServerCertificate=false username: myuser password: mypassword name: ssg
Note that the following SSL-related properties were added to the jdbcURL string:
  • useSSL=true
  • requireSSL=true
  • verifyServerCertificate=false
Enforcing TLS Usage For Your Database Connection
To ensure that a specific version of TLS is used for your secured MySQL connection, append the following property to jdbcURL:
  • enabledTLSProtocols=TLSv1.x
For example, if we wanted to enforce the use of TLS v1.2, the following property would be appended to the jdbcURL connection string:
jdbcURL: jdbc:mysql://myprimaryserver:3306,mysecondaryserver:3306/ssg?useSSL=true&requireSSL=true&verifyServerCertificate=false&enabledTLSProtocols=TLSv1.2
To learn more about jdbcURL configurations, check out the following MySQL resources:
Set Up MySQL SSL with No Server Certificate Validation
In this configuration, the following warning messages might appear if you are using MySQL higher than 5.5.45+, 5.6.26+, and 5.7.6+ 
Warning!
Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
1) Run the following command to confirm that SSL is not enabled on MySQL:
mysql> show global variables like '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ... | ... |
2) Run the following commands to create the Certificate Authority (CA) keys:
mkdir /etc/mysql cd /etc/mysql openssl genrsa 2048 > ca-key.pem openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
3) Run the following commands to create the server SSL key and certificate:
Note:
If you are generating certificates for use with MySQL, ensure that you convert the RSA keys to traditional PKCS #1 PEM format and that certificates are using SHA1 digests.
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem openssl rsa -in server-key.pem -out server-key.pem
4) Update ownership of certificate folder, files, and update permissions:
chown -R mysql:mysql /etc/mysql
5) Update MySQL's config file /etc/my.cnf. Include this under the [mysqld] section:
[mysqld] ssl-ca = /etc/mysql/ca-cert.pem ssl-cert = /etc/mysql/server-cert.pem ssl-key = /etc/mysql/server-key.pem
6) Locate and comment out in /etc/my.cnf :
[mysqld] #skip_ssl
7) Enable the
require_secure_transport
variable to make it mandatory for clients to connect using encrypted connections.
[mysqld] # Require clients to connect either using SSL # or through a local socket file require_secure_transport = ON
8) Restart mysql service/server.
service mysqld restart
9) Log in to MySQL and validate that ssl is turned on for MySQL:
mysql -u root -p --ssl-mode=required
mysql> show global variables like '%ssl%'; +---------------+----------------------------+ | Variable_name | Value | +---------------+----------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/mysql/server-key.pem | +---------------+----------------------------+
10) And check the status:
mysql> status -------------- mysql Ver 8.0.18-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial) Connection id: 201 Current database: Current user: [email protected] SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.18-commercial MySQL Enterprise Server - Commercial Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 13 min 18 sec
If SSL is not enabled, look in
/var/log/mysqld.log
for clues as to whether MySQL had problems loading the certificates.
11) Update the Gateway database user to require SSL:
The following commands force all connections to use SSL regardless of what is set in the Gateway configuration. It must match what is set here (whether SSL is enabled or disabled for the Gateway db user).
mysql> UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = 'gateway'; mysql> FLUSH PRIVILEGES;
12) Verify:
SELECT user,ssl_type from mysql.user; mysql> SELECT user,ssl_type from mysql.user; +---------------+----------+ | user | ssl_type | +---------------+----------+ | root | | | mysql.session | | | mysql.sys | | | gateway | ANY |
13) Run the following command to verify that MySQL accepts only SSL connections. Ensure that the MySQL client does not connect to the MySQL server.
mysql -u gateway -p --ssl-mode=DISABLED
14) Add the following to the Gateway's node.properties. This will enable Hibernate connections to use SSL.
l7.mysql.url.parameters.extra = &useSSL=true&requireSSL=true&verifyServerCertificate=false
The set up might result in warnings in the SSG log.
Thu Aug 29 10:47:22 PDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Thu Aug 29 10:47:22 PDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Thu Aug 29 10:47:22 PDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
See the next section to enable Server Certificate Validation. Once enabled, the warning messages above do not appear anymore.
Set Up MySQL SSL with Server Certificate Validation
Certificates Signed by Trusted CA:
No additional configurations are required with respect to certificates made regarding server authentication. Java verifies the server certificate using its default trusted CA certificates, usually from $JAVA_HOME/lib/security/cacerts.
Using Self-signed Certificates:
To verify the server certificate, the client needs to be able to read the certificate that signed it--the server certificate that signed itself or the self-signed CA certificate.
1) Import certificate into a custom Java truststore file and configure the client driver accordingly, follow these steps:
2) From the node that contains the ca-cert.pem, create a truststore and import the CA Cert from the previous step that was used to self-sign the MySQL server certificate.
/opt/SecureSpan/JDK/bin/keytool -importcert -alias MySQLCACert -file ca-cert.pem -keystore truststore.jks -storepass <mypassword>
3) Run the following command to verify the CA Cert is in the truststore:
/opt/SecureSpan/JDK/bin/keytool -list -v -keystore truststore.jks
4) Copy the truststore.jks to all of the nodes that will connect to the MySQL /etc/mysqlclient.
5) Change the permissions of JKS file:
chmod 444 /etc/mysqlclient/truststore.jks
6) Add the following lines to the
node.properties
file:
l7.mysql.url.parameters.extra =&useSSL=
true
&requireSSL=
true
&verifyServerCertificate=
true
&clientCertificateKeyStoreUrl=file:/etc/mysqlclient/truststore.jks&clientCertificateKeyStorePassword=<mypassword>&clientCertificateKeyStoreType=JKS&trustCertificateKeyStoreUrl=file:/etc/mysqlclient/truststore.jks&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=<mypassword>
The server certificate is now validated and the warning messages are no longer visible.
Set Up Replication to Use Encrypted Connections
Ensure that you complete the steps mentioned here: Configure Cluster Database Replication.
Both master server and slave server must support encrypted network connections to be able to use encrypted connections for the transfer of the binary log that is required during replication. If either of the servers does not support encrypted connections, replication through an encrypted connection is not possible.
Setting up encrypted connections for replication is similar to the process done for client/server connections. Create or obtain a suitable security certificate that you can use on the master, and a similar certificate from the same certificate authority on each slave along with suitable key files.
Follow these steps on both the MySQL nodes as Gateway uses MASTER-MASTER replication:
1) Run the following commands to create the client SSL key and certificate:
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem openssl rsa -in client-key.pem -out client-key.pem
2) Update MySQL's config file /etc/my.cnf to include the client certificates under the [client] section:
[client] ssl-ca = /etc/mysql/ca-cert.pem ssl-cert = /etc/mysql/client-cert.pem ssl-key = /etc/mysql/client-key.pem
3) Restart mysql service/server.
service mysqld restart
4) Update the Gateway replication user to require SSL:
The following commands force all connections to use SSL regardless of what is set in the replication configuration. It must match what is set here (whether SSL is enabled or disabled for the replication user).
mysql> UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = 'repluser'; mysql> FLUSH PRIVILEGES;
5) Restart slave using the following commands:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST=<hostname>,MASTER_PORT=<portnumber>,MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_SSL=1; mysql> START SLAVE;
6) Check status using the following command:
mysql>SHOW SLAVE STATUS \G
7) Restart replication if you see errors connecting to Master.
Connecting to a MySQL Database that Requires TLS v1.2
Certain SSL/TLS-enabled MySQL databases may be restricted to only allow TLS v.1.2 when clients such as the Gateway are connecting to it as part of security requirements. The database driver used by the Gateway may make some assumptions of the external database it’s connecting to and limit the TLS version to TLS v1.1. This is especially the case for MySQL databases that did not come with the Gateway (e.g., cloud-based MySQL databases such as AWS Aurora).
Because of the discrepancy in TLS version acceptance between the Gateway client and MySQL database, this may result in a failed database connection.
There are two primary places within the Gateway in which TLS versions could be configured and enforced, depending on the connection type: the node.properties file and the Manage JDBC Connection tool in Policy Manager.
Enforcing TLS v1.2 in node.properties
The node.properties file is located in
/opt/SecureSpan/Gateway/node/default/etc/conf/
When Gateway starts up, the node.properties is partly used to start up a connection to the MySQL database. To ensure that any database connection utilizes TLS v1.2, add the following line to the node.properties file:
l7.mysql.url.parameters.extra=&useSSL=true&verifyServerCertificate=false&enabledTLSProtocols=TLSv1.2
Enforcing TLS v1.2 in Manage JDBC Connections (Policy Manager)
If your policy or web service currently uses the Perform JDBC Query assertion and the Gateway is not providing TLS v1.2 during the attempted connection to the MySQL database that requires TLS v1.2, ensure that the JDBC URL is appended with the following property and value:
&enabledTLSProtocols=TLSv1.2
See Manage JDBC Connections to learn more.