Document Details

For more information on adding an Active Directory admin, see [Provision Microsoft Entra admin (SQL Database)](/azure/azure-sql/database/authentication-aad-configure#provision-azure-ad-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](configure-authentication-provider-aad.md). 1. In the [Azure portal](https://portal.azure.com) menu, select **Resource groups** or search for and select *Resource groups* from any page. 1. In **Resource groups**, find and select your resource group, then select your app. 1. In your app's left menu, select **Authentication**, and then select **Add identity provider**. 1. In the **Add an identity provider** page, select **Microsoft** as the **Identity provider** to sign in Microsoft and Microsoft Entra identities. 1. Accept the default settings and select **Add**. :::image type="content" source="./media/tutorial-connect-app-access-sql-database-as-user-dotnet/add-azure-ad-provider.png" alt-text="Screenshot showing the add identity provider page." lightbox="./media/tutorial-connect-app-access-sql-database-as-user-dotnet/add-azure-ad-provider.png"::: > [!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. 1. Select **Add a permission**, then select **APIs my organization uses**. 1. Type *Azure SQL Database* in the search box and select the result. 1. In the **Request API permissions** page for Azure SQL Database, select **Delegated permissions** and **user_impersonation**, then select **Add permissions**. :::image type="content" source="./media/tutorial-connect-app-access-sql-database-as-user-dotnet/select-permission.png" alt-text="Screenshot of the Request API permissions page showing Delegated permissions, user_impersonation, and the Add permission button selected." lightbox="./media/tutorial-connect-app-access-sql-database-as-user-dotnet/select-permission.png"::: ## 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.

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](../active-directory/develop/v2-permissions-and-consent.md#openid-connect-scopes). - `https://database.windows.net/user_impersonation` refers to Azure SQL Database. It's the scope that gives you a JWT that includes SQL Database as a [token audience](https://wikipedia.org/wiki/JSON_Web_Token). - [offline_access](../active-directory/develop/v2-permissions-and-consent.md#offline_access) is included here for convenience (in case you want to [refresh tokens](#what-happens-when-access-tokens-expire)). > [!TIP] > To configure the required scopes using a web interface instead, see the Microsoft steps at [Refresh auth tokens](configure-authentication-oauth-tokens.md#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](/ef/ef6/) (default for ASP.NET) or [Entity Framework Core](/ef/core/) (default for ASP.NET Core). # [Entity Framework](#tab/ef) 1. In Visual Studio, open the Package Manager Console and update Entity Framework:

# [Entity Framework Core](#tab/efcore) In your `DbContext` object (in *Models/MyDbContext.cs*), change the default constructor to the following.

1. Publish your changes in Visual Studio. In the **Solution Explorer**, right-click your **DotNetAppSqlDb** project and select **Publish**. :::image type="content" source="./media/app-service-web-tutorial-dotnet-sqldatabase/solution-explorer-publish.png" alt-text="Screenshot showing how to publish from the Solution Explorer in Visual Studio." lightbox="./media/app-service-web-tutorial-dotnet-sqldatabase/solution-explorer-publish.png"::: 1. In the publish page, select **Publish**. # [ASP.NET Core](#tab/dotnetcore) 1. **If you came from [Tutorial: Build an ASP.NET Core and SQL Database app in Azure App Service](tutorial-dotnetcore-sqldb-app.md)**, 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.

----- > [!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?](#how-do-i-debug-locally-when-using-app-service-authentication). ## 6. Publish your changes # [ASP.NET](#tab/dotnet) 1. **If you came from [Tutorial: Build an ASP.NET app in Azure with SQL Database](app-service-web-tutorial-dotnet-sqldatabase.md)**, 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.

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

This command may take a minute to run. ## Frequently asked questions - [Why do I get a `Login failed for user '<token-identified principal>'.` error?](#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-add-other-azure-ad-users-or-groups-in-azure-sql-database) - [How do I debug locally when using App Service authentication?](#how-do-i-debug-locally-when-using-app-service-authentication) - [What happens when access tokens expire?](#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?](#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-azure-ad-users-or-groups-in-azure-sql-database). <a name='how-do-i-add-other-azure-ad-users-or-groups-in-azure-sql-database'></a> #### How do I add other Microsoft Entra users or groups in Azure SQL Database? 1. Connect to your database server, such as with [sqlcmd](/azure/azure-sql/database/authentication-aad-configure#sqlcmd) or [SSMS](/azure/azure-sql/database/authentication-aad-configure#connect-to-the-database-using-ssms-or-ssdt). 1. [Create contained users mapped to Microsoft Entra identities](/azure/azure-sql/database/authentication-aad-configure#create-contained-users-mapped-to-azure-ad-identities) in SQL Database documentation. The following Transact-SQL example adds a Microsoft Entra identity to SQL Server and gives it some database roles: