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 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. Install the Service Connector passwordless extension

Install the latest Service Connector passwordless extension for the Azure CLI:

az extension add --name serviceconnector-passwordless --upgrade

Note

Please check the extension "serviceconnector-passwordless" version is "2.0.2" or higher by running az version. You may need to upgrade Azure CLI first to upgrade the extension version.

2. Create a passwordless connection

Next, create a passwordless connection with Service Connector.

Tip

The Azure portal can help you compose the commands below. In the Azure portal, go to your Azure App Service resource, select Service Connector from the left menu and select Create. Fill out the form with all required parameters. Azure automaticaly generates the connection creation command, which you can copy to use in the CLI or execute in Azure Cloud Shell.

The following Azure CLI command uses a --client-type parameter.

  1. Optionally run the az webapp connection create sql -h to get the supported client types.

  2. Choose a client type and run the corresponding command. Replace the placeholders below with your own information.

    az webapp connection create sql \
        --resource-group <group-name> \
        --name <server-name> \
        --target-resource-group <sql-group-name> \
        --server <sql-name> \
        --database <database-name> \
        --user-identity client-id=<client-id> subs-id=<subscription-id> \
        --client-type <client-type>
    

This Service Connector command completes the following tasks in the background:

  • Enable system-assigned managed identity, or assign a user identity for the app <server-name> hosted by Azure App Service.
  • Set the Microsoft Entra admin to the current signed-in user.
  • Add a database user for the system-assigned managed identity or user-assigned managed identity. Grant all privileges of the database <database-name> to this user. The username can be found in the connection string in preceding command output.
  • Set configurations named AZURE_MYSQL_CONNECTIONSTRING, AZURE_POSTGRESQL_CONNECTIONSTRING, or AZURE_SQL_CONNECTIONSTRING to the Azure resource based on the database type.
  • For App Service, the configurations are set in the App Settings blade.

If you encounter any problem when creating a connection, refer to Troubleshooting for help.

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.windows.net;Database=<database-name>;Authentication=Active Directory Default;TrustServerCertificate=True"
    // For user-assigned managed identity: "Server=tcp:<server-name>.database.windows.net;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.

For more information, see Homepage for client programming to Microsoft SQL Server. For more code samples, see Create a passwordless connection to a database service via Service Connector.

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?

Yes. For more information, see:

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

I created my app with the Web App + Database template, and now I can't configure a managed identity connection with the Service Connector commands.

Service Connector needs network access to the database in order to grant access for the app identity. When you create a secure-by-default app and database architecture in the Azure portal with the Web App + Database template, the architecture locks down network access to the database and only allows connections from within the virtual network. It's also true for Azure Cloud Shell. However, you can deploy Cloud Shell in the virtual network, then run the Service Connector command in that Cloud Shell.

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.