Edit

Share via

Tutorial: Use managed identity to connect an Azure web app to an Azure SQL database without secrets

Azure App Service provides a highly scalable, self-patching web hosting service in Azure. App Service also provides a managed identity for your app, which is a turnkey solution for securing access to Azure SQL and other Azure services. Managed identities in App Service make your app more secure by eliminating secrets, such as credentials in connection strings.

This tutorial shows you how to add managed identity to a sample .NET app that has an Azure SQL backend. After you finish, your app can connect to the Azure SQL database securely without the need for a user name and password.

Architecture diagram for tutorial scenario.

In this tutorial, you:

  • Enable managed identities.
  • Grant Azure SQL Database access to the managed identity.
  • Configure Entity Framework to use Microsoft Entra authentication with SQL Database.
  • Connect to SQL Database from Visual Studio using Microsoft Entra authentication.

For guidance about using Azure Database for MySQL or Azure Database for PostgreSQL in Node.js, Python, and Java frameworks, see Tutorial: Connect to Azure databases from App Service without secrets using a managed identity.

Note

Prerequisites

Grant database admin access to a Microsoft Entra user

Enable Microsoft Entra authentication to the Azure SQL database by assigning a Microsoft Entra user as the admin of the Azure SQL server. The Microsoft Entra admin must be a user that is created, imported, synced, or invited into Microsoft Entra ID. This user might not be the same as the Microsoft account user for your Azure subscription.

Run the following commands in the Bash environment of Azure Cloud Shell, or after signing in to Azure CLI locally.

  1. Use az ad user list with the display-name, filter, or upn parameter to get the object ID for the Microsoft Entra ID user you want to make admin. Run az ad user list standalone to show information for all the users in the Microsoft Entra directory.

    For example, the following command lists information for a Microsoft Entra ID user with the display-name of Firstname Lastname.

    az ad user list --display-name "Firstname Lastname"

    Here's example output:

     "businessPhones": [],
 "displayName": "Firstname Lastname",
 "givenName": null,
 "id": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
 "jobTitle": null,
 "mail": "firstname@contoso.com",
 "mobilePhone": null,
 "officeLocation": null,
 "preferredLanguage": null,
 "surname": null,
 "userPrincipalName": "firstname@contoso.com"

  2. Add the Microsoft Entra ID user as an admin on the Azure SQL server by using az sql server ad-admin create with the object-id parameter. In the following command, replace <server-name> with your server name minus the .database.windows.net suffix, and <entra-id> with the id value from the output of the preceding az ad user list command.

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

Set up managed identity connectivity for the app

The following steps configure your app to connect to Azure SQL Database by using a system-assigned managed identity. To use a user-assigned identity, see Tutorial: Connect to Azure databases from App Service without secrets using a managed identity.

Enable managed identity for the app

To enable a managed identity for your Azure app, use the az webapp identity assign command, replacing <app-name> with your app name. The name of a system-assigned identity is always the same as the app name.

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

Here's an example of the output:

{
  "additionalProperties": {},
  "principalId": "aaaaaaaa-bbbb-cccc-1111-222222222222",
  "tenantId": "aaaabbbb-0000-cccc-1111-dddd2222eeee",
  "type": "SystemAssigned"
}

To enable managed identity for a deployment slot, add --slot <slot-name> to the preceding command and use the name of the slot in <slot-name>. The name of a system-assigned identity for a deployment slot is <app-name>/slots/<slot-name>.

You can also add the identity to a Microsoft Entra group, then grant SQL Database access to the Microsoft Entra group instead of to the identity. To grant permissions for a Microsoft Entra group, use the group's display name. The following commands add the example managed identity 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 myResourceGroup --name <app-name> --query principalId --output tsv)
az ad group member add --group $groupid --member-id $msiobjectid
az ad group member list -g $groupid

Grant permissions to the managed identity

Grant the identity the minimum permissions your app needs.

  1. Open a PowerShell command line and sign in to SQL Database by using the following SQLCMD command. Replace <server-name> with your server name, <db-name> with your database name, and <admin-user> with the userPrincipalName of the admin user from the output of the preceding az ad user list command.

    sqlcmd -S <servername>.database.windows.net -d <db-name> -U <admin-user> -G -l 30

    Follow the prompts to sign in.

  2. At the SQL prompt, run the following commands to grant the app the minimum permissions it needs on the database. Replace <identity-name> with the name of the managed identity in Microsoft Entra ID, which is the same as the app name.

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

