Connect App Service with Azure SQL Database with Managed Identity

A managed identity from Azure Active Directory (Azure AD) allows your app to easily access other Azure AD-protected resources such as Azure Key Vault or Azure SQL. The identity is managed by the Azure platform and does not require you to provision or rotate any secrets.

We have two types of Managed Identities:

• System-assigned Identity
• User-assigned Identity

You can read about Managed Identity for App Service and Azure Functions here.

Below are the steps for the configuration:

1. You can create an App Service or an Azure Function and use the code example below to retrieve the token using the Azure Identity client library via System-assigned identity:

var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
var credential = new Azure.Identity.DefaultAzureCredential();
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
conn.AccessToken = token.Token;

If you are using User-assigned Identity you would need to tweak the code as below. You can read about DefaultAzureCredential()  here.

string userAssignedClientId = ""; //Give Client ID of User Managed Identity
var conn = new SqlConnection(connectionString);
var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = userAssignedClientId });
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default"}));
conn.AccessToken = token.Token;

2. You can remove the User ID / Password from the connection string:

Server=tcp:<AzSQLDBName>.database.windows.net,1433;Initial Catalog=<DBName>

3. Create a System Identity or User-Managed Identity and assign it to app service as per requirement.

4. Create the AD User in SQL Server and give the permissions your app needs:

• If the identity is system-assigned, the name is always the same as the name of your App Service app.

• If the identity is user-assigned the name is the Managed Identity resource rather than the site name.

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

• You can check the granted permission for your managed identity by using the below script:

SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName;
FROM sys.database_role_members AS DRM 
RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id;
LEFT OUTER JOIN sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id;
WHERE DP1.type = 'R' ORDER BY DP1.name;

5. Add the AD User or Managed Identity as the admin of the Az SQL Server. If you want, you can add the identity to an Azure AD group, then grant SQL Database access to the Azure AD group instead of the identity. (Link)

6. You can fetch the token and connect to the database using managed identity.

More Information:

• Managed identities - Azure App Service | Microsoft Docs
• Tutorial: Access data with managed identity - Azure App Service | Microsoft Docs
• DefaultAzureCredential Class (Azure.Identity) - Azure for .NET Developers | Microsoft Docs