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..

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](http://forums.mysql.com/read.php?11,400856,401127#msg-401127) in some way. Creating new keys+certs on an older system, using them on a recent MySQL solved the issue for me. — gertvdijk, Nov 23 '12 at 09:26
@gertvdijk I have used openssl-1.0.1c. I am checking logs for the mentioned error. — Sunrays, Nov 23 '12 at 09:30
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 — Valentin Bajrami, Nov 23 '12 at 09:30
@val0x00ff yes MySQL supports ssl if have_ssl value is disabled. — Sunrays, Nov 23 '12 at 09:31
try `mysql --ssl -u ssluser -p` to force SSL on the client. afterthat works one can trto identify why REQuIRE SSL doesn't. — johannes, Nov 23 '12 at 09:37
@gertvdijk There was no entry of "unable to read private key" or like error message. — Sunrays, Nov 23 '12 at 09:42
@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. — Sunrays, Nov 23 '12 at 09:49

score 7 · Answer 1 · edited Apr 13 '17 at 12:22

7

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

edited Apr 13 '17 at 12:22

Community

1

answered Nov 01 '15 at 11:52

Pavel Bernshtam

181
1
5

score 3 · Answer 2 · answered Nov 23 '12 at 09:36

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.

score 2 · Answer 3 · answered Jul 08 '21 at 12:29

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

score 2 · Answer 4 · answered Nov 08 '16 at 14:20

2

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.

answered Nov 08 '16 at 14:20

Franc Drobnič

141
5

score 2 · Answer 5 · answered Nov 12 '18 at 21:48

2

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)

answered Nov 12 '18 at 21:48

btb

21
1

This solved the issue for me. – anteatersa Jul 12 '19 at 09:04

score 0 · Answer 6 · answered Dec 03 '21 at 16:56

0

Faced same issue in Ubuntu 20 and Mariadb 10.2.40, Deployed certs but "have_ssl" was still disabled and solution was to change ownership of all certs to mysql user, group and it solved the issue.

answered Dec 03 '21 at 16:56

xs2rashid

204
1
7

score 0 · Answer 7 · answered Dec 22 '21 at 14:15

What Masegaloeh explained worked for me as well. I added this at the end of /etc/mysql/my.cnf

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

[client]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

score 0 · Answer 8 · edited Jun 04 '15 at 07:50

0

Add the "ssl" in the [mysqld] section in my.cnf file like

ssl
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

and then restart mysql and check again. It will work.

edited Jun 04 '15 at 07:50

masegaloeh

18,236
10
57
106

answered Jun 04 '15 at 07:36

Pratik

11

score 0 · Answer 9 · answered Sep 30 '16 at 03:47

I believe this issue is due to the fact that you have not specified the file paths for your CA cert, server key and server cert correctly. It must be in the following format...

ssl-ca=path/to/repo/mysql-directory/path/to/pem/cacert.pem
ssl-cert=path/to/repo/mysql-directory/path/to/pem/server-cert.pem
ssl-key=path/to/repo/mysql-directory/path/to/pem/server-key.pem

i.e. you need the FULL FILE PATH of the certs and keys within your mysql installation folder not a relative '/root/' path.

Here is an example from my installation:

mysql> show variables like '%ssl%';
+---------------+----------------------------------------------------------+
| Variable_name | Value                                                    |
+---------------+----------------------------------------------------------+
| have_openssl  | YES                                                      |
| have_ssl      | YES                                                      |
| ssl_ca        | C:/wamp64/bin/mysql/mysql5.7.14/etc/certs/ca.pem         |
| ssl_capath    |                                                          |
| ssl_cert      | C:/wamp64/bin/mysql/mysql5.7.14/etc/certs/server-cert.pem|
| ssl_cipher    |                                                          |
| ssl_key       | C:/wamp64/bin/mysql/mysql5.7.14/etc/certs/server-key.pem |
+---------------+----------------------------------------------------------+

Refer to the following link for addition information. https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-ssl.html

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. — bobpaul, Apr 04 '18 at 22:02

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

9 Answers9