MySql SSL configuration - Client section

Question

I'm trying to secure my work with a SSL connection between some applications and my dabtabases.

In a first time, i search how to create certificates, and i do something like this :

Creation of the folder :

[...]$ mkdir certificats && cd certificats

CA :

[...]$ openssl genrsa 2048 > ca-key.pem

[...]$ openssl req -new -x509 -nodes -days 500 -key ca-key.pem > ca-cert.pem

Server :

[...]$ openssl req -newkey rsa:2048 -days 500 -nodes 
       -keyout server-key.pem > server-req.pem

[...]$ openssl x509 -req -in server-req.pem -days 500
       -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Client :

[...]$ openssl req -newkey rsa:2048 -days 500 -nodes 
       -keyout client-key.pem > client-req.pem

[...]$ openssl x509 -req -in client-req.pem -days 500 -CA ca-cert.pem 
       -CAkey ca-key.pem -set_serial 01 > client-cert.pem

I answer to all questions asked like this : same answer to the three files except for "common name" (i saw somewhere than this can cause problem).

Server's configuration :

After that, i did this :

[...]$ vim /etc/my.cnf

to add the following rules (on the [mysqld]) :

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

Now, when i log into a normal account, i can see using

SHOW VARIABLES LIKE '%ssl%';

than ssl is enabled. (see 3 path + 2 'YES')

Connection :

So now, for connection, i have to use :

[...]$ mysql -u user -ppassword -h localhost --ssl-ca=ca-cert.pem 
             --ssl-key=client-key.pem --ssl-cert=client-cert.pem

right ? (the user is on 'REQUIRE SSL' mode).

But it doesnt worked well :

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

If i remove the "ca-cert.pem" from the connection, this work "well". i can connect, and i can saw "Ssl_cipher" with something like "xxx-xxx-AES264-xxx" when i wrote "\s".

So, i tried to add :

[client]

ssl-ca=/etc/ssl/certs_mysql/ca-cert.pem
ssl-cert=/etc/ssl/certs_mysql/client-cert.pem
ssl-key=/etc/ssl/certs_mysql/client-key.pem

But when i add "ssl-x" in the "client" section, mysql stop BUT won't restart using

[...]$ service mysqld restart

Why can't i wrote ssl configuration in the client section ?

I try a connection to my databases using a PHP file, when i used the 3 files, the connection is refused, but if i only used "client-key.pem" and "client-cert.pem", i'm connected.

i saw somewhere than using

[...]$ cat client-cert.pem server-cert.pem > ca-cert.pem

can solved the problem but so the ca-cert used contained 2 certs ?

So the problem, i though, is in ca-cert.pem ? Not recognize as a "real certificate" so the connection si refused ? But why can't i used [client] ?

Thanks in advance for you answers

Answer 1

This is a bit too long to post as a comment, even though it's not a complete answer. But if we get this info, we can get a few steps further. So here goes:

In order to resolve an SSL issue, I'd like to start by taking mysql out of the equation. So start by using only SSL to connect, like this:

openssl s_client -connect localhost:MYSQLPORTNR -cert client.pem -key client-key.pem -CAfile ca-cert.pem 

where MYSQLPORTNR should of course be replaced with the port number that you're running mysql on. Then add the output of this to your post.