Tutorial: Connect an App Service app to SQL Database on behalf of the signed-in user

This tutorial shows you how to enable built-in authentication in an App Service app using the Microsoft Entra authentication provider, then extend it by connecting it to a back-end Azure SQL Database by impersonating the signed-in user (also known as the on-behalf-of flow). This is a more advanced connectivity approach to Tutorial: Access data with managed identity and has the following advantages in enterprise scenarios:

• Eliminates connection secrets to back-end services, just like the managed identity approach.
• Gives the back-end database (or any other Azure service) more control over who or how much to grant access to its data and functionality.
• Lets the app tailor its data presentation to the signed-in user.

In this tutorial, you add Microsoft Entra authentication to the sample web app you deployed 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 authenticate users connect to SQL Database securely on behalf of the signed-in user.

Note

The steps covered in this tutorial support the following versions:

• .NET Framework 4.8 and higher
• .NET 6.0 and higher

What you will learn:

• Enable built-in authentication for Azure SQL Database
• Disable other authentication options in Azure SQL Database
• Enable App Service authentication
• Use Microsoft Entra ID as the identity provider
• Access Azure SQL Database on behalf of the signed-in Microsoft Entra user

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.

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

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.

Prepare your environment for the Azure CLI.

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

To start Azure Cloud Shell:

Option	Example/Link
Select Try It in the upper-right corner of a code or command block. Selecting Try It doesn't automatically copy the code or command to 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 menu bar at the upper right in the Azure portal.

To use Azure Cloud Shell:

1. Start Cloud Shell.

2. Select the Copy button on a code block (or command block) to copy the code or command.

3. Paste the code or command into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.

4. Select Enter to run the code or command.

1. Configure database server with Microsoft Entra authentication

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.

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

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

   Azure CLI

   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.

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

   Azure CLI

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

4. Restrict the database server authentication to Active Directory authentication. This step effectively disables SQL authentication.

   Azure CLI

   az sql server ad-only-auth enable --resource-group <group-name> --name <server-name>
   

For more information on adding an Active Directory admin, see Provision Microsoft Entra admin (SQL Database).

2. Enable user authentication for your app

You enable authentication with Microsoft Entra ID as the identity provider. For more information, see Configure Microsoft Entra authentication for your App Services application.

1. In the Azure portal menu, select Resource groups or search for and select Resource groups from any page.

2. In Resource groups, find and select your resource group, then select your app.

3. In your app's left menu, select Authentication, and then select Add identity provider.

4. In the Add an identity provider page, select Microsoft as the Identity provider to sign in Microsoft and Microsoft Entra identities.

5. Accept the default settings and select Add.

   

Tip

If you run into errors and reconfigure your app's authentication settings, the tokens in the token store may not be regenerated from the new settings. To make sure your tokens are regenerated, you need to sign out and sign back in to your app. An easy way to do it is to use your browser in private mode, and close and reopen the browser in private mode after changing the settings in your apps.

3. Configure user impersonation to SQL Database

Currently, your Azure app connects to SQL Database uses SQL authentication (username and password) managed as app settings. In this step, you give the app permissions to access SQL Database on behalf of the signed-in Microsoft Entra user.

1. In the Authentication page for the app, select your app name under Identity provider. This app registration was automatically generated for you. Select API permissions in the left menu.

2. Select Add a permission, then select APIs my organization uses.

3. Type Azure SQL Database in the search box and select the result.

4. In the Request API permissions page for Azure SQL Database, select Delegated permissions and user_impersonation, then select Add permissions.

   

4. Configure App Service to return a usable access token

The app registration in Microsoft Entra ID now has the required permissions to connect to SQL Database by impersonating the signed-in user. Next, you configure your App Service app to give you a usable access token.

In the Cloud Shell, run the following commands on the app to add the scope parameter to the authentication setting identityProviders.azureActiveDirectory.login.loginParameters. It uses [jq] for JSON processing, which is installed already in the Cloud Shell.

Azure CLI

authSettings=$(az webapp auth show --resource-group <group-name> --name <app-name>)
authSettings=$(echo "$authSettings" | jq '.properties' | jq '.identityProviders.azureActiveDirectory.login += {"loginParameters":["scope=openid profile email offline_access https://database.windows.net/user_impersonation"]}')
az webapp auth set --resource-group <group-name> --name <app-name> --body "$authSettings"

The commands effectively add a loginParameters property with extra custom scopes. Here's an explanation of the requested scopes:

• openid, profile, and email are requested by App Service by default already. For information, see OpenID Connect Scopes.
• https://database.windows.net/user_impersonation refers to Azure SQL Database. It's the scope that gives you a JWT token that includes SQL Database as a token audience.
• offline_access is included here for convenience (in case you want to refresh tokens).

Tip

