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 Microsoft Entra 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 a Microsoft Entra user as an administrator for your Azure database.
  • Connect to your database as the Microsoft Entra 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 Microsoft Entra user.

If you don't have an Azure subscription, create a trial 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.

You can use the local Azure CLI.

1. Grant database access to Microsoft Entra user

First, enable Microsoft Entra authentication to the Azure database by assigning a Microsoft Entra 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 Microsoft Entra ID. For more information on allowed Microsoft Entra users, see Microsoft Entra features and limitations in SQL Database.

  1. If your Microsoft Entra tenant doesn't have a user yet, create one by following the steps at Add or delete users using Microsoft Entra ID.

  2. Find the object ID of the Microsoft Entra 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 Microsoft Entra user as an Active Directory administrator using az sql server ad-admin create command in the Azure CLI. 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 a Microsoft Entra 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 Azure CLI. 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 Azure CLI, 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.chinacloudapi.cn -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

  1. Install dependencies.

    dotnet add package Microsoft.Data.SqlClient
    
  2. Get the Azure SQL Database connection string from the environment variable added by Service Connector.

    using Microsoft.Data.SqlClient;
    
    // AZURE_SQL_CONNECTIONSTRING should be one of the following:
    // For system-assigned managed identity:"Server=tcp:<server-name>.database.chinacloudapi.cn;Database=<database-name>;Authentication=Active Directory Default;TrustServerCertificate=True"
    // For user-assigned managed identity: "Server=tcp:<server-name>.database.chinacloudapi.cn;Database=<database-name>;Authentication=Active Directory Default;User Id=<client-id-of-user-assigned-identity>;TrustServerCertificate=True"
    
    string connectionString = 
        Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
    
    using var connection = new SqlConnection(connectionString);
    connection.Open();
    

    For more information, see Using Active Directory Managed Identity authentication.

4. Set up your dev environment

This sample code uses DefaultAzureCredential to get a useable token for your Azure database from Microsoft Entra ID 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 Microsoft Entra 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 Microsoft Entra user. In this step, you configure your environment of choice by signing in with your Microsoft Entra user.

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

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

For more information about setting up your dev environment for Microsoft Entra 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 Microsoft Entra authentication.

5. Test and publish

  1. Run your code in your dev environment. Your code uses the signed-in Microsoft Entra user in your environment to connect to the back-end database. The user can access the database because it's configured as a Microsoft Entra 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?

Microsoft Entra ID 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 a Microsoft Entra group?

If you want, you can add the identity to an Microsoft Entra group, then grant access to the Microsoft Entra 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 a Microsoft Entra group, see documentation for the respective database type.

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 a Microsoft Entra user as an administrator for your Azure database.
  • Connect to your database as the Microsoft Entra 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 Microsoft Entra user.