MySQL connection using ODBC (5.1) with SSL

Question

We've got a client application that connects to our online MySQL database (5.1.44-community-log) thru a ODBC connector (the server is a managed* dedicated webserver). This works very nice. However I can't get it to work using SSL. This is what I've done so far:

1. MySQL server

I've got the server manager* set up MySQL with SSL, this is 'proven by':

mysql> SHOW VARIABLES LIKE '%ssl%';

which results is this response:

+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /***/mysql-cert/ca-cert.pem     |
| ssl_capath    |                                 |
| ssl_cert      | /***/mysql-cert/server-cert.pem |
| ssl_cipher    |                                 |
| ssl_key       | /***/mysql-cert/server-key.pem  |
+---------------+---------------------------------+

Question: is the server configured right? I'm guessing it is...

2. Certificates

I've purchased real certificates (via my server manager). These are in the directory shown above. I've also downloaded the client-cert.pem, client-key.pem and ca-cert.pem from that directory.

3. MySQL user with REQUIRE [SSL|X509]

I've created a new user and then granted it access from any location (for testing) with SSL:

GRANT USAGE ON *.* TO 'somevaliduser'@'%' IDENTIFIED BY PASSWORD 'somevalidpass' REQUIRE X509 

4. ODBC Client

I've (just downloaded and) installed : mysql-connector-odbc-5.1.8-winx64.msi (64-bit) as my machine is a 64-bit Windows 7 machine (so that's not what's wrong).

And I've created a User DSN configuring it like this (no options set on tabs), which shows it connecting to the server (however not using - nor requesting to do so - SSL) successfully (using some valid user which doesn't requires SSL):

So the connection is able to establish, now try using SSL.

This is configured like this, which is like I've read about on MySQL.com. So I'm not 100% sure the options set are right.

As you can see it results in a error HY000. Turning on tracing (within the ODBC configuration) also shows this error.

Can anyone give me a hint on how to make this work? Even if you know about just a part of the solution?

Answer 1

I solved the problem. Because I tried several things at a time I don't know what did the trick:

1. I've had the server manager re-create the certificates: I bought some but I found out that those couldn't be used to SSL-encrypt the connection. So for now I'm using OpenSSL certificates. I've had them re-create the certificates with 4) Create your client .... server. They must be unique. (as mentioned here) in mind.
2. I guess the checkbox 'Verify SSL Certificate' only applies when you buy a certificate and a thrid party service should check the validity of the certificate. Uncheck that box!
3. Only fill out the fields:
   • 'SSL Key' (c:\path_to\client-key.pem)
   • 'SSL Certificate' (c:\path_to\client-cert.pem)
   • 'SSL Certificate Authority' (c:\path_to\ca-cert.pem)

Please note:

1. The port is still the same (for me).
2. The logs - as Michal Niklas proposed - didn't show any usefull information.
3. I've toggled on 'Use compression' which is said to improve performance.

Answer 2

I am using Ubuntu 12.04 LTS with MySQL Ver 5.5.22-0ubuntu1 for debian-linux-gnu on x86_64 ((Ubuntu)) and OpenSSL OpenSSL 1.0.1 14 Mar 2012

I created the certificates following the tutorial on http://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln (The tutorial is in German, but this is not important here).

When trying to connect with

mysql -u root -p --ssl-ca=/etc/mysql/ca-cert.pem --ssl-cert=/etc/mysql/client-cert.pem --ssl-key=/etc/mysql/client-key.pem  --protocol=tcp

I always got an error message SSL connection error: protocol version mismatch

This lead me to the site http://bugs.mysql.com/bug.php?id=64870 which confirms (for me) that there is a bug.

To make a long story short. In the end I created all certificates using my MacOS X Lion, copied the certificates to the server and client and it worked immediately!

When I got the Linux side working, Windows worked immediatly, too! As mentioned above, you just have to set client-key, client-cert and ca-cert!