Connect Azure App Service to Azure database for MySQL and PostgreSQL via SSL

mksunitha 5/10/2017 8:10:26 AM

---

Azure Database for MySQL (Preview) and Azure Database for PostgreSQL  (Preview), both services support Secure Sockets Layer (SSL)  encryption. By default if you create a MySQL or PostgreSQL server using this server SSL is required to connect to all databases on that server. With Web Apps , the application needs to provide the certificate authority (CA) is one that the client trusts for the connection to succeed. This involves a few steps . Follow the steps below to add SSL to an existing application on Azure App Service.

Note: These instructions are same whether you are using Windows or Linux based Azure App Service.

1. Follow the steps in this article on configuring SSL on Azure database for MySQL or PostgreSQL.
2. Create a bin folder under D:/home/site/wwwroot and place the certificate (.pem file generated from step #1) in bin folder.  You can do this directly by accessing  the file server for web app using FTP or Git or Kudu .
3. Log in to Azure portal.
4. Select your existing Web app and click on Application settings. Add the following app setting for your web app: For MYSQL : For PostgreSQL :
5. Now SSL certificate is added to your web application . Your application code needs to be updated to use this certificate authority when connecting to the database.

Please refer to the documentation of your application framework on how to consume the certificate authority to connect to the database via SSL. Here are a few examples below.

Connect to MySQL server with SSL for WordPress app

Add the following to wp-config.php to connect to the MySQL database via SSL

define('MYSQL_CLIENT_FLAGS', MYSQL_CLIENT_SSL);
define( 'MYSQL_SSL_CA', getenv('MYSQL_SSL_CA'));

Note:

If you are using PHP 7.X version , you need another flag  MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT for any PHP based application. For example with WordPress , you need to update MYSQL_CLIENT_FLAGS as shown below 

define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT ); 

Connect to PostgreSQL server with SSL for Drupal app

Add the following to site/default/settings.php  and add PDO options for SSL

$databases = array (
  'default' => 
  array (
    'default' =
    array (
      'database' => 'databasename',
      'username' => 'username',
      'password' => 'password',
      'host' => 'hostname',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array(
        PDO::PGSQL_ATTR_SSL_CA => getenv('POSTGRESQL_SSL_CA'),
     ), 
   ),
 );

Connect to MySQL server with SSL for Drupal app

Add the following to site/default/settings.php  and add PDO options for SSL

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'databasename',
      'username' => 'username',
      'password' => 'password',
      'host' => 'hostname',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array(
        PDO::MYSQL_ATTR_SSL_CA => getenv('MYSQL_SSL_CA'), 
       ), 
     ),
  );

Connect to PostgreSQL server with SSL for Django app

Add the following to settings.py  and the options for SSL 

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'dbuser',
        'PASSWORD': 'dbpassword',
        'HOST': 'dbhost',
        'OPTIONS': {
            'sslmode': 'require',
            'ca':os.environ.get('POSTGRESQL_SSL_CA', '')
        },
    },
}