MySQLd 5.0.40, MySQL Client, and SSL Connectivity Problems
So, I now have one of our new servers sitting in a datacenter in Dallas. It is to replace one of our dedicated servers we are renting through The Planet. Well, our client’s website is doing extremely well right now and is averaging roughly 2,000 hits per day. This is one of the various eCommerce websites we have built for various customers. So, credit card data does get transmitted (always securely) between the database server residing on the dedicated server. Well, in order to minimize downtime for our customer’s website and to retain no loss of data, we have to do something about database connectivity between the old server and the new server. Luckily, MySQL is resilient enough to allow for remote connections, and as long as the latency between the servers isn’t bad, the website will operate at the same rate as normal.
However, the difference between connecting to a database residing on localhost and a database server residing on some other machine is the type of connectivity. By default, connections are unencrypted, so eCommerce information should NOT be transmitted in this manner. We need to transmit it over SSL-style connections. Again, MySQL is resilient enough to have these features built-in, assuming you compiled your server with SSL support.
I began by taking a copy of our customer’s database and sending it over to the new server:oldServer> mysqldump -u root databasename -p > databasename.mysql
oldServer> scp databasename.mysql newserver:/home/customer/databasename.mysql
oldServer> ssh newserver
newServer> mysql -u root databasename -p < /home/customer/databasename.mysql
No problems were encountered here. So, the difference between SSL connections using SSH and SSL connections using MySQL is that MySQL requires SSL certificates and keys, much like web servers and mail servers do. However, you don’t have to purchase one of these. You CAN use self-signed certificates, as long as you have access to the Certificate Authority certificate file (shouldn’t be a problem if you are acting as the Certificate Authority). I’m not going to go into signing your own certificates, but there are several great websites that tell you how to do this, such as this one.
So, I did the usual things necessary to create my certificate (since I act as the certificate authority for my company), and copied the certificate data into the following locations:newServer> cp /root/sslcerts/newServer.key /etc/ssl/private/newServer.key
newServer> cp /root/sslcerts/newServer.crt /etc/ssl/certs/newServer.crt
newServer> cp /root/sslcerts/CA.crt /etc/ssl/certs/CA.pem
newServer> cd /etc/ssl/certs
newServer> /usr/bin/c_rehash
I followed the same pattern for the CA certificate as everything else that was in that directory. All extensions were .pem. Note that openssl generates PEM formatted certificates and keys anyways, so it is perfectly legal and acceptable. c_rehash is used to parse the /etc/ssl/certs directory for new certificate authority certificates and create a symbolic link that has a name that’s a hashed value of the certificate. Read the man page for c_rehash for further information.
Anywho, everything was in their respective location, and I had added the following lines in my /etc/mysql/my.cnf file (under the server section):ssl-ca = /etc/ssl/certs/CA.pem
ssl-key = /etc/ssl/private/newServer.key
ssl-cert = /etc/ssl/certs/newServer.crt
ssl-cipher = ALL:-AES:-EXP
I started MySQL and no errors were detected. As the MySQL Manual states for SSL connectivity, you issue the command show variables like ‘have%’ to show if you have SSL connectivity available. Here’s what mine showed:mysql> show variables like 'have%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+----------+
20 rows in set (0.00 sec)
Well that’s interesting, SSL is disabled. After a few hours of trying to figure out what this is, including starting SSL with those configuration options on the command line rather than through the my.cnf file, I found out the culprit of this was the ssl-cipher line in the config. Even setting this to just ALL caused the same result. However, when I removed that option and restarted MySQL, I got the following results:mysql> show variables like 'have%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | YES |
| have_ssl | YES |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+----------+
20 rows in set (0.00 sec)
I have filed a bug with MySQL about this, because it’s a critical flaw, not being able to specify which Ciphers you wish to allow.
So, now that I see SSL connectivity is allowed, I need to create an account and fully restrict it to require SSL connections. This is done in my grant statement. Full instructions on Granting with SSL Requirements are available in the MySQL Reference Manual. So, in MySQL, I issue the following command:mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `databasename`.* TO 'customer_dbuser'@'192.168.1.1' \
-> IDENTIFIED BY 'goodsecret' \
-> REQUIRE ISSUER '/C=US/ST=Texas/L=College Station/O=Company Name/OU=Certification Authority Name/CN=My Root Certificate Authority' \
-> SUBJECT '/C=US/ST=Texas/L=College Station/O=Company Name/OU=Department/CN=oldserver.domain.com/emailAddress=admin@domain.com' \
-> CIPHER 'DHE-RSA-AES256-SHA';
The ISSUER and SUBJECT lines come from what you filled out when creating the certificate authority CSR and the host CSR, respectively. You can get this value by looking for the issuer and subject lines in the ouput from the following command:shell> openssl x509 -noout -in oldServer.crt -text
The CIPHER is what openssl uses by default for creating SSL certificates. If you used a different cipher when creating the key, CSR, and Certificates, then specify it accordingly.
So, after having those problems and now having SSL connectivity (half-way) completed, it’s time to move onto the old server. I send over the newly created SSL certificates to the old server via SSH and install them in roughly the same places:oldServer> cp /root/sslcerts/CA.crt /etc/ssl/certs/CA.pem
oldServer> cp /root/sslcerts/oldServer.crt /etc/ssl/certs/oldServer.crt
oldServer> cp /root/sslcerts/oldServer.key /etc/ssl/private/oldServer.key
I then added the following lines under the client section in the /etc/mysql/my.cnf file:ssl-ca = /etc/ssl/certs/CA.pem
ssl-key = /etc/ssl/private/oldServer.key
ssl-cert = /etc/ssl/certs/oldServer.crt
# ssl-cipher = ALL:-AES:-EXP
The specification of certificate information on the server as well as certificate information on the client is ESSENTIAL in the success of this. RTFM, if you haven’t already.
So, time to fire up the connection to see if it works:oldServer> mysql -u customer_dbuser -h newServer -p --ssl-ca=/etc/ssl/certs/CA.pem --ssl-key=/etc/ssl/private/oldServer.key --ssl-cert=/etc/ssl/certs/oldServer.crt
Password: <enter goodsecret>
SSL Connect Failed
If you fail to put anything in the client section of the my.cnf file and DONT specify a ssl-key or ssl-cert on the command line, you will see a generic SSL connection error on the client side and the following error message on the server’s logs (IF you have debugging turned on):tls peer did not respond with certificate list
This even occurs if you only entered REQUIRE SSL for the client and nothing further. But, this was not my case. I spend yet another few hours trying to figure out what happened with this and finally recompiled the newServer MySQL with the debug option. After trying again and parsing the debug files, I found the following line:Error: "error:00000005:lib(0):func(0):DH lib"
This was in the sslaccept function. After Googling this for about an hour, I found nothing relevant to the problem. It’s a generic openssl error, assuming that you are using openssl and not another SSL library set.
To make a long story short, the error message was extremely misleading and it boiled down to a permissions problem. I coulda sworn that all my permissions were correct, but it turns out they weren’t. Here’s how it was setup:newServer> ls -alF /etc/ssl/private
drwx------ 2 root root 184 May 19 21:47 ./
drwxr-xr-x 5 root root 152 Apr 19 19:52 ../
-rw-r--r-- 1 root root 3247 May 19 20:32 newServer.key
For those still unfamiliar with Linux, if you look at the permissions for ./, you’ll notice it’s Read, Write, and Execute for the owner ONLY. This means that nobody else could traverse into this directory, EVEN THOUGH the newServer.key was at least readable by everyone.
Moral of the Story
Double check your permissions.
