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

Question

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.

Gaslight

Could you add error message? – Wormhole

Answer 1

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.

Unpile

The problem is that the appId is the ID of the Managed Identity created by the GitHub Action. I've tried to read that ID, but since the GitHub Action does not have access to read from the AD, it cannot retrieve this ID. Hence the catch-22. Again, my goal is to create everything fully automated, so I cannot add this ID to a GitHub secret. Also, even if I grant the GitHub Action Global Admin rights, it still cannot read from the AD. – Gaslight

is it user-assigned identity or system-assigned ? When creating the managed identity these information should be returned => at least for user-assigned identity – Unpile

and also how are you creating the identity: az cli / arm / bicep / az powershell ? – Unpile

I'm trying to grant access to a user assigned identity (see screenshot). Resources are created using Bicep (see github.com/PlatformPlatform/platformplatform/blob/main/…). SQL statements are executed from Bash script (could be PowerShell). See github.com/PlatformPlatform/platformplatform/blob/main/…. I need to create everything using Bicep, to enable drift detection. I plan to use the upcoming Azure Deployment Stack, which will block manual changes from e.g., PowerShell. – Gaslight

you can retrieve the clientId of the user assigned identity as sown here: https://mcmap.net/q/1329453/-output-parameters-for-user-assigned-identity-in-bicep. then you re good to go to grant permission in sql server – Unpile

Thanks for helping, Thomas. Truly appreciated. I was able to obtain the Principal ID of the Managed Identity by pulling it from the Bicep output. Unfortunately I learned that the Principal ID and the SID aren't the same thing. The SID is internal to Azure AD, and I have not been able to find a way to programmatically extract this. But if I manually grant the Managed Identity access, I can read it from the "sys.database_principals" table. I can also confirm that the command "CREATE USER [MyManagedIdentity] WITH SID = $SID, TYPE = E;" works when I have the SID. But I'm unfortunately still stuck. – Gaslight

You need to get the clientId not the principalId. SQL SID is just a binary version of the clientId. You could verify that by checking records in the [sys].[database_principals] table. That works for me on many pipelines – Unpile

Wow, it actually works! Thank you so much. I started a 50-point bounty for this, that I'm going to give to you in 24 hours — I've spent so much time thinking it was impossible. I'm using Bash, and even though I tried the [System.BitConverter] trick, I got it wrong and I concluded there was no correlation with the SID. Your persistence convinced me to give it another shot, and now it is working (even from Bash, although it's ugly compared to PowerShell). I can finally spin up a full cluster automatically without any secrets. Thanks again! – Gaslight

Good.to ear it's working for you :-) – Unpile

Answer 2

This is now possible using the Microsoft Graph Powershell module and the Biceps extension. I realize you're using Github actions and we're primarily using Azure DevOps, but maybe this can help someone. You'll need a Serviceaccount that you grant Directory.Read.All and RoleManagement.ReadWrite.Directory API access.

  $principalIdString = '<your Sql Server system assigned principal id'
  $accessToken = (Get-AzAccessToken -ResourceTypeName MSGraph -ErrorAction Stop).Token
  $secureToken = ConvertTo-SecureString -String $(accessToken) -AsPlainText -Force
  Connect-MgGraph -AccessToken $secureToken -NoWelcome
  $filter = "PrincipalId eq '" + $principalIdString + "'"
  $roleDefinition = Get-MgRoleManagementDirectoryRoleDefinition -Filter "DisplayName eq 'Directory Readers'"
  $rdnotexist = ((Get-MgRoleManagementDirectoryRoleAssignment -Filter $filter).RoleDefinitionId -eq $null)
  if( $rdnotexist ) {
    $roleAssignment = New-MgRoleManagementDirectoryRoleAssignment -DirectoryScopeId '/' -RoleDefinitionId $roleDefinition.Id -PrincipalId $principalIdString
  } else {
    Write-Host Role Already assigned
  }

The example from Microsoft is using scoped permissions when connecting, we're setting that permission to the service account in the portal. It also differs that it sets up a group instead with the correct permissions and adds the SQL Server managed identity to that group. This cannot be done without paid Azure Entra ID P1 or P2.

If you're using Bicep templates, there is now an extension. Extensions are enabled by adding this in bicepconfig.json in your working directory.

{
    "experimentalFeaturesEnabled": {
        "extensibility": true
    }
}

You'll need to explicitly delcare you want to import it in your .bicep template.

 provider microsoftGraph

I haven't managed to use it with the System Assigned identity, but setting up a user assigned works. The service account in this case must have AppRoleAssignment.ReadWrite.All and Application.Read.All and a Entra Id Role per documentation here.

resource sqlServerIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' existing = {
   name: sqlServerIdentityName
} // existing identity, or you can create a new one.

resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: <yourservername>
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${sqlServerIdentity.id}': {}
    }
  } // your other admin login properties and things here.
  primaryUserAssignedIdentityId: sqlServerIdentity.id
  }
}
// Grab the graph app, 
resource graphSpn 'Microsoft.Graph/[email protected]' existing = {
  appId: '00000003-0000-0000-c000-000000000000'
}
resource mgAppRoleAssignemnt 'Microsoft.Graph/[email protected]' = {
  appRoleId: '7ab1d382-f21e-4acd-a863-ba3e13f7da61' // Directory.Read.All guid.
  principalId: sqlServerIdentity.properties.principalId
  resourceId: graphSpn.id
  dependsOn: [ 
    sqlServer
    sqlServerIdentity
  ]
}

I hope this helps for others looking to automate their Azure SQL Server provisioning and deployment (IaC).

References

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-directory-readers-role-tutorial?view=azuresql

https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.applications/?view=graph-powershell-1.0

https://learn.microsoft.com/en-us/graph/templates/overview-bicep-templates-for-graph

Designer

@Crypht Thanks… this looks promising. I will take a look and see if I can get this to work, as my current workaround feels very hacky. I will write back here if this works and change the accepted answer, but it might take some time before I can prioritize this. – Gaslight

@ThomasJespersen Np, I'm in the same boat :) To me the Powershell thing looks cleaner after provisioning is done but is less elegant because of it's script based approach. The Bicep needs another User Assigned Identity which is less nice after setup and so is using experimental Bicep extension (being worked on to be production ready soon though). However, keeping it all in the Bicep templates feels much more consistent to me. I updated the answer with correct permissions for the Bicep approach. – Designer