Connect to MySQL using SSL in C#

Question

I'm connecting to a MySQL server that requires SSL, using a MySqlConnection:

var connection = new MySqlConnection("Data Source=127.0.0.1;Database=MyDb1;User Id=root;Password=blabla;encrypt=true");

This succeeds if the server doesn't require SSL, but after executing GRANT USAGE ON *.* TO 'root'@'localhost' REQUIRE SSL; on the server, it starts failing with:

ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. ---> MySql.Data.MySqlClient.MySqlException: Authentication to host '127.0.0.1' for user 'root' using method 'mysql_native_password' failed with message: Access denied for user 'root'@'localhost' (using password: YES) ---> MySql.Data.MySqlClient.MySqlException: Access denied for user 'root'@'localhost' (using password: YES)

How is SSL enabled? Should something be added to the connection string or is it done programmatically?

Answer 1

OK, after some more searching I found the answer at How determine if using SSL in a MySql Connection?.

I had to add SSL Mode=Required instead of encrypt=true to the connection string:

var connection = new MySqlConnection("SSL Mode=Required;Data Source=127.0.0.1;Database=MyDb1;User Id=root;Password=blabla");

Now I'm getting a new error - MySql.Data.MySqlClient.MySqlException: “The host localhost does not support SSL connections.”, but I think I'll figure it out.

Answer 2

This connecting string work for me with MySQL Comunity Server 8.0.35.

1. Look at here to generate SLL Certificates for MySQL Server (*.pem) and for C# Client (client.pfx)

2. I use connString like this:

String connString = "server=localhost;
                     user id=root;
                     database=YOUR_DB_NAME;
                     persistsecurityinfo=True;
                     password=YOUR_DB_PASSWORD;
                     allowuservariables=True;
                     allowbatch=True;
                     sslmode=Required;
                     certificatepassword=YOUR_PFX_CERTIFICATE_PASS;
                     certificatefile=YOUR_PFX_CERTIFICATE_FILE;"

Example

 YOUR_DB_NAME = discount_card
 YOUR_DB_PASSWORD = db_p455w0rd
 YOUR_PFX_CERTIFICATE_PASS = pfx_p455w0rd
 YOUR_PFX_CERTIFICATE_FILE = D:\\certificates\\client.pfx