How to: MySQL (Linux), client (Windows) via ODBC with SSL

rumoldus2003
rumoldus2003 used Ask the Experts™
on
I have trouble connecting a client on a Windows machine though MySQL database on a Linux server, using ODBC with SSL. Without SSL, no problem. I need the connection to be encrypted; I am not so much interested in client identification (x509).

I have purchased a SSL-certificate from a certificate-providing company. This file I saved on the Linux machine. I understand how to configure MySQL through my.cnf to use this certificate, using the ssl-ca=<path to certificate> option.
But then there are the server.crt and server.key files as well. I found these on the server, but as their exact function is not clear to me, it is also unclear whether these files will be OK to use.

It gets worse with the client side. As stated, I do not need a certificate at the client side, but I fail to understand the SSL parameters I need to provide in the ODBC driver.
First of all, do I need to copy the SSL-certificate to the client as well ? From the MySQL documentation I understand that it is sufficient to provide to use the --ssl_ca option, but that suggests that the certificate file be put on the client machine, to be used with that option. I find this confusing. When I use a https-connection to a secure website, I do not have to load some certificate file to use that connection; It is sufficient that the server has a certificate. Why should it be different for MySQL ?

I do understand that in order to force the use of SSL, I need to configure the MySQL user account with GRANT ... REQUIRE SSL.

MySQL: 5.0.37
Linux: Red-Hat 3.2.33(1), (bash)
ODBC: 3.51.27
Client OS: WinXP

Any help is appreciated.

Rumoldus
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave HoweSoftware and Hardware Engineer

Commented:
all good so far.

At the server side, you shouldn't really need to assert the CA cert - after all, you have the private key and public certificate - but it doesn't hurt.

use:
--ssl-ca=cacert.pem
--ssl-cert=server-cert.pem
--ssl-key=server-key.pem

