MySql connection using SSL

Today we are here to discuss about MySql connection using secure connection SSL (Secure Socket Layer) 

1) Connect on local machine using command line. 
Here is simple MySql client command to access MySql server.

• mysql -u root -p root

2) MySql connection using SSL certificates.

Tasks to perform on server side.

Note: If you don't have openssl then download it and extract after that find "openssl.cnf" file and set it into environment variables.
Variable Name: OPENSSL_CONF

Variable Value: directory location\openssl.cnf

For SSL connection we need to generate certificates.

Generate RSA private key.
openssl genrsa 2048 > "ca-key.pem"

Generate self-signed CA certificate using RSA private key.

openssl req -new -x509 -nodes -days 3600 -key "ca-key.pem" > "ca-cert.pem"

Generate server RSA key.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "server-key.pem" > "server-req.pem"

Generate server certificate.

openssl x509 -req -in "server-req.pem" -days 3600 -CA "ca-cert.pem" -CAkey "ca-key.pem" -set_serial 01 > "server-cert.pem"

Generate client RSA key.
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "client-key.pem" > "client-req.pem"

Generate client certificate.
openssl x509 -req -in "client-req.pem" -days 3600 -CA "ca-cert.pem" -CAkey "ca-key.pem" -set_serial 01 > "client-cert.pem"

Find MySQL's config file (in Windows: my.ini, in Linux: my.cnf) which is present in "C:\ProgramData\MySQL\MySQL Server 8.0".

Note: ProgramData is a hidden folder.

Open my.ini file, find "[mysqld]" section, and add following lines

[mysqld]

ssl-ca=C:\mysqlCerts\ca-cert.pem

ssl-cert=C:\mysqlCerts\\server-cert.pem

ssl-key=C:\mysqlCerts\\server-key.pem

Check certificates are set in MySql and ssl status.

• show variables like '%ssl%';

Note: If you have \s in your path, you will need to replace it with \\s because mysqld will substitue the \s for a whitespace character which will break the path to your key. The extra backslash escapes the original backslash, leaving your path intact.

Restart mysql service
Goto Run and type "services.msc"

Find Mysql and restart

Now try to connect with MySql using ssl certificates.

To require that clients connect using encrypted connections, enable the require_secure_transport system variable. For example, put these lines in the server my.ini file:

[mysqld]

require_secure_transport=ON

Or 

we can run below command for the same purpose.

• SET PERSIST require_secure_transport=ON;

to check require_secure_transport use below command.

• show variables like '%require_secure_transport%';

NOTE: After enabling require_secure_transport it is not possible to connect ussing --ssl-mode=DISABLED, It does not matter user is SSL or None SSL.

• mysql -u ssluser -p --ssl-mode=DISABLED

Check MySql status type status or \s in mysql command line.

If you see.   SSL: Not in use 
that means need to check configurations and enable SSL again, in this case client is connect through SSL.

After doing that we will create a user that require SSL connection.

• create user 'ssluser'@'%' identified by 'ssluser';

• grant all privileges on `my-database-1`.* to 'ssluser'@'%' with grant option;

• FLUSH PRIVILEGES;
• ALTER USER 'ssluser'@'%' REQUIRE SSL;
• FLUSH PRIVILEGES;

1) Use below command to connection using SSL

• mysql --ssl-mode=REQUIRED --ssl-cert="C:\mysqlCerts\client-cert.pem" --ssl-key="C:\mysqlCerts\client-key.pem" -u suraj -p

We need client certificate and client key pem file to connect.

2) There is another way to connect using CA certificate, the only condition is CA certificate must be signed by authority not self signed.

here is command to connect using CA certificate.

• mysql --ssl-ca="CA.pem" --ssl-mode=VERIFY_IDENTITY -u suraj -p

3) SSL connection using workbench.
open workbench -> edit connection -> goto SSL tab.-> enter certificates and click on test connection.

SSL Key: "C:\mysqlCerts\client-key.pem"

SSL Cert: "C:\mysqlCerts\client-cert.pem"

SSL CA: "C:\mysqlCerts\ca-cert.pem"

Here are some ssl modes.

In the absence of an --ssl-mode option, clients attempt to connect using encryption, falling back to an unencrypted connection if an encrypted connection cannot be established. 

1. With --ssl-mode=PREFFERED, if an encrypted connection cannot be established the proceeds with unencrypted connection also known as default ssl mode.
2. With --ssl-mode=REQUIRED, clients require an encrypted connection and fail if one cannot be established.
3. With --ssl-mode=DISABLED, clients use an unencrypted connection.
4. With --ssl-mode=VERIFY_CA or --ssl-mode=VERIFY_IDENTITY, clients require an encrypted connection, and also perform verification against the server CA certificate and (with VERIFY_IDENTITY) against the server host name in its certificate.