MySQL SSL Setup Debugging

For my own sanity, I’m writing down the steps I took to get SSL working between 2 servers today. I ran into some frustration following the step-by-step instructions on both mysql.com as well as on 1 or two other websites on the web. To add to the fun, it seems sometimes I would get different, no descriptive, errors such as:

• ERROR 2026 (HY000): SSL connection error (no additional details)
• ERROR 2026 (HY000): SSL connection error: Unable to get certificate
• ERROR 2026 (HY000): SSL connection error: protocol version mismatch

So here is what I did, step by step, with multiple test points…

On The MYSQL SERVER  that I wanted to connect to, I logged in and created a self signing Certificate Authority (CA) and then used that to sign a key for my mysql server. The commands looked like this:

openssl genrsa 1024 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

openssl req -newkey rsa:1024 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

You’ll note that I used 1024 for my key instead of 2048. While 2048 would be more secure, and is what is provided in the example here, it causes my version of OpenSSL to generate in a slightly weird format that the mysql client doesn’t seem to like in some cases. A number of example online said there was a simple work around involving adding “RSA” to the header and footer of the keys, but no matter what I seemed to do, this never worked for me… so I just lowered the key strength to 1024 to get it to generate it the old way. There was also a suggestion to merge the server and client certs into one “CA”… but I didn’t like that either… in fact… I didn’t want a Client cert if if I could avoid it… as I wasn’t authenticating against it… I just wanted the SSL tunnel.

Once I had my certs created, I added these 3 lines to my.cnf. In theory I could have put them in the startup command for the server, but this was the better long term solution:

ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Note: when I created all my certs above, I was in the folder /etc/mysql/ (in case you didn’t guess that already).

At this point, I restarted the mysql server and ran the following query on it:

show variables like “%ssl%”;

The server came back and would say that it “have_ssl” and “have_openssl” along with the paths to the certs I just loaded in. This was a good sign.

I think modified one of my users to only allow connections using SSL:

grant usage on *.* to example_user require ssl;

I than began testing from client devices. The first stop was the mysql workbench for windows. I loaded it up and tried to log in, and on the first try it failed (this I took to be a good sign, as I hadn’t told it anything about the SSL). I then grabbed the ca-cert.pem file I created on my server, and brought it over to my client box. I then told mysql workbench to use this file for the CA. You’ll note I DID NOT specify a client key or certificate for this. I then tried to connect, and wha-la, it let me in.

To confirm I was in fact transmitting over SSL at this point, I ran this query:

show status like ‘Ssl_cipher’;

and then insured it came back with a value (if it’s blank, your NOT connected over SSL)

I then moved on to my second client, which a LAMP setup. I tried connecting using phpmyadmin, and of course, it failed again as I had told it nothing about the certificate authority. So again, I copied the file over, this time adding the following to my my.cnf:

[client]
ssl-ca=/path/to/ca-cert.pem

I first tested with my mysql client on the machine and it worked great. I did run into a little trouble with phpmyadmin, but I found the connect lines in my config file and basically added this one line before the connection was made, but after the init:

mysqli_ssl_set ($link , NULL , NULL , “/path/to/ca-cert.pem” ,NULL , NULL );

I think this may not have been needed if my version of phpmyadmin had been newer… but not sure. (I was in 3.4.5 at the time, the documentation I was reading would have been for 4.2.7).

Regardless, when I signed off for the night, all my mysql connections that I cared about between my older “clients” and my new mysql server were running over SSL. Tomorrow, I hope to setup some replication between servers over SSL. This may require that I create a client certificate, but until then, I’m happy to not have needed one.