Cannot open SSL connection to MySql server from .NET client

[john22233328]

I am attempting to improve the internal security at our company by requiring
SSL connections to our MySql database from ASP.Net. I have set up the
MySql server to use OpenSSL, but when I attempt to make a connection from
the client, I get the error:

"Lost connection to MySQL server during query"

This error occurs immediately (it is not as though it timed out).

I have done various searches on this site, Google, etc and have not found
the solution.


The MySql (Linux) server is set up as follows:

1. Created certificates in /d1/mysql/Certificates using the openssl
command as specified at http://dev.mysql.com/doc/refman/5.0/en/ ... certs.html:

cacert.pem
client-cert.pem
client-key.pem

2. In the /etc/my.cnf file, added the following to the [mysqld] section:

ssl-ca=/d1/mysql/Certificates/cacert.pem
ssl-cert=/d1/mysql/Certificates/server-cert.pem
ssl-key=/d1/mysql/Certificates/server-key.pem

3. In the /etc/my.cnf file, added the following to the [client] section:

ssl-ca=/d1/mysql/Certificates/cacert.pem
ssl-cert=/d1/mysql/Certificates/client-cert.pem
ssl-key=/d1/mysql/Certificates/client-key.pem

4. Restarted the MySql server.

Now, when I do a SHOW VARIABLES LIKE '%ssl%', I get:

Variable_name Value
have_openssl YES
have_ssl YES
ssl_ca /d1/mysql/Certificates/cacert.pem
ssl_capath ""
ssl_cert /d1/mysql/Certificates/server-cert.pem
ssl_cipher ""
ssl_key /d1/mysql/Certificates/server-key.pem


The (WinXP) client attempts to connect to the server as follows:

1. Copy the certificate and keys to the C:\junk5 folder on the client:

ca-cert.pem
client-cert.pem
client-key.pem

2. Add the following code to my C# .NET program:

MySqlConnection connection = new MySqlConnection();

connection.ConnectionString = "server=10.1.1.111;user id=myuser; password=mypassword; database=MyDatabase; pooling=true;Protocol=SSL;";

connection.SslOptions.CACert = "file://C:\junk5\ca-cert.pem";
connection.SslOptions.Cert = "file://C:\junk5\client-cert.pem";
connection.SslOptions.Key = "file://C:\junk5\client-key.pem";
connection.Open();


As soon as an attempt is made to open the connection, the error

"Lost connection to MySQL server during query"

occurs. This works if I exclude setting the SslOptions and the "Protocol=SSL;" in
the connection string.


I have opened the permissions on the certificate and key files on both the
server and client.


Does anyone have any idea why this does not work?

[Alexey.mdr]

Could you please specify the full version and edition of MySQL server and MyDirect .NET?
The following connection string works fine:

Code: Select all

user id=root;host=testserver;protocol=Ssl;ssl ca cert=file://P:\Temp\SSL\caserver.pem;ssl cert=file://P:\Temp\SSL\server.pem;ssl key=file://P:\Temp\SSL\server_key.pem

[john22233328]

MySql version 5.0.41-community-log

CoreLab MyDirect .NET for .NET 2 Professsional
DLLs are V4.30.20

[Alexey.mdr]

We tested the SSL connection functionality. The following code works fine:

Code: Select all

MySqlConnection con =
                new MySqlConnection("User Id=testssl;Password=testssl;Host=sslserver;Protocol=Ssl;");
        con.SslOptions.CACert=@"file://P:\Temp\SSL\caserver.pem";
        con.SslOptions.Cert = @"file://P:\Temp\SSL\server.pem";
        con.SslOptions.Key = @"file://P:\Temp\SSL\server_key.pem";
        con.Open();

MyDirect .NET doesn't specify the reason of the lost connection.
This will be fixed in the future builds.
The socket was closed by the server.
Please check your user name and password.
Then verify all certificates and keys.
Try connecting through the standard MySQL console.

[john22233328]

After staring at it for several hours, I finally realized what went
wrong. In the /etc/my.cnf file, the lines

[mysqld]
ssl-ca=/d1/mysql/Certificates/cacert.pem

[client]
ssl-ca=/d1/mysql/Certificates/cacert.pem

should have been

[mysqld]
ssl-ca=/d1/mysql/Certificates/ca-cert.pem

[client]
ssl-ca=/d1/mysql/Certificates/ca-cert.pem

Apparently, the server did not even complain (not even in the log
files) that there was no file with the given name. Better feedback
from the API (if available from the server) would definitely have
made this easier.

Thanks for all of your help.

[Alexey.mdr]

Sorry it took so much time.
We will work on this problem and try to make the exception
messages more informative with SSL connection.
The main problem here is that the server usually sends a simple error message, like:
SSL connection failed. The server seldom sends even an error code.
Nevertheless we will make everything possible to make the exceptions more specific than Lost connection....