Tutorial: Connect to Azure databases from App Service without secrets using a managed identity

App Service provides a highly scalable, self-patching web hosting service in Azure. It also provides a managed identity for your app, which is a turn-key solution for securing access to Azure databases, including:

Note

This tutorial doesn't include guidance for Azure Cosmos DB, which supports Azure Active Directory authentication differently. For more information, see the Azure Cosmos DB documentation, such as Use system-assigned managed identities to access Azure Cosmos DB data.

Managed identities in App Service make your app more secure by eliminating secrets from your app, such as credentials in the connection strings. This tutorial shows you how to connect to the above-mentioned databases from App Service using managed identities.

What you will learn:

  • Configure an Azure AD user as an administrator for your Azure database.
  • Connect to your database as the Azure AD user.
  • Configure a system-assigned or user-assigned managed identity for an App Service app.
  • Grant database access to the managed identity.
  • Connect to the Azure database from your code (.NET Framework 4.8, .NET 6, Node.js, Python, Java) using a managed identity.
  • Connect to the Azure database from your development environment using the Azure AD user.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prerequisites

  • Create an app in App Service based on .NET, Node.js, Python, or Java.
  • Create a database server with Azure SQL Database, Azure Database for MySQL, or Azure Database for PostgreSQL.
  • You should be familiar with the standard connectivity pattern (with username and password) and be able to connect successfully from your App Service app to your database of choice.

Prepare your environment for the Azure CLI.

1. Grant database access to Azure AD user

First, enable Azure Active Directory authentication to the Azure database by assigning an Azure AD user as the administrator of the server. For the scenario in the tutorial, you'll use this user to connect to your Azure database from the local development environment. Later, you set up the managed identity for your App Service app to connect from within Azure.

Note

This user is different from the Microsoft account you used to sign up for your Azure subscription. It must be a user that you created, imported, synced, or invited into Azure AD. For more information on allowed Azure AD users, see Azure AD features and limitations in SQL Database.

  1. If your Azure AD tenant doesn't have a user yet, create one by following the steps at Add or delete users using Azure Active Directory.

  2. Find the object ID of the Azure AD user using the az ad user list and replace <user-principal-name>. The result is saved to a variable.

    azureaduser=$(az ad user list --filter "userPrincipalName eq '<user-principal-name>'" --query [].id --output tsv)
    
  1. Add this Azure AD user as an Active Directory administrator using az sql server ad-admin create command in the Cloud Shell. In the following command, replace <group-name> and <server-name> with your own parameters.

    az sql server ad-admin create --resource-group <group-name> --server-name <server-name> --display-name ADMIN --object-id $azureaduser
    

    For more information on adding an Active Directory administrator, see Provision an Azure Active Directory administrator for your server

2. Configure managed identity for app

Next, you configure your App Service app to connect to SQL Database with a managed identity.

  1. Enable a managed identity for your App Service app with the az webapp identity assign command in the Cloud Shell. In the following command, replace <app-name>.

    az webapp identity assign --resource-group <group-name> --name <app-name>
    

    Note

    To enable managed identity for a deployment slot, add --slot <slot-name> and use the name of the slot in <slot-name>.

  2. The identity needs to be granted permissions to access the database. In the Cloud Shell, sign in to your database with the following command. Replace <server-name> with your server name, <database-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Azure AD user's credentials from 1. Grant database access to Azure AD user.

    sqlcmd -S <server-name>.database.windows.net -d <database-name> -U <aad-user-name> -P "<aad-password>" -G -l 30
    
  3. Run the following database commands to grant the permissions your app needs. For example,

    CREATE USER [<app-name>] FROM EXTERNAL PROVIDER;
    ALTER ROLE db_datareader ADD MEMBER [<app-name>];
    ALTER ROLE db_datawriter ADD MEMBER [<app-name>];
    ALTER ROLE db_ddladmin ADD MEMBER [<app-name>];
    GO
    

    For a deployment slot, use <app-name>/slots/<slot-name> instead of <app-name>.

3. Modify your code

In this section, connectivity to the Azure database in your code follows the DefaultAzureCredential pattern for all language stacks. DefaultAzureCredential is flexible enough to adapt to both the development environment and the Azure environment. When running locally, it 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 managed identity. So it's possible to have connectivity to database both at development time and in production. The pattern is as follows:

  1. Instantiate a DefaultAzureCredential from the Azure Identity client library. If you're using a user-assigned identity, specify the client ID of the identity.
  2. Get an access token for the resource URI respective to the database type.
    • For Azure SQL Database: https://database.windows.net/.default
    • For Azure Database for MySQL: https://ossrdbms-aad.database.windows.net
    • For Azure Database for PostgreSQL: https://ossrdbms-aad.database.windows.net
  3. Add the token to your connection string.
  4. Open the connection.

