Encrypted MySQL Connections

This week I fought with MySQL trying to get an encrypted connection. Getting the server piece to appear to work was easier than actually getting the connection. Whether the problems didn’t show up until the connection or if the code in myql client app is just broken, I’m not sure, but I did get it to work.

My environment was:

RHEL 6.3
MySQL 5.5.28 from MySQL SRPM, compiled against OpenSSL 1.0.0j

ERROR 2026 (HY000): SSL connection error: ASN: before date in the future

this one is easy, the client checks the certificate date is > the current, so wait a minute and it goes away

I kept getting: ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Make sure that your DN’s are different.
I saw a lot of stuff to use 0.9.8(something) to generate the certificates, and this did work once, but I also had several fails with 0.9.8 as well.
I also got 1.0.0 to work – not 100% sure what I did differently to finally get it working. All of a sudden it went from not working to working.
I did use statically defined subject lines, but not 100% sure that fixed it. I think I had the first one fail but after so many trials, I’m not sure.
My guess is that the client side has a problem with any but the simplest DN’s. But that is just a guess.

For the SSL connection error: protocol version mismatch – I did a little research in the code, but then stopped once I got it working:

protocol version mismatch points comes from badVersion_error yassl_error.cpp
badVersion is used in 2 files: extra/yassl/src/yassl_imp.cpp & extra/yassl/src/yassl_int.cpp
either there is a test that is applied to the client cert file that is bombing out because of a version, or the client cert file is not being loaded & causing an error with the underlying ssl — haven’t dug deeper yet

So Here is the code:

/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
log-error=/var/log/mysqld.log
bind-address=server.milcom.us

sql_mode=STRICT_ALL_TABLES
max_allowed_packet=64M
query_cache_size=128M

# innodb settings
innodb_fast_shutdown=0
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120

# replication settings
server-id=1
log-bin=mysql-bin
binlog-format=MIXED
sync_binlog=1

# SSL settings
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

log-error=/var/log/mysql/error
log-warnings

[client]
host=server.milcom.us
port=3306
user=username

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

/etc/mysql/make-cert
#!/bin/bash
# ********* NEW CERT Script **********
rm -f *.pem

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem -subj '/DC=us/DC=milcom/CN=CA'

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj '/DC=us/DC=milcom/DC=server'
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

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem -subj '/DC=us/DC=milcom/DC=server/CN=user'
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 0x100001 -out client-cert.pem

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

chmod 600 ca-key.pem
chmod 644 ca-cert.pem
chgrp mysql server* client*
chmod 640 server*
chmod 644 client*

References:

http://waterlovinghead.com/MysqlSSL&show_comments=1#comments
http://www.mysqlfanboy.com/2011/11/simplified-mysql-ssl-connections/
http://bugs.mysql.com/bug.php?id=64870
http://orensol.com/2010/06/21/error-2026-hy000-ssl-connection-error-the-solution/
http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
http://www.howtoforge.com/managing-multiple-mysql-servers-from-one-phpmyadmin-installation-using-ssl-encryption