How to Enable SSL for MySQL in Windows

MySQL allows you to make connections via SSL, causing your connections and data transfer to be secure and encrypted. If you connect to remote MySQL databases without SSL, then it is possible that your connection is intercepted and data is leaked over the network. So it is a best practice to enable SSL especially when you connect to MySQL databases outside your network. But by default, SSL is supported but disabled on most MySQL servers. So you will need to enable it explicitly. In this article, we will learn how to enable SSL for MySQL in Windows.

How to Enable SSL for MySQL in Windows

Here are the steps to enable SSL for MySQL in Windows.

1. Check for MySQL Support

Most MySQL binaries that you download already have built-in SSL support. To verify this, go to MySQL installation folder. Replace 5.7 below with the version number of your MySQL installation.

cd c:\Program Files\MySQL\MySQL Server 5.7\bin

Log into MySQL

> mysql -uroot -p

You will be prompted for root password. Enter it to log in. Run the following command to check if SSL is supported or not.

mysql> show global variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ...           | ...      |

If you see DISABLED in value column for have_ssl above it means SSL is supported but disabled. If the value was NO then it would have meant that your MySQL installation does not support SSL.

Once SSL is enabled, it will show YES in value column, for have_ssl variable_name above.

2. Create SSL User

Run the following command to create a new user that connects via SSL connection. Replace ssluser and sslpassword with your database username and password respectively.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'sslpassword' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

Please remember to add ‘REQUIRE SSL’ at the end of GRANT ALL PRIVILEGES statement.

If you already have an existing user for whom you want to enable SSL connection, use the UPDATE statement as shown below.

mysql> UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = 'someUser';
mysql> FLUSH PRIVILEGES;

On the other hand, if you want to disable SSL connection for a given user, use the UPDATE statement as shown below.

mysql> UPDATE mysql.user SET ssl_type = '' WHERE user = 'someUser';
mysql> FLUSH PRIVILEGES;

3. Install OpenSSL

MySQL SSL connections require OpenSSL package for processing certificates. Most Linux systems already have OpenSSl package. You can download it for Windows from here.

4. Create SSL Certificates

Open command line by opening run windows (Alt+R) and entering cmd command in the textbox you see. It will open a command prompt.

Run the following command to create a new folder to store SSL certificates.

mkdir C:\mysqlCerts

Update environment variable to include path to OpenSSL

set OPENSSL_CONF=c:\OpenSSL-Win64\bin\openssl.cfg

Run the following command to create 2 certificate files ca-cert.pem and ca-key.pem

openssl genrsa 2048 > "C:\mysqlCerts\ca-key.pem"

openssl req -new -x509 -nodes -days 3600 -key "C:\mysqlCerts\ca-key.pem" > "C:\mysqlCerts\ca-cert.pem"

Next, run the following commands to create server keys server-cert.pem, server-key.pem and server-req.pem

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

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

Run the following commands to create client certificates.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout "C:\mysqlCerts\client-key.pem" > "C:\mysqlCerts\client-req.pem"

openssl x509 -req -in "C:\mysqlCerts\client-req.pem" -days 3600 -CA "C:\mysqlCerts\ca-cert.pem" -CAkey "C:\mysqlCerts\ca-key.pem" -set_serial 01 > "C:\mysqlCerts\client-cert.pem"

5. Update MySQL Configuration

Now that we have created required certificates, we need to mention their locations in MySQL configuration file. You will find it at C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Open it in a text editor. Under [mysqld] section, add the following lines

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

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

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

We replace \s in above paths with \\s since MySQL will substitute \s with whitespace character. Restart MySQL server.

Run the following command to check if SSL is enabled or not.

mysql> show global variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | YES      |
| have_ssl      | YES      |
| ...           | ...      |

6. Verify SSL Connection

Try to connect to your MySQL server with the following command, which forces SSL connection.

mysql.exe -ussluser -p --ssl-mode=REQUIRED

You will be prompted for password. If you are able to enter it and login correctly, then SSL connection is working properly.

Also, run the following command, to try connecting without SSL. It should now allow you to connect.

mysql.exe -ussluser -p --ssl=0

In this article, we have learnt how to enable SSL connection in MySQL in Windows.

How to Enable SSL for MySQL in Windows