I wanted to enable SSL on the connections to my MySQL server that I installed in a Windows machine, so I googled "use ssl mysql windows".
The first result is this simple and straightforward guide.
From the looks of it, it seems like the guide that I just needed. However:
- After following the steps, it doesn't work.
- After reading more deeply some of the links that he references (such as the bugs he submitted for the client accepting empty –ssl-key flags), some of them are obsolete (because I'm using a version of MySQL which doesn't have these problems).
- After troubleshooting my problems, I found it has some ambiguities, and things that could have been improved, however I cannot suggest edits to it because the author has closed the comments on his blog (or blog entry).
Therefore, I think this kind of guides are better off if placed in a site like serverfault.com, where anyone can propose changes to answers later on.
I'm, then, willing to accept an answer to this question that is just a copy+paste from this blog, so I can later propose changes to it. And so next time I need to set up SSL in my MySQL server I don't find the same quircks and I have a straightforward and complete guide to follow. And hopefully this gets ranked higher later by google. Thanks
Best Answer
Typically, the binaries for MySQL server for Windows that you can download from the MySQL website, have been compiled with SSL support. To double check that this is the case, we can connect to our instance via
mysql -uroot -p
(in Windows,mysql.exe
is located inC:\Program Files\MySQL\MySQL Server 5.7\bin
) and run:Ok, so SSL is available but DISABLED for now (if it had a 'NO' in the value, SSL was not enabled for our binaries, we would need to look for new ones). Our purpose is to configure MySQL first so that it shows a 'YES' in those values.
You can also see if SSL is being used for the current connection by issuing a
status
query:(This will become handy when we enable SSL in the server, but want to still make sure that SSL is being used for a particular user/connection.)
Now that we're inside this mysql console, let's create the user that will use the SSL mode (username is
ssluser
, password issslpassword
):The equivalent can also be done for an existing user, to force him to use SSL (and not allow her to connect if she doesn't), by issuing the following query:
(And to get it back to allow him to connect without SSL would be: )
Now you need to have OpenSSL installed/available on your machine. Many have this already, certainly most Linux anyway. For Windows, you can download it from here.
Now that that’s out of the way, you're basically looking at a 6 step process, which needs to be run from a command line (
cmd
) from the folder where we installed OpenSSL (e.g.C:\OpenSSL-Win64\bin
):mkdir C:\mysqlCerts
set OPENSSL_CONF=c:\OpenSSL-Win64\bin\openssl.cfg
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"
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"
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"
my.ini
, notmy.cnf
as in Linux, and notmy-default.ini
because the latter is a template), which normally lives inC:\ProgramData\MySQL\MySQL Server 5.7\
(note: ProgramData is a hidden folder), to include this under the[mysqld]
section: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.ssl-ca = "C:\mysqlCerts\ca-cert.pem"
ssl-cert = "C:\mysqlCerts\\server-cert.pem"
ssl-key = "C:\mysqlCerts\\server-key.pem"
and restart mysql service/server.
Now let's connect again via our normal user (not the
ssluser
) withmysql -uroot -p
, and check our ssl variables:If you don't have something like the above, something hasn't worked well. Maybe you assigned a passphrase when creating the certificates/keys? If that's the case, MySQL cannot use those without the passphrase. We can remove it then, by issuing this openssl command:
openssl rsa -in "C:\mysqlCerts\server-key.pem" -out "C:\mysqlCerts\server-key-ppless.pem"
Then change
my.ini
again to point to this new-ppless.pem
-suffixed file. Restart mysql again, connect again, and check thathave_ssl
isYES
now. Is it? Double check by showing the status:Now the SSL has something on it! Let's connect with our new
ssluser
then, now with the --ssl-mode flag:If it connects, we're done.
(But to double check, you can try to connect without SSL to see if it rejects your connection, this way:)
Now you should configure your app to use its connector via SSL. For example if you're using the .NET Connector, check the documentation here.