For Azure Database for MySQL and Azure Database for PostgreSQL, the database username that you created in 2. Configure managed identity for app is also required in the connection string.

  1. In Visual Studio, open the Package Manager Console and add the NuGet packages you need:

    Install-Package Azure.Identity
    Install-Package System.Data.SqlClient
    
  2. Connect to the Azure database by adding an access token. If you're using a user-assigned identity, make sure you uncomment the applicable lines.

    // Uncomment one of the two lines depending on the identity type
    //var credential = new Azure.Identity.DefaultAzureCredential(); // system-assigned identity
    //var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
    
    // Get token for Azure SQL Database
    var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
    
    // Add the token to the SQL connection
    var connection = new System.Data.SqlClient.SqlConnection("Server=tcp:<server-name>.database.windows.net;Database=<database-name>;TrustServerCertificate=True");
    connection.AccessToken = token.Token;
    
    // Open the SQL connection
    connection.Open();
    

    For a more detailed tutorial, see Tutorial: Connect to SQL Database from .NET App Service without secrets using a managed identity.

4. Set up your dev environment

This sample code uses DefaultAzureCredential to get a useable token for your Azure database from Azure Active Directory and then adds it to the database connection. While you can customize DefaultAzureCredential, it's already versatile by default. It gets a token from the signed-in Azure AD user or from a managed identity, depending on whether you run it locally in your development environment or in App Service.

Without any further changes, your code is ready to be run in Azure. To debug your code locally, however, your develop environment needs a signed-in Azure AD user. In this step, you configure your environment of choice by signing in with your Azure AD user.

  1. Visual Studio for Windows is integrated with Azure AD authentication. To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and select Sign in or Add.

  2. To set the Azure AD user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. Select the Azure AD user you added and select OK.

For more information about setting up your dev environment for Azure Active Directory authentication, see Azure Identity client library for .NET.

You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.

5. Test and publish

  1. Run your code in your dev environment. Your code uses the signed-in Azure AD user) in your environment to connect to the back-end database. The user can access the database because it's configured as an Azure AD administrator for the database.

  2. Publish your code to Azure using the preferred publishing method. In App Service, your code uses the app's managed identity to connect to the back-end database.

Frequently asked questions

Does managed identity support SQL Server?

Azure Active Directory and managed identities aren't supported for on-premises SQL Server.

I get the error Login failed for user '<token-identified principal>'.

The managed identity you're attempting to request a token for is not authorized to access the Azure database.

I made changes to App Service authentication or the associated app registration. Why do I still get the old token?

The back-end services of managed identities also maintain a token cache that updates the token for a target resource only when it expires. If you modify the configuration after trying to get a token with your app, you don't actually get a new token with the updated permissions until the cached token expires. The best way to work around this is to test your changes with a new InPrivate (Edge)/private (Safari)/Incognito (Chrome) window. That way, you're sure to start from a new authenticated session.

How do I add the managed identity to an Azure AD group?

If you want, you can add the identity to an Azure AD group, then grant access to the Azure AD group instead of the identity. For example, the following commands add the managed identity from the previous step to a new group called myAzureSQLDBAccessGroup:

groupid=$(az ad group create --display-name myAzureSQLDBAccessGroup --mail-nickname myAzureSQLDBAccessGroup --query objectId --output tsv)
msiobjectid=$(az webapp identity show --resource-group <group-name> --name <app-name> --query principalId --output tsv)
az ad group member add --group $groupid --member-id $msiobjectid
az ad group member list -g $groupid

To grant database permissions for an Azure AD group, see documentation for the respective database type.

I get the error mysql: unknown option '--enable-cleartext-plugin'.

If you're using a MariaDB client, the --enable-cleartext-plugin option isn't required.

I get the error SSL connection is required. Please specify SSL options and retry.

Connecting to the Azure database requires additional settings and is beyond the scope of this tutorial. For more information, see one of the following links:

Configure TLS connectivity in Azure Database for PostgreSQL - Single Server Configure SSL connectivity in your application to securely connect to Azure Database for MySQL

Next steps

What you learned:

  • Configure an Azure AD user as an administrator for your Azure database.
  • Connect to your database as the Azure AD user.
  • Configure a system-assigned or user-assigned managed identity for an App Service app.
  • Grant database access to the managed identity.
  • Connect to the Azure database from your code (.NET Framework 4.8, .NET 6, Node.js, Python, Java) using a managed identity.
  • Connect to the Azure database from your development environment using the Azure AD user.