AAD – Using Managed Service Identity (MSI) with Azure App Service and Azure SQL Database

Managed Identity Service is a useful feature to implement for the cloud applications you plan to develop in Azure. You can use this identity to authenticate to any service that supports Azure AD authentication without having any credentials in your code. The credentials never appear in the code or in the source control. It works by defining a service principal ID attributed to either a system-assigned (Azure Service instance) or a user-assigned (Azure stand-alone resource).

The difference between both is that for the system-assigned identity, the service principal ID is available only within the same subscription and the lifetime identity of the Azure Service instance is directly tied where its enabled on. If the instance is removed, the identity is also removed.

The service principal ID of a user-assigned identity is the same, only available within a same subscription but is managed separably from the life cycle of Azure instances to which its assigned.

In this article, i enabled the Managed Identity service for the web app with an Azure SQL database.

To give access to the web app to we will simply add the principal ID inside the SQL group. This will let the service principal ID of the web app to request a token to authenticate to the SQL database.

My setup

Databases : DotNetAppSqlDbDEV_db

AD Admin group : SG-SQL

Web app : DotNetAppSqlDbDEV

Resource Group : WebApp

 

Enabled managed identities

First, enable the Managed Identity on the Web App.

It could also be completed using Azure CLI

az webapp identity assign --resource-group WebApp --name DotNetAppSqlDbDEV

Create a service principal ID for the Web App

Using Cloud Shell start a prompt and type

az webapp identity show --resource-group WebApp --name DotNetAppSqlDbDEV

We should see the input

DotNetAppSqlDbDEV
{
“identityIds”: null,
“principalId”: “930e03f9-5f4e-4318-a968-56168373717c”,
“tenantId”: “0c48420c-e308-4473-b18a-ad23996ec439”,
“type”: “SystemAssigned”
}

Copy the PrincipalId for later.

Creating SQL Access

Create the Active Directory Admin for the SQL Server and assign the group to have access to the database. Follow this post for the complete steps.

Modify your connection string connecting your Web App to the database

Since we wont need any credentials present in the connection string, modify it with the following Azure CLI command.

az webapp config connection-string set --resource-group WebApp --name DotNetAppSqlDbDEV --settings MyDbConnection='Server=tcp:mysqlserver.database.windows.net,1433;Database=DotNetAppSqlDbDEV_db;' --connection-string-type SQLAzure

Add the service PrincipalID access to the SQL group

Azure SQL Database does not support creating logins or users from service principals created from Managed Service Identity so the way to do it is by adding the ID in the SQL groups that was previously given the rights to the database.

az ad group member add -g SG-SQL --member-id 930e03f9-5f4e-4318-a968-56168373717c

Modify your code to acquire a token

Add the library and the constructor :

using Microsoft.Azure.Services.AppAuthentication;
using System.Data.SqlClient;
using System.Web.Configuration;

This constructor configures a custom SqlConnection object to use an access token for Azure SQL Database from App Service. With the access token, your App Service app authenticates with Azure SQL Database with its managed identity.

public MyDatabaseContext(SqlConnection conn) : base(conn, true)
{
    conn.ConnectionString = WebConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;
    // DataSource != LocalDB means app is running in Azure with the SQLDB connection string you configured
    if(conn.DataSource != "(localdb)\\MSSQLLocalDB")
        conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

    Database.SetInitializer<MyDatabaseContext>(null);
}

private MyDatabaseContext db = new MyDatabaseContext(new SqlConnection());

Refer to the link for the complete code and tutorial on how to deploy : https://docs.microsoft.com/en-ca/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet-code

Publish your application to Azure and you should be able to use the servicePrincipal token to authenticate to the database instead of a set of credentials.

Helpful link  : https://winterdom.com/2017/10/19/azure-sql-auth-with-msi

https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview