Encrypting Network Traffic with MySQL

Navigation: Additional Tips and Resources > Database Tips > MySQL >

The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to respond to several prompts by the openssl commands. For testing, you can press Enter to all prompts. For production use, you should provide nonempty responses.

1.	Download OpenSSL for Windows. An overview of available packages can be seen here: http://www.slproweb.com/products/Win32OpenSSL.html

2.	Choose of the following packages, depending on your architecture (32-bit or 64-bit):

* Win32 OpenSSL v0.9.8o Light, available at: http://www.slproweb.com/download/Win32OpenSSL_Light-0_9_8o.exe

* Win64 OpenSSL v0.9.8o Light, available at: http://www.slproweb.com/download/Win64OpenSSL_Light-0_9_8o.exe

if a message occurs during setup indicating '...critical component is missing: Microsoft Visual C++ 2008 Redistributables', cancel the setup and download one of the following packages as well, again depending on your architecture (32-bit or 64-bit):

* Visual C++ 2008 Redistributables (x86), available at: http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF&displaylang=en

* Visual C++ 2008 Redistributables (x64), available at: http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6&displaylang=en

After installing the additional package, restart the OpenSSL setup.

3.	During installation, leave the default C:\OpenSSL as the install path, and also leave the default option 'Copy OpenSSL DLL files to the Windows system directory' selected.

4.	When the installation has finished, add C:\OpenSSL\bin to the Windows System Path variable of your server:

a)	On the Windows desktop, right-click the My Computer icon, and select Properties.

b)	Next select the Advanced System Settings from the System Properties menu that appears, and click the Environment Variables button.

c)	Under System Variables, select Path, and then click the Edit button. The Edit System Variable dialogue should appear.

d)	Add ';C:\OpenSSL\bin' to the end (notice the semicolon).

e)	Press OK to save your changes.

f)	Check that OpenSSL was correctly integrated into the Path variable by opening a new command console (Start>Run>cmd.exe) and verifying that OpenSSL is available:

Microsoft Windows [Version ...]

C:\Windows\system32>cd \

C:\>openssl

OpenSSL> exit <<< If you see the OpenSSL prompt, installation was successful.

C:\>

Depending on your version of Windows, the preceding instructions might be slightly different.

5.	After OpenSSL has been installed, use the following instructions to create the necessary certificates:

* Open a Windows Command prompt ('Start' -> 'Run' -> type 'CMD.EXE')

#Create clean environment

C:\WINDOWS\System32\>md C:\newcerts

C:\WINDOWS\System32\>cd C:\newcerts

# Create CA certificate

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

C:\newcerts\>openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

# Create server certificate

C:\newcerts\>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

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

# Create client certificate

C:\newcerts\>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

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

Testing SSL connections

1.	To test SSL connections, stop the SQL server and edit the MY.INI to add the following lines in the correct sections of the file:

[client]

ssl-ca="C:/newcerts/ca-cert.pem"

ssl-cert="C:/newcerts/client-cert.pem"

ssl-key="C:/newcerts/client-key.pem"

[mysqld]

ssl-ca="C:/newcerts/ca-cert.pem"

ssl-cert="C:/newcerts/server-cert.pem"

ssl-key="C:/newcerts/server-key.pem"

2.	restart the MySQL server and login to the SQL console as ROOT

3.	type the following line at the SQL prompt:

grant SELECT, INSERT on *.* to root@client_domain_or_IP_address IDENTIFIED BY "password" REQUIRE SSL;

("client_domain_or_IP_address" will be replaced with the IP address of the system you are attempting to attach from and "password" will be replaced with the password for that account)

4.	Repeat this process for any additional accounts you are going to require SSL from (eventsentry_svc and eventsentry_web for example).

5.	You can then add your SSL key, SSL Certificate, SSL Certificate Authority and SSL CA Path to your DSN.