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?
Cannot open SSL connection to MySql server from .NET client
-
- Posts: 3
- Joined: Fri 02 May 2008 22:39
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Could you please specify the full version and edition of MySQL server and MyDirect .NET?
The following connection string works fine:
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.
-
- Posts: 3
- Joined: Fri 02 May 2008 22:39
Re: Cannot open SSL connection to MySql server from .NET client
MySql version 5.0.41-community-log
CoreLab MyDirect .NET for .NET 2 Professsional
DLLs are V4.30.20
CoreLab MyDirect .NET for .NET 2 Professsional
DLLs are V4.30.20
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
We tested the SSL connection functionality. The following code works fine: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.
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();
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.
-
- Posts: 3
- Joined: Fri 02 May 2008 22:39
Re: Cannot open SSL connection to MySql server from .NET client
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.
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.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
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....
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....