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
Categories: Azure, Azure Active Directory, Azure SQL, Identity, Managed Service Identity, Windows