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

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.

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

2. Sign in to Azure CLI with the following command using your Microsoft Entra user:

   az login --allow-no-subscriptions
   

1. Visual Studio Code is integrated with Microsoft Entra authentication through the Azure extension. Install the Azure Tools extension in Visual Studio Code.

2. In Visual Studio Code, in the Activity Bar, select the Azure logo.

3. In the App Service explorer, select Sign in to Azure... and follow the instructions.

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

2. Sign in to Azure with the following command using your Microsoft Entra user:

   az login --allow-no-subscriptions
   

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

2. Sign in to Azure CLI with the following cmdlet using your Microsoft Entra user:

   Connect-AzAccount -Environment AzureChinaCloud
   

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

2. 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.chinacloudapi.cn;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.

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

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

2. 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.chinacloudapi.cn/.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.

3. In Web.config, find the connection string called MyDbConnection and replace its connectionString value with "server=tcp:<server-name>.database.chinacloudapi.cn;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.

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

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

   

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

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