To configure the required scopes using a web interface instead, see the Microsoft steps at Refresh auth tokens.

Your apps are now configured. The app can now generate a token that SQL Database accepts.

5. Use the access token in your application code

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

Entity Framework

1. In Visual Studio, open the Package Manager Console and update Entity Framework:

   PowerShell

   Update-Package EntityFramework
   

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

   C#

   var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
   conn.AccessToken = System.Web.HttpContext.Current.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];
   

Entity Framework Core

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

C#

public MyDatabaseContext (DbContextOptions<MyDatabaseContext> options, IHttpContextAccessor accessor)
    : base(options)
{
    var conn = Database.GetDbConnection() as SqlConnection;
    conn.AccessToken = accessor.HttpContext.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];
}

Note

The code adds the access token supplied by App Service authentication to the connection object.

This code change doesn't work locally. For more information, see How do I debug locally when using App Service authentication?.

6. Publish your changes

ASP.NET

1. If you came from Tutorial: Build an ASP.NET app in Azure with SQL Database, you set a connection string in App Service using SQL authentication, with a username and password. Use the following command to remove the connection secrets, but replace <group-name>, <app-name>, <db-server-name>, and <db-name> with yours.

   Azure CLI

   az webapp config connection-string set --resource-group <group-name> --name <app-name> --connection-string-type SQLAzure --settings MyDbConnection="server=tcp:<db-server-name>.database.windows.net;database=<db-name>;"
   

2. Publish your changes in Visual Studio. In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.

   

3. In the publish page, select Publish.

ASP.NET Core

1. If you came from Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service, you have a connection string called defaultConnection in App Service using SQL authentication, with a username and password. Use the following command to remove the connection secrets, but replace <group-name>, <app-name>, <db-server-name>, and <db-name> with yours.

   Azure CLI

   az webapp config connection-string set --resource-group <group-name> --name <app-name> --connection-string-type SQLAzure --settings defaultConnection="server=tcp:<db-server-name>.database.windows.net;database=<db-name>;"
   

2. You would have made your code changes in your GitHub fork, with Visual Studio Code in the browser. From the left menu, select Source Control.

3. Type in a commit message like OBO connect and select Commit.

   The commit triggers a GitHub Actions deployment to App Service. Wait a few minutes for the deployment to finish.

When the new webpage shows your to-do list, your app is connecting to the database on behalf of the signed-in Microsoft Entra user.

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

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

Azure CLI

az group delete --name <group-name>

This command may take a minute to run.

Frequently asked questions

• Why do I get a Login failed for user '<token-identified principal>'. error?
• How do I add other Microsoft Entra users or groups in Azure SQL Database?
• How do I debug locally when using App Service authentication?
• What happens when access tokens expire?

Why do I get a Login failed for user '<token-identified principal>'. error?

The most common causes of this error are:

• You're running the code locally, and there's no valid token in the X-MS-TOKEN-AAD-ACCESS-TOKEN request header. See How do I debug locally when using App Service authentication?.
• Microsoft Entra authentication isn't configured on your SQL Database.
• The signed-in user isn't permitted to connect to the database. See How do I add other Microsoft Entra users or groups in Azure SQL Database?.

How do I add other Microsoft Entra users or groups in Azure SQL Database?

1. Connect to your database server, such as with sqlcmd or SSMS.

2. Create contained users mapped to Microsoft Entra identities in SQL Database documentation.

   The following Transact-SQL example adds a Microsoft Entra identity to SQL Server and gives it some database roles:

   SQL

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

How do I debug locally when using App Service authentication?

Because App Service authentication is a feature in Azure, it's not possible for the same code to work in your local environment. Unlike the app running in Azure, your local code doesn't benefit from the authentication middleware from App Service. You have a few alternatives:

• Connect to SQL Database from your local environment with Active Directory Interactive. The authentication flow doesn't sign in the user to the app itself, but it does connect to the back-end database with the signed-in user, and allows you to test database authorization locally.
• Manually copy the access token from https://<app-name>.azurewebsites.net/.auth/me into your code, in place of the X-MS-TOKEN-AAD-ACCESS-TOKEN request header.
• If you deploy from Visual Studio, use remote debugging of your App Service app.

What happens when access tokens expire?

Your access token expires after some time. For information on how to refresh your access tokens without requiring users to reauthenticate with your app, see Refresh identity provider tokens.

Next steps

What you learned:

• Enable built-in authentication for Azure SQL Database
• Disable other authentication options in Azure SQL Database
• Enable App Service authentication
• Use Microsoft Entra ID as the identity provider
• Access Azure SQL Database on behalf of the signed-in Microsoft Entra user

Map an existing custom DNS name to Azure App Service

Tutorial: Access Microsoft Graph from a secured .NET app as the app

Tutorial: Isolate back-end communication with Virtual Network integration