SQL Server MI.md

We have continued to research the topic and propose the following alternative solution. We ask Microsoft kindly to verify the solution described below and to inform whether this is an approperiate solution.

Proposed solution
A service principal is configured to create various resources in a subscription. This service principal has to be assigned to the Directory Readers role in the AAD in order to determine the Application Id for a Managed Identity in the context of the deployment process.

The configured “Managed Identity” of the Azure App Service can be registered on the respective Azure SQL database with the following command.

CREATE USER [{managedIdentityName}] WITH SID={applicationSid}, TYPE=E

The SID (applicationSid) passed in the SQL statement is now resolved by the deployment process and is accepted by the SQL Server without verification. You can use the .NET Core library Microsoft.Azure.Management.Graph.RBAC.Fluent to obtain the Application Id for an AAD registered application (Managed Identity).

var principal = await Az.GraphApi.ServicePrincipals.GetByNameAsync(managedIdentityName);
var applicationId = Guid.Parse(principal.ApplicationId);
var applicationSid = FormatSqlByteLiteral(applicationId.ToByteArray());

private static string FormatSqlByteLiteral(byte[] bytes)
{
    var stringBuilder = new StringBuilder();
    stringBuilder.Append("0x");
    foreach (var @byte in bytes)
    {
        if (@byte < 16)
        {
            stringBuilder.Append("0");
        }
        stringBuilder.Append(Convert.ToString(@byte, 16));
    }
    return stringBuilder.ToString();
}

After that, authorization roles (e.g. db_datareader, db_datawriter, db_ddladmin) can now be assigned to the assigned Managed Identity.

ALTER ROLE {role} ADD MEMBER [{managedIdentityName}];

Conclusion
The Azure App Service can now access the previously authorized Azure SQL database in the context of its Managed Identity.