Muokkaa

Jaa

Migrate an application to use passwordless connections with Azure Database for MySQL

  • Artikkeli

This article explains how to migrate from traditional authentication methods to more secure, passwordless connections with Azure Database for MySQL.

Application requests to Azure Database for MySQL must be authenticated. Azure Database for MySQL provides several different ways for apps to connect securely. One of the ways is to use passwords. However, you should prioritize passwordless connections in your applications when possible.

Compare authentication options

When the application authenticates with Azure Database for MySQL, it provides a username and password pair to connect to the database. Depending on where the identities are stored, there are two types of authentication: Microsoft Entra authentication and MySQL authentication.

Microsoft Entra authentication

Microsoft Entra authentication is a mechanism for connecting to Azure Database for MySQL using identities defined in Microsoft Entra ID. With Microsoft Entra authentication, you can manage database user identities and other Microsoft services in a central location, which simplifies permission management.

Using Microsoft Entra ID for authentication provides the following benefits:

  • Authentication of users across Azure Services in a uniform way.
  • Management of password policies and password rotation in a single place.
  • Multiple forms of authentication supported by Microsoft Entra ID, which can eliminate the need to store passwords.
  • Customers can manage database permissions using external (Microsoft Entra ID) groups.
  • Microsoft Entra authentication uses MySQL database users to authenticate identities at the database level.
  • Support of token-based authentication for applications connecting to Azure Database for MySQL.

MySQL authentication

You can create accounts in MySQL. If you choose to use passwords as credentials for the accounts, these credentials will be stored in the user table. Because these passwords are stored in MySQL, you need to manage the rotation of the passwords by yourself.

Although it's possible to connect to Azure Database for MySQL with passwords, you should use them with caution. You must be diligent to never expose the passwords in an unsecure location. Anyone who gains access to the passwords is able to authenticate. For example, there's a risk that a malicious user can access the application if a connection string is accidentally checked into source control, sent through an unsecure email, pasted into the wrong chat, or viewed by someone who shouldn't have permission. Instead, consider updating your application to use passwordless connections.

Introducing passwordless connections

With a passwordless connection, you can connect to Azure services without storing any credentials in the application code, its configuration files, or in environment variables.

Many Azure services support passwordless connections, for example via Azure Managed Identity. These techniques provide robust security features that you can implement using DefaultAzureCredential from the Azure Identity client libraries. In this tutorial, you'll learn how to update an existing application to use DefaultAzureCredential instead of alternatives such as connection strings.

DefaultAzureCredential supports multiple authentication methods and automatically determines which should be used at runtime. This approach enables your app to use different authentication methods in different environments (local dev vs. production) without implementing environment-specific code.

The order and locations in which DefaultAzureCredential searches for credentials can be found in the Azure Identity library overview. For example, when working locally, DefaultAzureCredential will generally authenticate using the account the developer used to sign in to Visual Studio. When the app is deployed to Azure, DefaultAzureCredential will automatically switch to use a managed identity. No code changes are required for this transition.

To ensure that connections are passwordless, you must take into consideration both local development and the production environment. If a connection string is required in either place, then the application isn't passwordless.

In your local development environment, you can authenticate with Azure CLI, Azure PowerShell, Visual Studio, or Azure plugins for Visual Studio Code or IntelliJ. In this case, you can use that credential in your application instead of configuring properties.

When you deploy applications to an Azure hosting environment, such as a virtual machine, you can assign managed identity in that environment. Then, you won't need to provide credentials to connect to Azure services.

Note

A managed identity provides a security identity to represent an app or service. The identity is managed by the Azure platform and does not require you to provision or rotate any secrets. You can read more about managed identities in the overview documentation.

Migrate an existing application to use passwordless connections

The following steps explain how to migrate an existing application to use passwordless connections instead of a password-based solution.

0) Prepare the working environment

First, use the following command to set up some environment variables.

export AZ_RESOURCE_GROUP=<YOUR_RESOURCE_GROUP>
export AZ_DATABASE_SERVER_NAME=<YOUR_DATABASE_SERVER_NAME>
export AZ_DATABASE_NAME=demo
export AZ_MYSQL_AD_NON_ADMIN_USERNAME=<YOUR_AZURE_AD_NON_ADMIN_USER_DISPLAY_NAME>
export AZ_MYSQL_AD_MI_USERNAME=<YOUR_AZURE_AD_MI_DISPLAY_NAME>
export AZ_USER_IDENTITY_NAME=<YOUR_USER_ASSIGNED_MANAGEMED_IDENTITY_NAME>
export CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName --output tsv)
export CURRENT_USER_OBJECTID=$(az ad signed-in-user show --query id --output tsv)

