This article is contributed. See the original author and article here.

Managed Identity are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code. You can learn more about this in the following document: how to connect with Managed Identity to Azure Database for MySQL 

 

The blog will outline how to use Function App System Managed Identity to connect to Azure Database for MySQL.

 

  • Step 1: Configure Azure AD Authentication for MySQL
  • Step 2:Enable Managed Identity for the Function App
  • Step 3: Find the Managed Identity GUID and then create a user in MySQL
  • Step 4: Writing code for function app
  • Step 5: Test the function app

 

Step 1: Configure Azure AD Authentication for MySQL

First, we need to make sure that the Azure Database for MySQL is configured for Azure AD authentication. If your server is not already configured, follow the steps in this how-to guide: Use Azure Active Directory for authentication with MySQL

 

Step 2: Enable Managed Identity for the Function App

Next, enable Managed identify for a Function app. For this you need to log in to the Azure Portal and then select the Function App which you will be using. Select Identity under Settings. Change the Status to On.

 

1.png

 

Step 3: Find the Managed Identity GUID and then create a user in MySQL

Next, create a MySQL user for your Managed Identity. For this we need to get the application ID. For that, go to Azure Active Directory from the Azure portal and then go to Enterprise Applications.

 

2.png

 

Once you select the application, you can will be able to copy the Application ID.

 

3.png

Once you have the Application ID, log in to Azure Database for MySQL using the AAD login and execute the below query. This query ensures the managed identity now has access when authenticating with the username myuser. Replace the Application ID (GUID) in the query with your own. 

 

 

SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER 'myuser' IDENTIFIED BY 'cc5e6297-6bdc-4608-9ab5-ddb0c81e2a7e';
--I would also recommend to GRANTS necessary permission in DB
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

 

 

Step 4: Writing code for the Function app

Next, let’s see how to get an access token using the Function app System managed identity and use it to call Azure Database for MySQL. Azure Database for MySQL natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. When creating a connection to MySQL, you pass the access token in the password field.

 

Here’s a .NET code example of opening a connection to MySQL using an access token. This code must run on the Function App to access the Function App system-assigned managed identity’s endpoint. .NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method.

 

Please note the community MySQL Connector/NET (MySql.Data)does not support cleartext plugin. You can use MySqlConnector instead. 

 

The below is a sample code. Replace the values of “Servername”, “User”, and “Database” with your own server credentials. 

 

 

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using MySqlConnector;
using Microsoft.Azure.Services.AppAuthentication;

namespace myfunappmysqlapp1
{
    public static class mysqlapp
    {
        private static string Host = "<Servername>.mysql.database.azure.com";
        private static string User = "myuser@<Servername>";
        private static string mydatabase = "db";
        [FunctionName("mysqlapp")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");

            /*Gettting the Token*/
            var sqlServerTokenProvider = new AzureServiceTokenProvider();
            var SqlAccessToken = await sqlServerTokenProvider.GetAccessTokenAsync("https://ossrdbms-aad.database.windows.net");
            //log.LogInformation(SqlAccessToken); /*For troubleshooting in case you need to print the token */
            log.LogInformation("Connecting to database.");

            //
            // Open a connection to the MySQL server using the access token.
            //
            var builder = new MySqlConnectionStringBuilder
            {
                Server = Host,
                Database = mydatabase,
                UserID = User,
                Password = SqlAccessToken,
                SslMode = MySqlSslMode.Required,

            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                log.LogInformation("Opening connection using access token...");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT VERSION()";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            log.LogInformation("nConnected!nnMySQL version: {0}", reader.GetString(0));
responseMessage = reader.GetString(0);
                        }
                    }
                }
            }
            
             responseMessage = "The versions is Azure Database for Mysql is:" + responseMessage;
            return new OkObjectResult(responseMessage);
        }
    }
}

 

 

 

Step 5: Test the Function app

Once you publish the Application, you can test the with the following steps below. 

From the Azure Portal, go to Function App you published and select Functions.

4.png

Once the Function is selected, you can select Code+Test and click on Test/Run.

5.png

Click on Run on the on the pop up window

7.png

You can see the output in terminal for App Insights.

output_mysql.jpg

 

We can see that we connected to the database and could successfully run the query.

 

Hope this helps!

 

Sudheesh Narayanaswamy

Technical Support Engineer

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: