title	description	author	ms.author	ms.devlang	ms.topic	ms.date	ms.custom
Tutorial: Access data with managed identity	Secure Azure SQL Database connectivity with managed identity from a sample .NET web app, and also how to apply it to other Azure services.	cephalin	cephalin	csharp	tutorial	04/17/2024	devx-track-csharp, mvc, cli-validate, devx-track-azurecli, devx-track-dotnet, AppServiceConnectivity

Tutorial: Connect to SQL Database from .NET 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 SQL Database and other Azure services. Managed identities in App Service make your app more secure by eliminating secrets from your app, such as credentials in the connection strings. In this tutorial, you add managed identity to the sample web app you built in one of the following tutorials:

Tutorial: Build an ASP.NET app in Azure with Azure SQL Database
Tutorial: Build an ASP.NET Core and Azure SQL Database app in Azure App Service

When you're finished, your sample app will connect to SQL Database securely without the need of username and passwords.

Note

The steps covered in this tutorial support the following versions:

.NET Framework 4.8 and above
.NET 6.0 and above

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

What you will learn:

[!div class="checklist"]

Enable managed identities

Grant 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

Note

Microsoft Entra authentication is different from Integrated Windows authentication in on-premises Active Directory (AD DS). AD DS and Microsoft Entra ID use completely different authentication protocols. For more information, see Microsoft Entra Domain Services documentation.

[!INCLUDE quickstarts-free-trial-note]

Prerequisites

This article continues where you left off in either one of the following tutorials:

Tutorial: Build an ASP.NET app in Azure with SQL Database
Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service.

If you haven't already, follow one of the two tutorials first. Alternatively, you can adapt the steps for your own .NET app with SQL Database.

To debug your app using SQL Database as the back end, make sure that you've allowed client connection from your computer. If not, add the client IP by following the steps at Manage server-level IP firewall rules using the Azure portal.

Prepare your environment for the Azure CLI.

[!INCLUDE azure-cli-prepare-your-environment-no-header.md]

1. Grant database access to Microsoft Entra user

First, enable Microsoft Entra authentication to SQL Database by assigning a Microsoft Entra user as the admin of the server. 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.

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.
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)
```
[!TIP] To see the list of all user principal names in Microsoft Entra ID, run az ad user list --query '[].userPrincipalName'.
Add this Microsoft Entra user as an Active Directory admin using az sql server ad-admin create command in the Cloud Shell. In the following command, replace <server-name> with the server name (without the .database.windows.net suffix).
```
az sql server ad-admin create --resource-group myResourceGroup --server-name <server-name> --display-name ADMIN --object-id $azureaduser
```

For more information on adding an Active Directory admin, see Provision a Microsoft Entra administrator for your server

2. Set up your dev environment

Visual Studio Windows

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.
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.

Visual Studio for macOS

Visual Studio for Mac is not integrated with Microsoft Entra authentication. However, the Azure Identity client library that you'll use later can use tokens from Azure CLI. To enable development and debugging in Visual Studio, install Azure CLI on your local machine.
Sign in to Azure CLI with the following command using your Microsoft Entra user:
```
az login --allow-no-subscriptions
```

Visual Studio Code

Visual Studio Code is integrated with Microsoft Entra authentication through the Azure extension. Install the Azure Tools extension in Visual Studio Code.
In Visual Studio Code, in the Activity Bar, select the Azure logo.
In the App Service explorer, select Sign in to Azure... and follow the instructions.

Azure CLI

The Azure Identity client library that you'll use later can use tokens from Azure CLI. To enable command-line based development, install Azure CLI on your local machine.
Sign in to Azure with the following command using your Microsoft Entra user:
```
az login --allow-no-subscriptions
```

Azure PowerShell

The Azure Identity client library that you'll use later can use tokens from Azure PowerShell. To enable command-line based development, install Azure PowerShell on your local machine.
Sign in to Azure CLI with the following cmdlet using your Microsoft Entra user:
```
Connect-AzAccount
```

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.

3. Modify your project

Note

Microsoft.Azure.Services.AppAuthentication is no longer recommended to use with new Azure SDK. It is replaced with new Azure Identity client library available for .NET, Java, TypeScript and Python and should be used for all new development. Information about how to migrate to Azure Identitycan be found here: AppAuthentication to Azure.Identity Migration Guidance.

The steps you follow for your project depends on whether you're using Entity Framework Core (default for ASP.NET Core) or Entity Framework (default for ASP.NET).

Entity Framework Core

In Visual Studio, open the Package Manager Console and add the NuGet package Microsoft.Data.SqlClient:
```
Install-Package Microsoft.Data.SqlClient -Version 5.1.0
```
In the ASP.NET Core and SQL Database tutorial, the MyDbConnection connection string in appsettings.json isn't used at all yet. The local environment and the Azure environment both get connection strings from their respective environment variables in order to keep connection secrets out of the source file. But now with Active Directory authentication, there are no more secrets. In appsettings.json, replace the value of the MyDbConnection connection string with:
```
"Server=tcp:<server-name>.database.windows.net;Authentication=Active Directory Default; Database=<database-name>;"
```
[!NOTE] The Active Directory Default authentication type can be used both on your local machine and in Azure App Service. The driver attempts to acquire a token from Microsoft Entra ID using various means. If the app is deployed, it gets a token from the app's system-assigned managed identity. It 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. If the app is running locally, it tries to get a token from Visual Studio, Visual Studio Code, and Azure CLI.

That's everything you need to connect to SQL Database. When you debug in Visual Studio, your code uses the Microsoft Entra user you configured in 2. Set up your dev environment. You'll set up SQL Database later to allow connection from the managed identity of your App Service app. The DefaultAzureCredential class caches the token in memory and retrieves it from Microsoft Entra ID just before expiration. You don't need any custom code to refresh the token.
Type Ctrl+F5 to run the app again. The same CRUD app in your browser is now connecting to the Azure SQL Database directly, using Microsoft Entra authentication. This setup lets you run database migrations from Visual Studio.

Entity Framework

In Visual Studio, open the Package Manager Console and add the NuGet package Azure.Identity and update Entity Framework:
```
Install-Package Azure.Identity
Update-Package EntityFramework
```
[!NOTE] The token caching feature for Managed Identity is available starting from Azure.Identity version 1.8.0. To help reduce network port usage, consider updating Azure.Identity to this version or later.

In your DbContext object (in Models/MyDbContext.cs), add the following code to the default constructor.

Azure.Identity.DefaultAzureCredential credential;
var managedIdentityClientId = ConfigurationManager.AppSettings["ManagedIdentityClientId"];
if(managedIdentityClientId != null ) {
    //User-assigned managed identity Client ID is passed in via ManagedIdentityClientId
    var defaultCredentialOptions = new DefaultAzureCredentialOptions { ManagedIdentityClientId = managedIdentityClientId };
    credential = new Azure.Identity.DefaultAzureCredential(defaultCredentialOptions);
}
else {
    //System-assigned managed identity or logged-in identity of Visual Studio, Visual Studio Code, Azure CLI or Azure PowerShell
    credential = new Azure.Identity.DefaultAzureCredential();
}
var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
conn.AccessToken = token.Token;

This code uses Azure.Identity.DefaultAzureCredential to get a useable token for SQL Database from Microsoft Entra ID and then adds it to the database connection. While you can customize DefaultAzureCredential, by default it's already versatile. When it runs in App Service, it uses the app's system-assigned managed identity by default. If you prefer to use a user-assigned managed identity, add a new App setting named ManagedIdentityClientId and enter the Client Id GUID from your user-assigned managed identity in the value field. When it runs locally, it can get a token using the logged-in identity of Visual Studio, Visual Studio Code, Azure CLI, and Azure PowerShell.

In Web.config, find the connection string called MyDbConnection and replace its connectionString value with "server=tcp:<server-name>.database.windows.net;database=<db-name>;". Replace <server-name> and <db-name> with your server name and database name. This connection string is used by the default constructor in Models/MyDbContext.cs.

That's every thing you need to connect to SQL Database. When you debug in Visual Studio, your code uses the Microsoft Entra user you configured in 2. Set up your dev environment. You'll set up SQL Database later to allow connection from the managed identity of your App Service app.
Type Ctrl+F5 to run the app again. The same CRUD app in your browser is now connecting to the Azure SQL Database directly, using Microsoft Entra authentication. This setup lets you run database migrations from Visual Studio.

4. Use managed identity connectivity

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

Note

While the instructions in this section are for a system-assigned identity, a user-assigned identity can just as easily be used. To do this. you would need the change the az webapp identity assign command to assign the desired user-assigned identity. Then, when creating the SQL user, make sure to use the name of the user-assigned identity resource rather than the site name.

Enable managed identity on app

To enable a managed identity for your Azure app, use the az webapp identity assign command in the Cloud Shell. In the following command, replace <app-name>.

az webapp identity assign --resource-group myResourceGroup --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>.

Here's an example of the output:

{
  "additionalProperties": {},
  "principalId": "21dfa71c-9e6f-4d17-9e90-1d28801c9735",
  "tenantId": "72f988bf-86f1-41af-91ab-2d7cd011db47",
  "type": "SystemAssigned"
}

Grant permissions to managed identity

Note

If you want, you can add the identity to an Microsoft Entra group, then grant SQL Database 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 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

In the Cloud Shell, sign in to SQL Database by using the SQLCMD command. Replace <server-name> with your server name, <db-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Microsoft Entra user's credentials.
```
sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30
```
In the SQL prompt for the database you want, run the following commands to grant the minimum permissions your app needs. For example,
```
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
```
<identity-name> is the name of the managed identity in Microsoft Entra ID. If the identity is system-assigned, the name is always the same as the name of your App Service app. For a deployment slot, the name of its system-assigned identity is <app-name>/slots/<slot-name>. To grant permissions for a Microsoft Entra group, use the group's display name instead (for example, myAzureSQLDBAccessGroup).
Type EXIT to return to the Cloud Shell prompt.

[!NOTE] The back-end services of managed identities also maintains a token cache that updates the token for a target resource only when it expires. If you make a mistake configuring your SQL Database permissions and try to modify the permissions 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.

[!NOTE] Microsoft Entra ID and managed identities are not supported for on-premises SQL Server.

Modify connection string

Remember that the same changes you made in Web.config or appsettings.json works with the managed identity, so the only thing to do is to remove the existing connection string in App Service, which Visual Studio created deploying your app the first time. Use the following command, but replace <app-name> with the name of your app.

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

5. Publish your changes

All that's left now is to publish your changes to Azure.

ASP.NET

If you came from Tutorial: Build an ASP.NET app in Azure with SQL Database, publish your changes in Visual Studio. In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.
In the publish page, select Publish.

[!IMPORTANT] Ensure that your app service name doesn't match with any existing App Registrations. This will lead to Principal ID conflicts.

ASP.NET Core

If you came from Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service, publish your changes using Git, with the following commands:

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

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

You should now be able to edit the to-do list as before.

[!INCLUDE cli-samples-clean-up]

Next steps

What you learned:

[!div class="checklist"]

Enable managed identities

Grant 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

[!div class="nextstepaction"] Secure with custom domain and certificate

[!div class="nextstepaction"] Tutorial: Connect an App Service app to SQL Database on behalf of the signed-in user

[!div class="nextstepaction"] Tutorial: Connect to Azure databases from App Service without secrets using a managed identity

[!div class="nextstepaction"] Tutorial: Connect to Azure services that don't support managed identities (using Key Vault)

[!div class="nextstepaction"] Tutorial: Isolate back-end communication with Virtual Network integration

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

tutorial-connect-msi-sql-database.md

tutorial-connect-msi-sql-database.md

Tutorial: Connect to SQL Database from .NET App Service without secrets using a managed identity

Prerequisites

1. Grant database access to Microsoft Entra user

2. Set up your dev environment

Visual Studio Windows

Visual Studio for macOS

Visual Studio Code

Azure CLI

Azure PowerShell

3. Modify your project

Entity Framework Core

Entity Framework

4. Use managed identity connectivity

Enable managed identity on app

Grant permissions to managed identity

Modify connection string

5. Publish your changes

ASP.NET

ASP.NET Core

Next steps

Files

tutorial-connect-msi-sql-database.md

Latest commit

History

tutorial-connect-msi-sql-database.md

File metadata and controls

Tutorial: Connect to SQL Database from .NET App Service without secrets using a managed identity

Prerequisites

1. Grant database access to Microsoft Entra user

2. Set up your dev environment

3. Modify your project

4. Use managed identity connectivity

Enable managed identity on app

Grant permissions to managed identity

Modify connection string

5. Publish your changes

Next steps