In this tutorial you will learn how you can use MySQL Connector/NET to connect to a MySQL server configured to use SSL. Support for SSL client certificates was added with MySQL Connector/NET 6.2.
MySQL Server uses the PEM format for certificates and private keys.
This tutorial will use the test certificates from the server test
suite by way of example. You can obtain the MySQL Server source code
from
MySQL
Downloads. The certificates can be found in the directory
./mysql-test/std_data
.
To carry out the steps in this tutorial you will also need to have Open SSL installed. This can be downloaded for Microsoft Windows at no charge from Shining Light Productions.
Further details on the connection string options used in this tutorial can be found at Section 17.2.6, “Connector/NET Connection String Options Reference”.
Configuring the MySQL Server to use SSL
In the MySQL Server configuration file, set the SSL parameters as follows:
ssl-ca=path/to/repo/mysql-test/std_data/cacert.pem ssl-cert=path/to/repo/mysql-test/std_data/server-cert.pem ssl-key=path/to/repo/mysql-test/std_data/server-key.pem
Adjust the directories according to the location in which you installed the MySQL source code.
In this step you create a test user and set the user to require SSL.
Using the MySQL Command Line Client, connect as root and create
the user sslclient
.
To set privileges and requirements, issue the following command:
GRANT ALL PRIVILEGES ON *.* TO sslclient@'%' REQUIRE SSL;
Creating a certificate file to use with the .NET client
The .NET client does not use the PEM file format, as .NET does
not support this format natively. You will be using test client
certificates from the same server repository, for the purposes
of this example. You will need to convert these to PFX format
first. This format is also known as PKCS#12. An article
describing this procedure can be found at the
Citrix
website. From the directory
,
issue the following command:
server-repository-root
/mysql-test/std_data
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -certfile cacert.pem -out client.pfx
When asked for an export password, enter the password
“pass”. The file client.pfx
will be generated. This file is used in the remainder of the
tutorial.
Connecting to the server using a file-based certificate
You will use PFX file, client.pfx
you
created in the previous step to authenticate the client. The
following example demonstrates how to connect using the
SSL Mode
, CertificateFile
and CertificatePassword
connection string
options:
using (MySqlConnection connection = new MySqlConnection( "database=test;user=sslclient;" + "CertificateFile=H:\\bzr\\mysql-trunk\\mysqlest\\std_data\\client.pfx" + "CertificatePassword=pass;" + "SSL Mode=Required ")) { connection.Open(); }
The path to the certificate file will need to be changed to reflect your individual installation.
Connecting to the server using a store-based certificate
The first step is to import the PFX file,
client.pfx
, into the Personal Store.
Double-click the file in Windows explorer. This launches the
Certificate Import Wizard.
Follow the steps dictated by the wizard, and when prompted for the password for the PFX file, enter “pass”.
Click Finish to close the wizard and import the certificate into the personal store.
Examine certificates in the Personal Store
Start the Microsoft Management Console by entering
mmc.exe
at a command prompt.
Select File, Add/Remove snap-in. Click Add. Select Certificates from the list of available snap-ins in the dialog.
Click Add button in the dialog, and select the My user account radio button. This is used for personal certificates.
Click the Finish button.
Click OK to close the Add/Remove Snap-in dialog.
You will now have Certificates – Current User displayed in the left panel of the Microsoft Management Console. Expand the Certificates - Current User tree item and select Personal, Certificates. The right-hand panel will display a certificate issued to MySQL. This is the certificate that was previously imported. Double-click the certificate to display its details.
After you have imported the certificate to the Personal Store, you can use a more succint connection string to connect to the database, as illustrated by the following code:
using (MySqlConnection connection = new MySqlConnection( "database=test;user=sslclient;" + "Certificate Store Location=CurrentUser;" + "SSL Mode=Required")) { connection.Open(); }
Certificate Thumbprint Parameter
If you have a large number of certificates in your store, and many
have the same Issuer, this can be a source of confusion and result
in the wrong certificate being used. To alleviate this situation,
there is an optional Certificate Thumbprint parameter that can
additionally be specified as part of the connection string. As
mentioned before, you can double-click on a certificate in the
Microsoft Management Console to display the certificate's details.
When the Certificate dialog is displayed click the
Details tab and scroll down to see the
thumbprint. The thumbprint will typically be a number such as
47 94 36 00 9a 40 f3 01 7a 14 5c f8 47 9e 76 94 d7 aa de
f0
. This thumbprint can be used in the connection string,
as the following code illustrates:
using (MySqlConnection connection = new MySqlConnection( "database=test;user=sslclient;" + "Certificate Store Location=CurrentUser;" + "Certificate Thumbprint=479436009a40f3017a145cf8479e7694d7aadef0;"+ "SSL Mode=Required")) { connection.Open(); }
Spaces in the thumbprint parameter are optional and the value is case-insensitive.
User Comments
Add your own comment.