Cannot open SSL connection to MySql server from .NET client

Cannot open SSL connection to MySql server from .NET client

Postby john22233328 » Sat 03 May 2008 02:29

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?
john22233328
 
Posts: 3
Joined: Fri 02 May 2008 22:39

Postby Alexey.mdr » Mon 05 May 2008 14:08

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
Last edited by Alexey.mdr on Tue 06 May 2008 12:12, edited 1 time in total.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Re: Cannot open SSL connection to MySql server from .NET client

Postby john22233328 » Mon 05 May 2008 17:34

MySql version 5.0.41-community-log

CoreLab MyDirect .NET for .NET 2 Professsional
DLLs are V4.30.20
john22233328
 
Posts: 3
Joined: Fri 02 May 2008 22:39

Postby Alexey.mdr » Tue 06 May 2008 14:22

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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Re: Cannot open SSL connection to MySql server from .NET client

Postby john22233328 » Tue 06 May 2008 16:39

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.
john22233328
 
Posts: 3
Joined: Fri 02 May 2008 22:39

Postby Alexey.mdr » Wed 07 May 2008 09:45

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....
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL