Configured MySQL for SSL , but SSL is still DISABLED..!

Question

I configured SSL for MySQL using the following script.

#!/bin/bash
#
mkdir -p /root/abc/ssl_certs
cd /root/abc/ssl_certs
#
echo "--> 1. Create CA cert, private key"
openssl genrsa 2048 > ca-key.pem

echo "--> 2. Create CA cert, certificate"
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

echo "--> 3. Create Server certificate, key"
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

echo "--> 4. Create Server certificate, cert"
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
echo ""
echo 
echo ""

echo "--> 5. Create client certificate, key. Use DIFFERENT common name then server!!!!"
echo ""
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
echo "6. Create client certificate, cert"
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
exit 0

The following files were created:

ca-key.pem             ca-cert.pem
server-req.pem         server-key.pem       server-cert.pem
client-req.pem         client-key.pem       client-cert.pem

Then I combined server-cert.pem and client-cert.pem into ca.pem (I read in a post to do so..)

I created a ssl user in MySQL:

GRANT ALL ON  *.* to sslsuer@hostname IDENTIFIED BY 'pwd' REQUIRE SSL;

Next I added the following in my.cnf

[mysqld]
ssl-ca          = /root/abc/ssl_certs/ca.pem
ssl-cert        = /root/abc/ssl_certs/server-cert.pem
ssl-key         = /root/abc/ssl_certs/server-key.pem

After restarting the server,I connected to mysql but SSL was still not in use :(

mysql -u ssluser -p

SSL:                    Not in use

Even the have_ssl parameter was still showing disabled.. :(

mysql> show variables like '%ssl%';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| have_openssl  | DISABLED                                    |
| have_ssl      | DISABLED                                    |
| ssl_ca        | /root/abc/ssl_certs/ca.pem          |
| ssl_capath    |                                             |
| ssl_cert      | /root/abc/ssl_certs/server-cert.pem |
| ssl_cipher    |                                             |
| ssl_key       | /root/abc/ssl_certs/server-key.pem  |
+---------------+---------------------------------------------+

Have I missed any step, or whats wrong..

Answers with missed steps in detail will be highly appreciated..

Solution 1

See solution here: https://askubuntu.com/questions/194074/enabling-ssl-in-mysql

You need convert certificates to the old format:

openssl rsa -in client-key.pem -out client-key.pem
openssl rsa -in server-key.pem -out server-key.pem

Solution 2

Try checking this:

• Enable warning output logging of MySQL, and read those actual log entries.
• Check filesystem permissions to let the user as which MySQL runs (mysql?) read the files. In /root I don't think it has by default.
• Is Apparmor or SELinux preventing MySQL to read the certs and keys?
• You may be hit by a nasty bug of a recent OpenSSL vs. MySQL incompatibility. I've been unable to generate keys on Ubuntu 12.04 which MySQL could read, whereas the ones I generate on Debian Squeeze work fine.

Solution 3

On Ubuntu, you may check if apparmor blocks access to your cert files, see the manual.

Then you should check file permissions/ownership and add necessary rights to /etc/apparmor.d/usr.sbin.mysqld. See this thread.

Solution 4

I had this problem, and it helped changing ownership on the .pem files:

chown mysql.mysql /var/lib/mysql/*.pem  (or in your case /root/abc/ssl_certs/*.pem)

(From Craig Wright - https://askubuntu.com/questions/194074/enabling-ssl-in-mysql)

Solution 5

Pavel Bernshtam suggested a perfect solution

openssl rsa -in client-key.pem -out client-key.pem
openssl rsa -in server-key.pem -out server-key.pem

but I needed also to assign the owner to the certs folder, otherwise mysql can't read the key file

chown -R mysql:mysql /path/to/certs

View more solutions

Comments

[gertvdijk]

What version of OpenSSL are you using? And please enable error+warning logging in MySQL. If you see "unable to read private key" error message you're probably hit by this bug in some way. Creating new keys+certs on an older system, using them on a recent MySQL solved the issue for me.

[Sunrays]

@gertvdijk I have used openssl-1.0.1c. I am checking logs for the mentioned error.

[Valentin Bajrami]

First of all. Check if mysql supports ssl. mysql --ssl --help Also don't forget to restart mysql after making those modifications to my.cnf

[johannes]

try mysql --ssl -u ssluser -p to force SSL on the client. afterthat works one can trto identify why REQuIRE SSL doesn't.

[Sunrays]

@gertvdijk There was no entry of "unable to read private key" or like error message.

[Sunrays]

@johannes same ssl not in use.. One more thing I am not able to connect with password.. without password it is atleast connecting but ssl is still not in use.

[bobpaul]

His paths start with a /, which means they are full paths, not relative paths. /root/ is the home folder for the root user on a unix system.

[anteatersa]

This solved the issue for me.