Cannot get SSL with Mysql working [SOLVED]
Posted: 23.08.2013, 15:22
I am trying to build an application for accessing the mysql service provider cleardb.com
They provide a mysql server as a service: a small server is even free and I have some small projects that would benefit from a secure internet mysql server :-)
For a secure connection they provide the three ssl certificates as .pem files, on the user dashboard.
The certificates are fine, because I tested them using the commandline:
(using Windows 8.1 preview, Mysql 5.5.33 community client, Mysql 5.5.21 server (Cleardb.com))
(After doing: )
This results in a secure connection because 'show variables like "ssl_cipher"' shows an expected value.
However, I want this to work with Zeos 7.0.4 stable and Lazarus 1.0.10. A normal insecure connection with cleardb.com is no problem. That works fine. But the ssl part does not work :-(
This is the additional code I have (compared to a normal connection which works fine).
The connection is made, but it is not secure (ssl_cipher is empty).
The certificates must be correct as they work from the commandline. And I also tried the 'key.pem' version instead of 'key-no-password.pem' version.
UPDATE:
Ok, I found the solution. After spending a lot of time stepping over the code. At some point I was puzzeled by the library names refered to in the code plus options to be strict or not. It turns out that is of major importance. I had several libmysql.dll files in the same directory as my exe. I thought that Zeos would by default use 'libmysql.dll', so I renamed other ones to something like 'libmysql51.dll'. But in non-struct mode (the default...), Zeos has a preference for non-standard names ! So I either have to remove all other dlls or properly name the libmysql.dll to libmysql55.dll (if thats the correct version). Mybe it would also have helped to set Zeos to strict mode.
ALSO:
The dll that Zeos used implicitely during my tests, was only 1,3 Mb while the dll I thought was being used was 4 Mb. The ssl connection is still not possible with the 1,3 Mb dll so I guess that one was not compiled with support for ssl. I read something about that earlier, so that is why I added the 4 Mb version later.
They provide a mysql server as a service: a small server is even free and I have some small projects that would benefit from a secure internet mysql server :-)
For a secure connection they provide the three ssl certificates as .pem files, on the user dashboard.
The certificates are fine, because I tested them using the commandline:
(using Windows 8.1 preview, Mysql 5.5.33 community client, Mysql 5.5.21 server (Cleardb.com))
Code: Select all
mysql --host=<HOST> --user=<USER> --password=<PASSWORD> --ssl-ca=c:\cleardb\ca.pem --ssl-cert=c:\cleardb\cert.pem --ssl-key=c:\cleardb\key-no-password.pemCode: Select all
openssl rsa -in key.pem -out key-no-password.pemThis results in a secure connection because 'show variables like "ssl_cipher"' shows an expected value.
However, I want this to work with Zeos 7.0.4 stable and Lazarus 1.0.10. A normal insecure connection with cleardb.com is no problem. That works fine. But the ssl part does not work :-(
This is the additional code I have (compared to a normal connection which works fine).
Code: Select all
Connection.Properties.Values['MYSQL_SSL'] := 'TRUE';
Connection.Properties.Values['MYSQL_SSL_CA'] := 'c:\Cleardb\ca.pem';
Connection.Properties.Values['MYSQL_SSL_CERT'] := 'c:\Cleardb\cert.pem';
Connection.Properties.Values['MYSQL_SSL_KEY'] := 'c:\Cleardb\key-no-password.pem';The certificates must be correct as they work from the commandline. And I also tried the 'key.pem' version instead of 'key-no-password.pem' version.
UPDATE:
Ok, I found the solution. After spending a lot of time stepping over the code. At some point I was puzzeled by the library names refered to in the code plus options to be strict or not. It turns out that is of major importance. I had several libmysql.dll files in the same directory as my exe. I thought that Zeos would by default use 'libmysql.dll', so I renamed other ones to something like 'libmysql51.dll'. But in non-struct mode (the default...), Zeos has a preference for non-standard names ! So I either have to remove all other dlls or properly name the libmysql.dll to libmysql55.dll (if thats the correct version). Mybe it would also have helped to set Zeos to strict mode.
ALSO:
The dll that Zeos used implicitely during my tests, was only 1,3 Mb while the dll I thought was being used was 4 Mb. The ssl connection is still not possible with the 1,3 Mb dll so I guess that one was not compiled with support for ssl. I read something about that earlier, so that is why I added the 4 Mb version later.