Establish SSL Connection

SSL connection prerequisites

1. Open command prompt and connect to mysql server by running below command and run \s to see the SSL connection that is established. If SSL is Not in Use then the connection established is unencrypted. mysql -u{user_name} -p{password}
mysql> \s

2. To establish the secure connection, run the below command with either "PREFERRED" or "REQUIRED" ssl mode. mysql -u{user_name} -p{password} --ssl-mode={PREFERRED | REQUIRED}
mysql> \s

3. Generating SSL/TLS Certificates and Keys
a. To enable SSL connections to MySQL, you first need to generate the appropriate certificate and key files. MySQL versions 5.7 and above provide a utility called mysql_ssl_rsa_setup that helps simplify this process.
mysql-server$ sudo mysql_ssl_rsa_setup --uid=mysql --verbose
The MySQL process must be able to read the generated files, so use the --uid option to declare mysql as the system user that should own the generated files.
b. The above command will produce the output that is similar to the following attached screenshot.
c. These new files will be stored in MySQL’s data directory, located by default at /var/lib/mysql. Check the generated files by typing:
mysql-server$ sudo find /var/lib/mysql -name '*.pem' -ls
d. These files are the key and certificate pairs for the certificate authority (starting with “ca”), the MySQL server process (starting with “server”), and for MySQL clients (starting with “client”). Additionally, the private_key.pem and public_key.pem files are used by MySQL to securely transfer passwords when not using SSL.

4. Enabling SSL connection on MySQL Server:
a. Modern versions of MySQL look for the appropriate certificate files within the MySQL data directory whenever the server starts. Because of this, you won’t need to modify MySQL’s configuration to enable SSL. Instead, enable SSL by restarting the MySQL service:
sudo systemctl restart mysql
b. After restarting, open up a new MySQL session using the same command as before. The MySQL client will automatically attempt to connect using SSL if it is supported by the server:
mysql -u root -p -h 127.0.0.1
c. Let’s take another look at the same information we requested last time. Check the values of the SSL-related variables:
mysql> SHOW VARIABLES LIKE '%ssl%
d. The have_openssl and have_ssl variables now read YES instead of DISABLED. Furthermore, the ssl_ca, ssl_cert, and ssl_key variables have been populated with the names of the respective files that we just generated.
e. Check the status of your current connection to confirm this:
mysql> \s
f. MySQL server is configured to accept SSL connections from clients. However, it will still allow unencrypted connections if requested by the client. We can change this by turning on the require_secure_transport option.
mysql-server$ sudo nano /etc/mysql/my.cnf g. Start by creating a [mysqld] section to target the MySQL server process. Under that section header, set require_secure_transport to ON, which will force MySQL to only allow secure connections. In order to allow MySQL to listen for external connections, you must configure it to listen for connections on an external IP address. To do this, you can add the bind-address setting and point it to 0.0.0.0, a wildcard IP address that represents all IP addresses. Essentially, this will force MySQL to listen for connections on every interface:

How to establish SSL connection in AgileApps platform?

1. Importing the CA certificate to client JVM cacerts. To perform this action open the command prompt. Go to cacerts path of Java and run the below command.
keytool -importcert -alias MySQLCACert -file "/var/lib/mysql/ca.pem" -keystore cacerts
2. Update the below two properties files with sslMode={SSL_MODE} in url key.
a. Open the com.softwareag.catalina.resource.pid-agileappsRN.properties in below location:
/opt/softwareag/profiles/IS_default/configuration/com.softwareag.platform.config.propsloader/com.softwareag.catalina.resource.pid-agileappsRN.properties

• Modify the url key by appending ?sslMode={SSL_MODE}
  

b. Open the com.softwareag.catalina.resource.pid-agileappsRN.properties in below location:
/opt/softwareag/profiles/IS_default/configuration/com.softwareag.platform.config.propsloader/com.softwareag.catalina.resource.pid-agileappsQuartz.properties

• Modify the url key by appending ?sslMode={SSL_MODE}
  

Note: Don't leave the sslmode parameter value empty, use PREFERRED/DISABLED mode if unsure of secure connection.