How to grant a Managed Identity permissions to an Azure SQL Database using IaC?

I use GitHub actions to spin up Azure resources from scratch using Infrastructure as Code (IaC). In my case Bicep, but it could be Terraform. This includes an Azure SQL Server, a SQL Database, and a User Assigned Managed Identity. After the resources are created I'm trying to get the GitHub action to grant the managed identity access to the database using this SQL script:

CREATE USER [MyManagedIdentity] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [MyManagedIdentity];
ALTER ROLE db_datawriter ADD MEMBER [MyManagedIdentity];

This is however failing because this requires that either the SQL Server or the GitHub action needs to read the Managed Identity from the Azure AD. Even if I manually add the Service Principal used by the GitHub action to the built-in "Directory Readers" role group (or to the Global Administrator for testing purposes) this does not work. I'm also unable to get the GitHub action to grant the SQL Server permissions to read the Active Directory (e.g. add it to the "Directory Readers" role).

It's a catch-22. After spending too much time on this, I believe it's not possible to create a new Azure SQL Server, a SQL Database, and a managed identity using Infrastructure as Code (IaC) and grant the Managed Identity reader and writer access to the database, but I would love to be proven wrong.

If I login to the SQL database with my own user (who is the Azure Admin on the SQL Server), this works fine. I assume it works because a normal AD user can read the Active Directory. It seems like an Azure service principal cannot be granted these permissions.

If I manually add the Azure SQL Server to the "Directory Readers" built-in Azure role, it also works. But I want to avoid manual steps, as I plan to create many Azure databases.

I'm okay with having a few manual steps when setting up the GitHub workflow, the Azure AD, and the Azure subscription. But my goal is to have all Azure resources from that point created using Infrastructure as Code, orchestrated from a GitHub action. Another goal is to have everything created without having any secrets, so a solution where I have AD username and password as GitHub secrets are also not acceptable. The GitHub Action uses a service principal that is using the new federated credentials instead of secrets, so I truly mean "no secrets".

I'm building a multi-tenant SaaS reference architecture called PlatformPlatform with .NET, DDD, Clean Architecture, CQRS, ASP.NET Minimal API, TypeScript, GitHub actions, IaC, Azure Container Apps, and you guessed it: "enterprise grade security". So if you need to have access to a test out a solution you can find a full example on GitHub. If anyone can solve this problem I will of course mark the correct answer, but you will also be able to see whatever workaround I find on GitHub.

When you run the command CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;, it creates an entry in the [sys].[database_principals] table.

Azure SQL will retrieve the managed identity AppId/ClientId connecting to AAD. That's why the user/principal running your Iac code needs directory read permission.

The AppId/ClientId is then converted to varbinary and inserted in the [sys].[database_principals] table as the sid (Security Identifier).

So CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER; is equivalent to CREATE USER [<identity-name>] WITH DEFAULT_SCHEMA=[dbo], SID = '<encoded-app-id>', TYPE = 'E';

Using powershell (as an example), you can convert the AppId/ClientId to sid like that:

$appId = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
$sid = "0x" + [System.BitConverter]::ToString(([guid]$appId).ToByteArray()).Replace("-", "")

So in your IaC pipeline:

1. Create the Azure SQL DB
2. Create the managed identity and retrieve the AppId/ClientId
3. Convert the AppId/ClientId to sid
4. Invoke SQL server (SqlCommand) to create the the sql user and grant it the required permissions.