How to integrate Azure MySQL with PHP (on an Azure Webapp)

Introduction

When you deploy a MySQL in Azure, you should know that by default this enforces the usage of SSL on your connection.

And this is for due reason! You really do not want to create your database connection unencrypted. Though this is something that comes as something new to most PHP deployments, so let’s take a look at how to tackle this!

 

Database Hardening

Right under the Encryption setting (of your MySQL database), you can also find the “Firewall rules” ;

Here the hardening is pretty similar to the combination of an Azure (MSSQL) Database & the Webapp. Just go to your Azure Webapp, and browse to “Properties”. Here you can see the “Outbound IP Addresses”.

You should use these for the firewall rules of your MySQL database ;

So now only our web app is able to reach our database! Next up, let’s ensure that that connection is encrypted!

 

Quick Example

Before we begin, read this documentation page!

Now the first thing we do is download the CA Certificate (as mentioned in the documentation). The MySQL client will use this later on to validate the authenticity of the certificate presented by the MySQL server.

Next up, we’ll need to transform that certificate from a “cer”-format to a “pem”-format. This is to ensure that our Azure Webapp is able to process the certificate. For this I used the “Bash on Windows” with OpenSSL installed ;

  • OpenSSL>x509 -inform DER –in BaltimoreCyberTrustRoot.cer -out MyServerCACert.pem

And afterwards I copied the “MyServerCACert.pem” to /mnt/c/users/…(and so on), in order to “see” it in my Windows system.

Now we are good to go… Let’s create a folder “cert” in the wwwroot of our webapp, and upload an “index.php” to the root of our webapp with the following contents ;

<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);

$db_ca_cert = realpath('./cert/MyServerCACert.pem');
$db_user = "user@host";
$db_pass = "mynotsosecretpassword" ;
$db_host = "host.mysql.database.azure.com";
$db_db = "billing";
$db_port = "3306";

$con=mysqli_init(); 
mysqli_ssl_set($con, NULL, NULL, $db_ca_cert, NULL, NULL);
$link = mysqli_real_connect($con, $db_host, $db_user, $db_pass, $db_db, $db_port, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);

if (!$link)
{
 die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
 $res = $con->query('SHOW TABLES;');
 print_r ($res);
 $con->close();
}
?>

You can notice that the connection isn’t the default styled on. I’ve added the CA Certificate for the mysql client to find. But I’ve also added the following tag “MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT”. At this time of writing, it appears that there is still an issue with the server presenting a wrong hostname on the certificate. Though the product team is working on getting this issue fixed! Anyhow, with that tag, we’re telling the client to ignore issues on the hostname.

Before we load up this page, we’ll need to populate our newly created database first. So let’s do that one!  Let’s try to connect ;

Okay, first we’ll need to add our system to the firewall rules too… 🙂 Once that’s done, let’s try it again ;

Great! Now let’s create the database “billing”;

Switch to that database ;

And create a dummy table ; 

And what does our page say…

Eureka! Our connection was established… in an encrypted manner! 😉

 

Closing Thoughts

SSL Encryption on your database is another complexity. I can understand that people would like to avoid the additional hassle. Though you should not cheap out on this one! You are probably going to store sensitive (like, personal identifiable information) and sending that in an unencrypted manner is really a bad idea. So if you are thinking about that one…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s