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?

Solution 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.

Solution 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!

Comments

[Michał Niklas]

On both screens you are using the same port. Is this ok? What can you fins on the server side? Are there any logs?

[Bas van Ommen]

Both really good questions! Thanks, I will look in to this soon and report back. Thanks!

[Bas van Ommen]

Thanks Michal, for answering.

[Drew]

This was killing me. Thank you for confirming. It seems like there is some issue with openssl 1.0.1

[symcbean]

"I guess 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!" - this is very dangerous advice. There's no point using a server certificate unless you do some sort of validation on it. It would be trivial to test whether you have completely disabled the validation or if something more esoteric is going on by pointing the CA path to an empty file. If you can still connect, then you should not have unchecked the box - you should have fixed the error.