Replace the placeholders with the following values, which are used throughout this article:

  • <YOUR_RESOURCE_GROUP>: The name of the resource group your resources are in.
  • <YOUR_DATABASE_SERVER_NAME>: The name of your MySQL server, which should be unique across Azure.
  • <YOUR_AZURE_AD_NON_ADMIN_USER_DISPLAY_NAME>: The display name of your Microsoft Entra non-admin user. Make sure the name is a valid user in your Microsoft Entra tenant.
  • <YOUR_AZURE_AD_MI_DISPLAY_NAME>: The display name of Microsoft Entra user for your managed identity. Make sure the name is a valid user in your Microsoft Entra tenant.
  • <YOUR_USER_ASSIGNED_MANAGEMED_IDENTITY_NAME>: The name of your user-assigned managed identity server, which should be unique across Azure.

1) Configure Azure Database for MySQL

1.1) Enable Microsoft Entra ID-based authentication

To use Microsoft Entra ID access with Azure Database for MySQL, you should set the Microsoft Entra admin user first. Only a Microsoft Entra Admin user can create/enable users for Microsoft Entra ID-based authentication.

If you're using Azure CLI, run the following command to make sure it has sufficient permission:

az login --scope https://graph.microsoft.com/.default

Run the following command to the create user identity for assigning:

az identity create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_USER_IDENTITY_NAME

Important

After creating the user-assigned identity, ask your Global Administrator or Privileged Role Administrator to grant the following permissions for this identity: User.Read.All, GroupMember.Read.All, and Application.Read.ALL. For more information, see the Permissions section of Active Directory authentication.

Run the following command to assign the identity to the MySQL server for creating the Microsoft Entra admin:

az mysql flexible-server identity assign \
    --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME \
    --identity $AZ_USER_IDENTITY_NAME

Then, run following command to set the Microsoft Entra admin:

az mysql flexible-server ad-admin create \
    --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME \
    --display-name $CURRENT_USERNAME \
    --object-id $CURRENT_USER_OBJECTID \
    --identity $AZ_USER_IDENTITY_NAME

This command will set the Microsoft Entra admin to the current signed-in user.

Note

You can only create one Microsoft Entra admin per MySQL server. Selection of another one will overwrite the existing Microsoft Entra admin configured for the server.

2) Configure Azure Database for MySQL for local development

2.1) Configure a firewall rule for local IP

Azure Database for MySQL instances are secured by default. They have a firewall that doesn't allow any incoming connection.

You can skip this step if you're using Bash because the flexible-server create command already detected your local IP address and set it on MySQL server.

If you're connecting to your MySQL server from Windows Subsystem for Linux (WSL) on a Windows computer, you need to add the WSL host ID to your firewall. Obtain the IP address of your host machine by running the following command in WSL:

cat /etc/resolv.conf

Copy the IP address following the term nameserver, then use the following command to set an environment variable for the WSL IP address:

export AZ_WSL_IP_ADDRESS=<the-copied-IP-address>

Then, use the following command to open the server's firewall to your WSL-based app:

az mysql server firewall-rule create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_SERVER_NAME-database-allow-local-ip-wsl \
    --server $AZ_DATABASE_SERVER_NAME \
    --start-ip-address $AZ_WSL_IP_ADDRESS \
    --end-ip-address $AZ_WSL_IP_ADDRESS \
    --output tsv

2.2) Create a MySQL non-admin user and grant permission

Next, create a non-admin Microsoft Entra user and grant all permissions on the $AZ_DATABASE_NAME database to it. You can change the database name $AZ_DATABASE_NAME to fit your needs.

Create a SQL script called create_ad_user.sql for creating a non-admin user. Add the following contents and save it locally:

export AZ_MYSQL_AD_NON_ADMIN_USERID=$(az ad signed-in-user show --query id --output tsv)

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '$AZ_MYSQL_AD_NON_ADMIN_USERNAME' IDENTIFIED BY '$AZ_MYSQL_AD_NON_ADMIN_USERID';
GRANT ALL PRIVILEGES ON $AZ_DATABASE_NAME.* TO '$AZ_MYSQL_AD_NON_ADMIN_USERNAME'@'%';
FLUSH privileges;
EOF

Then, use the following command to run the SQL script to create the Microsoft Entra non-admin user:

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com --user $CURRENT_USERNAME --enable-cleartext-plugin --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken) < create_ad_user.sql

Now use the following command to remove the temporary SQL script file:

rm create_ad_user.sql

Note

You can read more detailed information about creating MySQL users in Create users in Azure Database for MySQL.

3) Sign in and migrate the app code to use passwordless connections

For local development, make sure you're authenticated with the same Microsoft Entra account you assigned the role to on your MySQL. You can authenticate via the Azure CLI, Visual Studio, Azure PowerShell, or other tools such as IntelliJ.

Sign in to Azure through the Azure CLI by using the following command:

az login

Next, use the following steps to update your code to use passwordless connections. Although conceptually similar, each language uses different implementation details.

  1. Inside your project, add the following reference to the azure-identity-extensions package. This library contains all of the entities necessary to implement passwordless connections.

    <dependency>
    <groupId>com.azure</groupId>
    <artifactId>azure-identity-extensions</artifactId>
    <version>1.0.0</version>
