0

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.

Azure Resource group showing the resources in play

Thomas Jespersen
  • 11,493
  • 14
  • 47
  • 55

1 Answers1

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.
Thomas
  • 24,234
  • 6
  • 81
  • 125
  • 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. – Thomas Jespersen Aug 29 '23 at 06:54
  • is it user-assigned identity or system-assigned ? When creating the managed identity these information should be returned => at least for user-assigned identity – Thomas Aug 29 '23 at 07:11
  • and also how are you creating the identity: az cli / arm / bicep / az powershell ? – Thomas Aug 29 '23 at 07:12
  • I'm trying to grant access to a user assigned identity (see screenshot). Resources are created using Bicep (see https://github.com/PlatformPlatform/platformplatform/blob/main/cloud-infrastructure/cluster/main-cluster.bicep). SQL statements are executed from Bash script (could be PowerShell). See https://github.com/PlatformPlatform/platformplatform/blob/main/cloud-Infrastructure/cluster/grant-database-permissions.sh. 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. – Thomas Jespersen Aug 29 '23 at 07:41
  • 1
    you can retrieve the clientId of the user assigned identity as sown here: https://stackoverflow.com/a/75619568/4167200. then you re good to go to grant permission in sql server – Thomas Aug 29 '23 at 19:55
  • 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. – Thomas Jespersen Sep 01 '23 at 20:13
  • 1
    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 – Thomas Sep 02 '23 at 00:16
  • 1
    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! – Thomas Jespersen Sep 02 '23 at 15:30
  • Good.to ear it's working for you :-) – Thomas Sep 02 '23 at 19:36