Note

The backend managed identity services maintain a token cache that updates the token for a target resource only when it expires. If you try to modify your SQL Database permissions after first getting a token with your app, you don't get a new token with updated permissions until the cached token expires.

Remove the original connection string

Any changes you made in web.config or appsettings.json work with the managed identity. You can remove the original connection string you used when you deployed your app the first time. To delete the connection string, run the following Azure CLI command, replacing <app-name> with the name of your app and <connection-string-name> with the name of your connection string.

az webapp config connection-string delete --resource-group myResourceGroup --name <app-name> --setting-names <connection-string-name>

Set up your development environment

Set up your chosen development environment and sign in to Azure. For more information about setting up your dev environment for Microsoft Entra authentication, see Azure Identity client library for .NET.

Visual Studio for Windows is integrated with Microsoft Entra authentication.

  1. To enable development and debugging in Visual Studio, add your Microsoft Entra user in Visual Studio by selecting File > Account Settings from the top menu, and then select Sign in or Add.
  2. To set the Microsoft Entra user for Azure service authentication, select Tools > Options from the top menu, and then select Azure Service Authentication > Account Selection. Select the Microsoft Entra user you added and select OK.

Modify your project and publish your app

Your Azure SQL database-backed web app uses a database context to connect with the database. To use Microsoft Entra authentication to work with the app, you must update the database context to refer to the Entity Framework SQL Server provider, which depends on the modern Microsoft.Data.SqlClient ADO.NET provider.

The Entity Framework provider replaces the built-in System.Data.SqlClient SQL Server provider, and includes support for Microsoft Entra ID authentication methods. For more information, see Microsoft.EntityFramework.SqlServer.

[DbConfigurationType(typeof(MicrosoftSqlDbConfiguration))] works locally to use Microsoft.Data.SqlClient for the database context, but because System.Data.SqlClient is hardcoded as the provider in Azure App Service, you must extend MicrosoftSqlDbConfiguration to redirect System.Data.SqlClient references to Microsoft.Data.SqlClient instead. The steps differ depending on whether you have an ASP.NET or ASP.NET Core app.

An ASP.NET Core app uses Entity Framework Core by default.

  1. In the Visual Studio Package Manager Console, add the NuGet package Microsoft.Data.SqlClient.

    Install-Package Microsoft.Data.SqlClient

  2. In appsettings.json, replace the value of the connection string with the following code, replacing <server-name and <database-name> with your server name and database name.

    "Server=tcp:<server-name>.database.windows.net;Authentication=Active Directory Default; Database=<database-name>;"

    Note

    You can use Active Directory Default authentication both on your local machine and in Azure App Service. The driver can acquire a token from Microsoft Entra ID in several different ways.

    If the app is deployed, the driver gets a token from the app's system-assigned managed identity. The driver can also authenticate with a user-assigned managed identity if you include User Id=<client-id-of-user-assigned-managed-identity>; in your connection string.

    The DefaultAzureCredential class caches the token in memory and retrieves it from Microsoft Entra ID before expiration. You don't need any custom code to refresh the token.

    You now have everything you need to connect to Azure SQL Database when you debug in Visual Studio. Your code uses the Microsoft Entra user you configured when you set up your dev environment.

  3. Run your app. The CRUD app in your browser connects to the Azure SQL database directly, using Microsoft Entra authentication. This setup lets you run database migrations from Visual Studio.

  4. Publish your changes using the following Git commands:

    git commit -am "configure managed identity"
git push azure main

Test the app

When the new webpage shows your to-do list, your app is connecting to the database using the managed identity.

Screenshot that shows the Azure app after Code First Migration.

You can now edit the to-do list.

Clean up resources

In the preceding steps, you created Azure resources in a resource group. If you don't expect to need these resources in the future, delete the resource group by running the following command in the Cloud Shell:

az group delete --name myResourceGroup

This command may take a minute to run.

Related content