where cacert and server-cert are the ca and server certificates you bought from the commercial CA, and server-key was output by your original request (when you created your certificate signing request, you should have gotten a CSR and a KEY file - this is the KEY file.

now, on the client side, you usually specify the ssl-ca so that the client can verify the certificate.

note that in every case, you are specifying the CA to be used - so there is no advantage to buying a certificate, you might as well mint one yourself and save yourself the cash!

for odbc, you need to use the extended parameters to specify the CA cert. this is used to verify the server cert presented by the server.

http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html

Author

Commented:
Hi,
Thanks for your time. It looks better now.
I do have a x509 certificate, as received from the commercial CA, after I sent in the csr file. But that's all, I received 1 certificate, not 2. Which one would that be then; The server cert or the CA thing.
And should I have expected 2 ?
(I have basically followed the instructions for an Apache server, as there were no specific ones for MySQL). Any clue ?
And I'm still puzzled by the difference in Webbrowsing through SSL and using MySQL over SSL. I seem to miss some essential pieces of information there.

Dave HoweSoftware and Hardware Engineer

Commented:
if you have received only one cert, it will be the server cert not the CA cert. easiest way (in windows) to obtain the CA cert is to double-click the server cert - this will open it in the (internet explorer) keystore tool, and will show you the certificate chain - i.e. the CA cert IE has that verifies the server cert you double clicked. this is the "certification path" tab.

if you click on that, you should see both your certificate, and a CA certificate. clicking on the CA lets you "view certificate" then on the details tab "copy to file". The newly created file will be the CA certificate that verifies the issued cert, both at the server side and in the odbc client.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Dave HoweSoftware and Hardware Engineer

Commented:
and essentially, there is no difference between a webserver cert, an email server (TLS) cert, and a mysql (TLS/SSL) cert - they all use the same encryption libraries and perform the same tasks, and the same certificate can be used for all three.
Dave HoweSoftware and Hardware Engineer

Commented:
one practical difference though - a web browser will already contain all the CA certs, and expect your server cert to be verified by one of them. the MySQL ODBC client expects *you* to give it the CA cert or certs, and hence you could have made your own (for free) with little or no change in functionality.

Author

Commented:
Hi Dave,
many thanks so far. You'll be rewarded.
I am trying this now. I changed my.cnf, stopped the mysql deamon through mysqladmin, checked that it stopped, restarted with command mysqld_safe &, but then I'm asked for a PEM pass phrase. (See fragment below). Apart from the fact that the pass phrase does not seem to be accepted, I am worried for server reboots in the future. (This is not my own server). Will mysql daemon startup be dependant on some user input ? I haven't seen anything like this anywhere on the Web so far.


[root@server etc]# nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /var/lib/mysql
Enter PEM pass phrase:
########
-bash: ########: No such file or directory
 
[2]+  Stopped                 mysqld_safe

Open in new window

Dave HoweSoftware and Hardware Engineer

Commented:
yes. the choices are to either provide the passphrase in the startup, start up manually (so you can type the passphrase) or remove the passphrase from the secret keyfile (ensuring of course that file permissions ensure it is readable only by the daemon and the daemon's account isn't shared by any other process (apache, for instance)

the latter is the most common solution.

Author

Commented:
OK, there is progress. I removed the passphrase, and now mysqld no longer asks for it.
What I forgot to mention is, that when I was playing with the certificates and keys etc. in my.cnf, the variable have_openssl changed to 'disabled' where it always had been 'yes'.
Now it is 'yes' again. I have the feeling that at the server side everything should be OK.
(Is there anything to check there ?)
Yet, I still cannot get an SSL connection to the database. The main problem is that the ODBC driver, when using 'Test', only gives 'Request returned with result SQL-ERROR'. No specifics.
In the ODBC-driver, the only 2 fields I have entered for SSL are SSL Certificate Authority (Value D:\ca.pem, I also tried D:/ca.pem just to be sure) and SSL Cipher (Value ALL). So the main target now is to understand why the connection fails and what the parameters should be.
I also entered ssl-ca=<path> in the my.ini file, but it is unclear in what section this should be placed for ODBC connections.

In the mean while, when I look at run time information on the server, I see:
SSL-accepts 8
Ssl_ctx_verify_depth 18.45 E
SSL_ctx_verify mode 5
SSL_cache_mode SERVER
SSL_cache_size 128

I think most of these were 0 previously. The other SSL fields are still 0.

Dave HoweSoftware and Hardware Engineer

Commented:
for the odbc connector, it can be added in the gui - see the link I posted in my first reply.

Author

Commented:
I tried that first, but no results. (Same SQL_ERROR). That's why I went for my.ini.
I've now set up a link using mysql.exe from windows. I then get Error 2026 (HY000): SSL Connection error. With other users and a non-SSL link, I can get a normal connection.
If I remove the SSL requirement from the test user, I also can get a normal connection. When the require SSL is removed, and I activate the 'Verify SSL certificate' in the ODBC driver, I still get a good connections. No idea whether that is because the certificate check is OK or because it is not checked at all.
Any idea how I can diagnose further ? What I understand from internet discussions, there is not much information to be expected at the client side. I probably should check the validity of the certficates somehow, and check information the server provides (run time info, logs) but I could use some hints here (basically I haven't got a clue where to go from here).

Author

Commented:
I now generated all certificates myself as per the official MySQL documentation.
Same problem at remote site. SSL Connection error from mysql.exe, also using ODBC.
I also run mysql locally at the server, this works fine using SSL with my own new certificates. So there is no fundamental problem with SSL certificates setup but I still can't connect remotely using SSL from a Windows machine.

Any help will be welcome.

Author

Commented:
I now think that the current version of MySQL on the server I'm working on, is part of the problem. It is 5.0.37 and it seems some SSL-problems have been solved in 5.0.64 (or thereabout). Current 5.0 version is 5.0.87 or so.
Could anybody confirm this suspicion ?

Commented:
Question PAQ'd, 500 points refunded, and stored in the solution database.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial