ASP .NET MVC project hosted on Azure to connect to clearDB mySQL using SSL authentication

Question

I have a free subscription on Azure which comes with a web application and a small mySQL database from cleardb.com (Dreamspark). ClearDB provides certificate downloads for SSL authentication to the database, which works fine when I try to connect to it directly using either MySQL Workbench or from VisualStudio.

How do I set up a connectionString in VisualStudio to specify the remote path to the certificate file once I've copied it to Azure?

In other words how can I specify the path to the ssl certification file on Azure and where do I put it to connect to cleardb using SSL? My local path on the "C:\" drive is obviously not going to work.

The connectionString in Web.config should look something like this:

<add name="mySQLExample"
     connectionString="server=azure-example.cloudapp.net;
     user id=example-user;
     password=password;
     persistsecurityinfo=True;
     connectiontimeout=5;
     database=mySQL_db_example;
     sslmode=Required;
     certificatefile=C:\path\to\cert\file\randomchars-cert.pfx"
  providerName="MySql.Data.MySqlClient" />

I'm guessing it's a Windows Server, so I need a similar path, right? Or will it work with a UNIX/internet style path which simply specifies my home directory on Azure with ~/randomchars-cert.pfx (wouldn't that be nice)?

Answer 1

The following guide that can help you connect to ClearDB using SSL security, thus ensuring a 100% SSL Everywhere environment for your database.

Preparing for SSL connectivity

ClearDB offers our users the ability to connect via SSL using certificates and keys.

Note: do not share these certificates with anyone that you don't want to have access to your database. Each certificate is only available and visible to your account.

Download the correct certificate(s) for use in your applications.

Connecting via SSL to ClearDB using PHP

In order to connect via SSL using PHP, you'll need to use the "MySQLi" extension, like this:

$db = mysqli_init(); $db->ssl_set(PATH_TO_SSL_CLIENT_KEY_FILE, PATH_TO_SSL_CLIENT_CERT_FILE, PATH_TO_CA_CERT_FILE, null, null); $db->real_connect(HOSTNAME, USERNAME, PASSWORD, DATABASE_NAME);

For more information about using PHP's MySQLi extension to create SSL encrypted connections to MySQL, see the official PHP documentation. http://www.php.net/manual/en/mysqli.ssl-set.php

Connecting via SSL to ClearDB using Python/Django

Connecting via Python/Django should be easily performed by simply passing the SSL information as follows:

DATABASES['default'] = { 'ENGINE': 'django.db.backends.mysql', 'HOST': 'my-host-goes-here', 'USER': 'my-user-goes-here', 'NAME': 'my-db-name-goes-here', 'PASSWORD': 'my-db-pass-goes-here', 'OPTIONS': {'ssl': {'ca':'/path/to/cert.pem', 'cert':'/path/to/cert.pem', 'key':'/path/to/key.pem'},}, }

You can also find out how to connect via SSL to ClearDB by checking out the MySQLdb driver documentation at http://mysql-python.sourceforge.net/MySQLdb.html

For .NET MySQL connectivity see

MySQL Connector/Net

The MySQL Connector/Net manual is now published in standalone form, not as part of the MySQL Reference Manual.

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. see http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-ssl.html

• Release notes: MySQL Connector/Net Release Notes http://dev.mysql.com/doc/relnotes/connector-net/en/

Connecting to the server using a store-based certificate

1. 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.

2. Follow the steps dictated by the wizard, and when prompted for the password for the PFX file, enter “pass”.

3. Click Finish to close the wizard and import the certificate into the personal store.

Examine certificates in the Personal Store

1. Start the Microsoft Management Console by entering mmc.exe at a command prompt.

2. Select File, Add/Remove snap-in. Click Add. Select Certificates from the list of available snap-ins in the dialog.

3. Click Add button in the dialog, and select the My user account radio button. This is used for personal certificates.

4. Click the Finish button.

5. Click OK to close the Add/Remove Snap-in dialog.

6. 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.

7. 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(); }

   using (MySqlConnection connection = new MySqlConnection( "database=test;user=sslclient;" + "Certificate Store Location=CurrentUser;" + "Certificate Thumbprint=479436009a40f3017a145cf8479e7694d7aadef0;"+ "SSL Mode=Required")) { connection.Open(); }