Azure AD authentication for MySQL - Flexible Server from end-to-end

[Guest CyrilleVisser]

The Azure Database for MySQL team just announced General Availability of Azure Active Directory (Azure AD) authentication for Flexible Server. In this post, I’ll walk through how to configure applications to use Azure AD authentication. In general, this process involves:

 

• Creating a User-assigned Managed Identity.
  
• Creating an Azure AD administrator for MySQL – Flexible Server.
  
• Connecting to a MySQL flexible server from an App Service without using secrets.
  
• Modifying the application code by using the Azure Identity Library.
  

 

 

 

Create a User-assigned Managed Identity

 

 

Flexible Server leverages a User-assigned Managed Identity (UMI) to request access tokens. Creating a UIM requires that your account have the Managed Identity Contributor role. In addition, after you create the UIM, you’ll need to log in as a Global Administrator in Azure AD and assign the UMI the following Microsoft Graph permissions:

 

 

 

Permission	Allows access to AAD…
User.Read.All	User information
GroupMember.Read.All	Group information
Application.Read.All	Service principal (application) information

 

 

 

For more information, see Active Directory authentication - Azure Database for MySQL - Flexible Server.

 

 

 

1. Verify that your account is assigned the Managed Identity Contributor role.
    
    
   
2. To create the UIM, run the following command:
   

 

az identity create -g <RESOURCE GROUP> -n <USER ASSIGNED IDENTITY NAME>

 

1. Log in as a Global Administrator in AD, and then run the following PowerShell script:
   

 

$tenantid="provide the tenant ID"

 

$graphappid = "00000003-0000-0000-c000-000000000000"

 

$uminame="provide user managed identity name"

 

$permissions = "Directory.Read.All", "User.Read.All","Application.Read.All"

 

 

Connect-AzureAD -TenantId $tenantid

 

 

$umi = (Get-AzureADServicePrincipal -Filter "displayName eq '$uminame'")

 

$graphsp = Get-AzureADServicePrincipal -Filter "appId eq '$graphappid'"

 

$approles = $graphsp.AppRoles | Where-Object {$_.Value -in ($permissions) -and $_.AllowedMemberTypes -contains "Application"}

 

 

foreach ($approle in $approles)

 

{

 

New-AzureAdServiceAppRoleAssignment -ObjectId $umi.ObjectId -PrincipalId $umi.ObjectId -ResourceId $graphsp.ObjectId -Id $approle.Id

 

}

 

 

 

Create an Azure AD administrator for MySQL – Flexible Server

 

 

To create an Azure AD administrator for MySQL – Flexible Server, run the following command:

 

 

az mysql flexible-server ad-admin create -g <resource group> -s <FlexibleServer name> -u <mysql ad admin user> -i <mysql ad admin user object id> --identity <managed user identity>

 

 

The following example creates an Azure AD admin user “Cyrille” for the MySQL flexible server “myflex01.mysql.database.azure.com” and the UIM “mysqluseridentity”:

 

 

az mysql flexible-server ad-admin create -g "myrg" -s myflex01 -u

cyrille@cyrille.onmicrosoft.com

-i 000f0c00-c000-0ec0-0000-0a0eba000bd0 --identity "mysqluseridentity"

 

 

 

Connect to a MySQL flexible server from an App Service without secrets

 

 

To connect to a MySQL flexible server without using secrets, perform the following steps.

 

 

 

1. Enable a system managed identity for your App Service by running the following command:
   

 

az webapp identity assign --resource-group <group-name> --name <app-name> --output tsv --query principalId

 

The output is the application id of your App Service, which is required to create a MySQL user.

 

1. Sign into your MySQL flexible server using the full username of the Azure AD user (e.g., cyrille@cyrille.onmicrosoft.com).
    
   

 

# Get access token for MySQL with the Azure AD user

 

az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken

 

# Sign into the MySQL server using the token

 

mysql -h <server-name>.mysql.database.azure.com --user <aad-user-name> --enable-cleartext-plugin --password=<token-output-from-last-command>

 

1. To add the App Service system managed identity using the application id, run the following command:
    
   

 

SET aad_auth_validate_oids_in_tenant = OFF;

 

CREATE AADUSER '<mysql-user-name>' IDENTIFIED BY '<application-id-of-system-assigned-identity>';

 

Note

: The <mysql-user-name> can be any name, e.g., appserviceuser. Later in this procedure, you’ll need this username to include in a connection string.

 

1. To grant permissions, run the following command:
    
   

 

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO '<mysql-user-name>'@'%' WITH GRANT OPTION;

 

FLUSH PRIVILEGES;

 

 

Modifying the application code

 

 

In code, we’ll use the Azure Identity Library to provide Azure AD token authentication support for clients. We’ll use the DefaultAzureCredential object, which is flexible enough to adapt to both the development environment and the Azure environment. When it’s running locally, DefaultAzureCredential can retrieve the logged-in Azure user from the environment of your choice (Visual Studio, Visual Studio Code, Azure CLI, or Azure PowerShell). When running in Azure, it retrieves the system managed identity.

 

 

 

The pattern is as follows:

 

 

1. Verify that your Dev environment is configured correctly. For more information, see Azure Identity client library for .NET - Azure for .NET Developers.
    
    
   
2. Install the following packages:
   
   • dotnet add package Azure.Identity
     
   • dotnet add package MySql.Data
      
     

[*]Instantiate a DefaultAzureCredential from the Azure Identity client library by specifying the client ID of the user managed identity.

 

 

[*]Get an access token for the resource https://ossrdbms-aad.database.windows.net.

 

 

[*]Add the token to your connection string.

 

 

[*]Open the connection.

 

The following example demonstrates authenticating to MySQL flexible server using an Azure AD token:

 

DefaultAzureCredential credential = null;

 

// if identity endpoint is present we will use the managed identity

 

string user;

 

if (String.IsNullOrEmpty(Environment.GetEnvironmentVariable("IDENTITY_ENDPOINT")))

 

{

 

// use user assigned managed identity to obtain token for the AAD user

 

credential = new DefaultAzureCredential(

 

new DefaultAzureCredentialOptions

 

{

 

ManagedIdentityClientId = "<user managed identity client id>",

 

TenantId = "<tenant id>"

 

});

 

user = "<aad user>";

 

}

 

else

 

{

 

// use managed identity

 

credential = new DefaultAzureCredential();

 

user = "<mysql managed identity user>";

 

}

 

}

 

// Get token for Azure Database for MySQL

 

AccessToken token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "

https://ossrdbms-aad.database.windows.net"

}));

 

 

// Add the token to the MySQL connection

 

string connectionString = "Server=mysql0002.mysql.database.azure.com;" +

 

"Port=3306;" +

 

"SslMode=Required;" +

 

"Database=test;" +

 

"Uid=" + user + ";" +

 

"Password=" + token.Token;

 

 

MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);

 

 

connection.Open();

 

Conclusion

 

 

You're now ready to use Azure AD authentication to develop and debug your applications running MySQL – Flexible Server as the back end. When running in App Service, the code uses the app's managed identity to connect to the database on the MySQL flexible server.

 

 

 

If you have any questions or suggestions, feel free to add them in the Comments section below. Thank you!

 

Continue reading...