Tutorial: Secure Azure SQL Database connection from App Service 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 will add managed identity to the sample ASP.NET web app you built in Tutorial: Build an ASP.NET app in Azure with SQL Database. When you're finished, your sample app will connect to SQL Database securely without the need of username and passwords.

Note

This scenario is currently supported by .NET Framework 4.7.2 and above. .NET Core 2.2 does support the scenario, but is not yet included in the default images in App Service.

What you learn how to:

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

Note

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

If you don't have an Azure subscription, create a free account before you begin.

Prerequisites

This article continues where you left off in Tutorial: Build an ASP.NET app in Azure with SQL Database. If you haven't already, follow that tutorial first. Alternatively, you can adapt the steps for your own ASP.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.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. Cloud Shell lets you use either bash or PowerShell to work with Azure services. You can use the Cloud Shell pre-installed commands to run the code in this article without having to install anything on your local environment.

To launch Azure Cloud Shell:

Option Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell. Example of Try It for Azure Cloud Shell
Go to https://shell.azure.com or select the Launch Cloud Shell button to open Cloud Shell in your browser.
Select the Cloud Shell button on the top-right menu bar in the Azure portal. Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Launch Cloud Shell.
  2. Select the Copy button on a code block to copy the code.
  3. Paste the code into the Cloud Shell session with Ctrl+Shift+V on Windows and Linux, or Cmd+Shift+V on macOS.
  4. Press Enter to run the code.

Grant Azure AD user access to database

First enable Azure AD authentication to SQL Database by assigning an Azure AD user as the Active Directory admin of the SQL Database 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 Azure AD. For more information on allowed Azure AD users, see Azure AD features and limitations in SQL Database.

Find the object ID of the Azure AD 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 [].objectId --output tsv)

Tip

To see the list of all user principal names in Azure AD, run az ad user list --query [].userPrincipalName.

Add this Azure AD 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>.

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 an Azure Active Directory administrator for your Azure SQL Database Server

Set up Visual Studio

To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and click Add an account.

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

You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.

Modify ASP.NET project

In Visual Studio, open the Package Manager Console and add the NuGet package Microsoft.Azure.Services.AppAuthentication:

Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.2.0

In Web.config, working from the top of the file and make the following changes:

  • In <configSections>, add the following section declaration in it:

    <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    
  • below the closing </configSections> tag, add the following XML code for <SqlAuthenticationProviders>.

    <SqlAuthenticationProviders>
      <providers>
        <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
      </providers>
    </SqlAuthenticationProviders>
    
  • Find the connection string called MyDbConnection and replace its connectionString value with "server=tcp:<server-name>.database.windows.net;database=<db-name>;UID=AnyString;Authentication=Active Directory Interactive". Replace <server-name> and <db-name> with your server name and database name.

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 Azure AD authentication. This setup lets you run database migrations. Later when you deploy your changes to App Service, the same settings work with the app's managed identity.

Use managed identity connectivity

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

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>

Here's an example of the output:

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

Add managed identity to an Azure AD group

To grant this identity access to your SQL Database, you need to add it to an Azure AD group. In the Cloud Shell, add it to a new group called myAzureSQLDBAccessGroup, shown in the following script:

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

If you want to see the full JSON output for each command, drop the parameters --query objectId --output tsv.

Grant permissions to Azure AD group

In the Cloud Shell, sign in to SQL Database by using the SQLCMD command. Replace <server-name> with your SQL Database server name, <db-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Azure AD 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 add the Azure AD group and grant the permissions your app needs. For example,

CREATE USER [myAzureSQLDBAccessGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [myAzureSQLDBAccessGroup];
ALTER ROLE db_datawriter ADD MEMBER [myAzureSQLDBAccessGroup];
ALTER ROLE db_ddladmin ADD MEMBER [myAzureSQLDBAccessGroup];
GO

Type EXIT to return to the Cloud Shell prompt.

Modify connection string

Remember that the same changes you made in Web.config works with the managed identity, so the only thing to do is to remove the existing connection string in your app, 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

Publish your changes

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

In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.

Publish from Solution Explorer

In the publish page, click Publish. When the new webpage shows your to-do list, your app is connecting to the database using the managed identity.

Azure app after Code First Migration

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

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.

Next steps

What you learned:

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

Advance to the next tutorial to learn how to map a custom DNS name to your web app.