user2212190 user2212190 - 4 months ago 22
MySQL Question

MySQL 5.5 and SSL on Ubuntu 14.04

I'm trying to set up a mysql server 5.5.38-0ubuntu0.14.04.1 with SSL support on an Ubuntu 14.04 Linux (64bit) with kernel 3.13.0-32-generic.

I allowed remote access to mysql and changed the /etc/mysql/my.cnf in order to support ssl...

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


I generated the certificates...

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem


I have different names for the Common Name value used for the server and client certificates/keys. Same names seem to be a problem in some cases.

I correctly set the permisions of all *.pem files (mysql:adm) which are in /etc/mysql/. After restaring mysql, I can login as root and see that ssl is now supported:

mysql> show variables like '%ssl%';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mysql/server-key.pem |
+---------------+-----------------------------------+
7 rows in set (0.00 sec)


So.. I generated a test-user to test a ssl connection:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'password' REQUIRE X509;
mysql> flush privileges;


However, when I try to connect...

# mysql -u ssluser -p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
Enter password:
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation


However, my certificates seem to be valid:

# openssl verify -CAfile ca-cert.pem server-cert.pem client cert.pem
server-cert.pem: OK
client-cert.pem: OK


I tried to solve this problem since several hours, now I do not have any new ideas..
Help is greatly appreciated!

Answer

Solved: Works once the keys are generated with SHA1

# Generate a CA key and certificate with SHA1 digest
openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# Create server key and certficate with SHA1 digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -sha1 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl rsa -in server-key.pem -out server-key.pem

# Create client key and certificate with SHA digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem