Give me security; WordPress&MySQL!

This is a follow-up on a previous blog post of mine. I don’t like to leave the security of the solution hanging as we are exposing the MySQL database to the internet up in the cloud. There exist a possibility to use Azure internal virtual network, though for that your web site must use a more expensive tier. So therefore I will address how to set up a secure connection from the WordPress frontend to the MySQL backend.

First you will need to download OpenSSL (I’m using the Windows lite version found here).

Install this to an appropriate folder and add the path to the environment setting.

If on Win10 just hit the windows button on your keyboard and enter the search term: System. Go to Advanced system settings, and enter the path to where the OpenSSL software installed.

Windows_Environment Windows_OpenSSLPathWindows_System

Then open an cmd window and type in:

(change out the file path to where you installed OpenSSL)
Reboot

Create Certificates

Now we can start using OpenSSL to create the certificates we need. First create a folder to hold the files in, for me I used: c:\temp\MySQLCerts

Open the command prompt again (CMD) and go to the folder.
Then run the following commands:

Remember to fill inn some of the prompts or use . (dot) if you want to leave it empty. This will be the CA certificate. I’m using 9999 days, so the certificate will never expire in the lifetime of the database.

CA_cert

Next we will create the server certificate.
Enter the commands:

Remember here to also type in something, and make sure the Common Name is not the same used for generating the CA certificate or an error will occur.

ServerCert

Next run:

Verify the certificates with the command:

Both certificates should come out with a OK in the command window printout.

MySQL Docker VM

Get your favorite SSH client, I used MobaXterm for my session. Log into your MySQL VM in Azure by either inputting the IP or DNS of the server. This can be found going to the Virtual Machine tab in the Azure portal and look at the settings of the VM.

SSH_VMLogin to the server and copy the 3 files ca.pem, server-cert.pem and server-key.pem from where you created them into the VM. Using MobaXterm this is easy as you can just use the upload functionality in the client.

SSH_FilesVM

Next mount the docker container running MySQL in the VM. Find the name of the container by running the command: docker ps to find the name of your container. Mine was named compose_mysql_1

ContainerName

Then use (mounts the Container):

You will now be inside the container, from here run:

You are now back in the VM, run these commands from there to copy the certificate files into the container:

Enter the container again with the docker exec command from above.
Then run the command below to set the correct security rights for the files:

We will now need an editor to change the config file of MySQL; so do this inside of the container:

When finished run:

Inside the editor navigate to the [mysqld] section, place the cursor at the bottom of this section where there is a free space and press the i-button to enter insert mode. Once in insert mode enter the text:

MySQLSettings

Press Esc to end edit mode, and then enter :wq (including : symbol) to save the file.

Then run the following commands from the commandline:

Once out of the container restart it with the following command:

SSL should now be activated. To check use MySQL Workbench and add the CA certificate to the Blog DB connection settings.

MySQLWB_Settings

Run the command:

CheckMySQL_Cert

WordPress

Now you will need to add a line to a file in the WordPress site. Use a FTP client to log on to the site and open the wp-config.php file. Find the section with the text: define(‘DB_Collate’,’’);

Below this line enter the following text:

WP_ConfigChanges

Save the file, and do a restart of your site. Your connection the database is now encrypted.

I want to thanks this blogger for showing me the correct Linux commands to run.

Happy tinkering!