</dependency>

  2. Enable the Azure MySQL authentication plugin in the JDBC URL. Identify the locations in your code that currently create a java.sql.Connection to connect to Azure Database for MySQL. Update url and user in your application.properties file to match the following values:

    url=jdbc:mysql://$AZ_DATABASE_SERVER_NAME.mysql.database.azure.com:3306/$AZ_DATABASE_NAME?serverTimezone=UTC&sslMode=REQUIRED&defaultAuthenticationPlugin=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin&authenticationPlugins=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin
user=$AZ_MYSQL_AD_NON_ADMIN_USERNAME

    Note

    If you're using the MysqlConnectionPoolDataSource class as the datasource in your application, be sure to remove defaultAuthenticationPlugin=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin from the URL.

    url=jdbc:mysql://$AZ_DATABASE_SERVER_NAME.mysql.database.azure.com:3306/$AZ_DATABASE_NAME?serverTimezone=UTC&sslMode=REQUIRED&authenticationPlugins=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin
user=$AZ_MYSQL_AD_NON_ADMIN_USERNAME

  3. Replace the one $AZ_DATABASE_SERVER_NAME variable, one $AZ_DATABASE_NAME variable and one $AZ_MYSQL_AD_NON_ADMIN_USERNAME variable with the values that you configured at the beginning of this article.

  4. Remove the password from the JDBC URL.

Run the app locally

After making these code changes, run your application locally. The new configuration should pick up your local credentials if you're signed in to a compatible IDE or command line tool, such as the Azure CLI, Visual Studio, or IntelliJ. The roles you assigned to your local dev user in Azure will allow your app to connect to the Azure service locally.

4) Configure the Azure hosting environment

After your application is configured to use passwordless connections and it runs locally, the same code can authenticate to Azure services after it's deployed to Azure. For example, an application deployed to an Azure App Service instance that has a managed identity assigned can connect to Azure Storage.

In this section, you'll execute two steps to enable your application to run in an Azure hosting environment in a passwordless way:

  • Assign the managed identity for your Azure hosting environment.
  • Assign roles to the managed identity.

Note

Azure also provides Service Connector, which can help you connect your hosting service with PostgreSQL. With Service Connector to configure your hosting environment, you can omit the step of assigning roles to your managed identity because Service Connector will do it for you. The following section describes how to configure your Azure hosting environment in two ways: one via Service Connector and the other by configuring each hosting environment directly.

Important

Service Connector's commands require Azure CLI 2.41.0 or higher.

Assign the managed identity using the Azure portal

The following steps show you how to assign a system-assigned managed identity for various web hosting services. The managed identity can securely connect to other Azure Services using the app configurations you set up previously.

  1. On the main overview page of your Azure App Service instance, select Identity from the navigation pane.

  2. On the System assigned tab, make sure to set the Status field to on. A system assigned identity is managed by Azure internally and handles administrative tasks for you. The details and IDs of the identity are never exposed in your code.

You can also assign managed identity on an Azure hosting environment by using the Azure CLI.

You can assign a managed identity to an Azure App Service instance with the az webapp identity assign command, as shown in the following example:

export AZ_MI_OBJECT_ID=$(az webapp identity assign \
    --resource-group $AZ_RESOURCE_GROUP \
    --name <service-instance-name> \
    --query principalId \
    --output tsv)

Assign roles to the managed identity

Next, grant permissions to the managed identity you assigned to access your MySQL instance.

These steps will create a Microsoft Entra user for the managed identity and grant all permissions for the database $AZ_DATABASE_NAME to it. You can change the database name $AZ_DATABASE_NAME to fit your needs.

First, create a SQL script called create_ad_user.sql for creating a non-admin user. Add the following contents and save it locally:

export AZ_MYSQL_AD_MI_USERID=$(az ad sp show --id $AZ_MI_OBJECT_ID --query appId --output tsv)

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '$AZ_MYSQL_AD_MI_USERNAME' IDENTIFIED BY '$AZ_MYSQL_AD_MI_USERID';
GRANT ALL PRIVILEGES ON $AZ_DATABASE_NAME.* TO '$AZ_MYSQL_AD_MI_USERNAME'@'%';
FLUSH privileges;
EOF

Then, use the following command to run the SQL script to create the Microsoft Entra non-admin user:

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com --user $CURRENT_USERNAME --enable-cleartext-plugin --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken) < create_ad_user.sql

Now use the following command to remove the temporary SQL script file:

rm create_ad_user.sql

Test the app

Before deploying the app to the hosting environment, you need to make one more change to the code because the application is going to connect to MySQL using the user created for the managed identity.

Update your code to use the user created for the managed identity:

properties.put("user", "$AZ_MYSQL_AD_MI_USERNAME");

After making these code changes, you can build and redeploy the application. Then, browse to your hosted application in the browser. Your app should be able to connect to the MySQL database successfully. Keep in mind that it may take several minutes for the role assignments to propagate through your Azure environment. Your application is now configured to run both locally and in a production environment without the developers having to manage secrets in the application itself.

Next steps

In this tutorial, you learned how to migrate an application to passwordless connections.

You can read the following resources to explore the concepts discussed in this article in more depth: