Access Azure Database for MySQL from Azure functions with SSL Certificate Verification

Recently I got a customer who has relatively small volume data to be managed. So, I suggested the Azure Database for MySQL. Basically, the smallest instance cost about $560 AUD per month will be enough. Also, because this is DBaaS, the customer does not need to hire more people to maintain it.

The customer also wants to automate the ETL processes to this MySQL DB. Although I think Data Factory would be the ideal solution, the cost can be a big issue because the ETL has to be done every 5 minutes which will trigger the orchestration too frequently and cause a “shock” bill. Therefore, I suggest using Azure Functions which is a serverless computing solution to do this ETL. Another reason is that the data transformation is not too complicated and will rarely be changed once done.

However, I met an issue when I use python to implement this Azure function to connect to the Azure MySQL server. That is, the MySQL has enforced the SSL encryption, but the Azure Function side doesn’t provide a certificate. In our case, disabling the SSL enforcement is not an option because we don’t want to put customer’s data in a risky situation such as man-in-the-middle attacks. Therefore, I’ll need to solve this problem and finally, I did it. Here are the steps to reproduce the issue and how to solve it.

Step 1: Create Azure Database for MySQL

Go to your Azure subscription and click Create a resource -> search for Azure Database for MySQL -> Create

In the Create MySQL server page, fill in the resource group, server name and other required fields. In this example, I choose MySQL v8.0, then click Review + create -> Create

To be able to test your Azure Functions on your local machine. Don’t forget to add your local IP address to the connection security settings. Specifically, Go to the Azure MySQL we just created. On the left navigation select Connection security in Settings section. Then on the right main view click + Add client IP button. This will automatically add your current IP address to whitelist. Then, click Save.

Step 2: Create Azure Functions

I prefer to create Azure Functions in VS code for the simplicity of development. You’ll need to install the Azure Functions extension for VS code. Once you’re done, you should be able to see the extra Azure button on the left side. Click the button and follow the instructions to log in to your Azure account.

Press cmd+shit+p on Mac or ctrl+shift+p on Windows to call the command palette then choose Create New Project

In this example, I’ll use python as the development language.

Then, choose HTTP trigger because it is easier for testing purpose.

Then, complete the rest steps for the instructions.

Step 3: Install mysql-connector for Python

In your VS code terminal, using pip to install the mysql-connector library for Python.

$ pip install mysql-connector

Also, don’t forget to add the dependencies to requirements.txt, as Azure Functions rely on this file to install Python dependencies for your app.

Step 4: Write Python code to connect to the MySQL server

Go to the Azure Database for MySQL that you just created. Note down the server name and login name.

The code is simply connecting to the MySQL server and show all the databases.

import logging
import azure.functions as funcimport mysql.connectordef main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')    # Connect to MySQL
    cnx = mysql.connector.connect(
        user="ctao@mysql80-test-chris", 
        password='<your_password>', 
        host="mysql80-test-chris.mysql.database.azure.com", 
        port=3306
    )
    logging.info(cnx)    # Show databases
    cursor = cnx.cursor()
    cursor.execute("SHOW DATABASES")
    result_list = cursor.fetchall()
    # Build result response text
    result_str_list = []
    for row in result_list:
        row_str = ', '.join([str(v) for v in row])
        result_str_list.append(row_str)
    result_str = '\n'.join(result_str_list)    return func.HttpResponse(
        result_str,
        status_code=200
    )

Step 5: Run Azure Function (Reproducing the problem)

You can either deploy the Azure Function to test it in a production environment or run the following command in VS code terminal to test it locally.

$ func start run

Once the Azure Function is running, access the URL to try to get the list of databases.

If you are running this locally, the URL should be as follows

http://localhost:7071/api/<your_app_name>

Unsurprisingly, your browser will complain of an HTTP error 500. If you go back to your VS code terminal, you’ll see some error messages in the console. The key to this issue is highlighted in the image

Exception: ProgrammingError: 9002 (28000): SSL connection is required. Please specify SSL options and retry.

Step 6: Add Certificate File and Correctly Use it in Python

Honestly, this issue bothered me for a while, But I solved it eventually.

Firstly, It is not difficult the get the certificate file. You can download it from Microsoft Azure official documentation website, on this page. I’ll post the download link directly here:

https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

It is also not difficult to refer to it in Python. Usually, you can use either relative path:

crtpath = '../BaltimoreCyberTrustRoot.crt.pem'

or an absolute path:

crtpath = '/User/.../BaltimoreCyberTrustRoot.crt.pem'

Then, just simply put the path variable in the connect function:

# Connect to MySQL
cnx = mysql.connector.connect(
    user="ctao@azure-mysql-test", 
    password='<your_password>', 
    host="azure-mysql-test.mysql.database.azure.com", 
    port=3306,
    ssl_ca=crtpath
)

However, this will NOT work after you deploy the Azure Function.

This is because Azure Function will automatically deploy your code somewhere under /var on the actual back-end server, and you don’t have access to this server at all.

In this case, we have to let Python find the correct location of the cert file, and that’s definitely doable. We can use __file__.

Let’s put the cert file in the root path of your project. So, if later on we have more Azure Functions and want to reuse this cert file, it makes sure we can reference it in the right way.

Then, write a function to get this file

import pathlibdef get_ssl_cert():
    current_path = pathlib.Path(__file__).parent.parent
    return str(current_path / 'BaltimoreCyberTrustRoot.crt.pem')

Note that the first .parent will return the root path of the Azure Function, and we need another .parent to make sure it returns the root path of the project. That means, you only need one .parent if you prefer to put the cert file in the root path of your Azure Function.

After that, change the connecting code as follows:

cnx = mysql.connector.connect(
    user="ctao@mysql80-test-chris", 
    password='<your_password>', 
    host="mysql80-test-chris.mysql.database.azure.com", 
    port=3306,
    ssl_ca=get_ssl_cert()
)

Let’s try it now!

Access the URL again, you will be able to see the list of databases!

This will work both on your local machine and after deployed your Azure Functions.

This article was originally published on Towards Data Science:
https://towardsdatascience.com/access-azure-database-for-mysql-from-azure-functions-with-ssl-certification-verification-b6c6784f76fb