Using Managed Identities to access Azure SQL

Using managed identities to access SQL in Azure is a great way to up the security of your solution.

Unfortunately, it is a bit of a hassle to set up… And it comes with some “interesting” caveats that can make it a bit complicated and confusing.

The main problem is that adding a managed identity as a SQL Server user is a bit complex. And, requires some “privilege tweaking”. Because of this, a lot of samples on the interwebs tend to simply use the Active Directory SQL admin account for access. This is however not the best way to do it, and it definitely doesn’t conform to any type of “least privilege” principle.

On the other hand, I completely understand why people tend to take this route, as it is way simpler to set up, and requires a lot less work. But extra work shouldn’t really be a reason to skimp on security in my mind…

What am I going to show?

To demonstrate the whole thing, I’m going to show how to set up a SQL Server database and a Web App in Azure, and have the web app talk to SQL using a managed identity. But, I’m going to do it “the right way”, and not use the Active Directory SQL admin account to get access. Instead, I’m going to set up a specific database user for the Web App’s identity. Like it is supposed to be done.

Why?

Well, using managed identities (MSI) to perform authentication and authorization, allows us to get rid of user credentials in the application. This means that there are no credentials that could end up be disclosed somewhere, and that we don’t have to worry about rotating the credentials every x [time unit]. This is a great way to improve the security of our applications without adding a lot of work to our lives.

Managed identity authentication is a feature that can be leveraged for a lot of services in the Azure. For example, I find it very common to put your database credentials in Key Vault, and then use managed identities to access the Key Vault to get hold of them. And even if this is a good use of MSI, it can be made even better by using the identity to access the database directly. This way, we don’t have to store any credentials at all, even if Key Vault is great at that, and we don’t have to worry about rotating them.

But, if we are going to use MSI in this way, we should do it the right way. And that means that we should not use the Active Directory SQL admin account to get access to the database. Instead, we should create a specific database user for the MSI, and use that to access to the database. This is the way it is supposed to be done, and it is the way that we should do it.

Creating the infrastructure

I’m going to set up the infrastructure using Azure CLI and PowerShell, but the sample repo on GitHub also contains Bicep templates, if you want to do it using Bicep instead.

1. Setting up the SQL database

The first step is to create the SQL Server and database. But before I can do that, I need to log in, and create a resource group to place the resources in. To do that, I run the following commands

> az login

> $group = az group create -g SqlDemo -l WestEurope | ConvertFrom-Json

Once the resource group is in place, I can go ahead and create the SQL Server. But…as I am going to assign myself as the SQL Server admin, I first need to get hold of…myself? Well, the current user.

> $currentUser = az ad signed-in-user show | ConvertFrom-Json

I’m also going to make sure that the resource names are unique, by suffixing then with a random string. So, I also need to create a random suffix

> $random = ([char[]]([char]'a'..[char]'z') + 0..9 | sort {get-random})[0..6] -join ''

Note: I’m using PowerShell on Windows for this. And most of this will work on PowerShell Core on Linux/Mac as well. However, you are going to have to find another way to generate a random string, as this code has some issues in those cases.

Once I have the prerequisites in place, I can go ahead and create the SQL Server and SQL Database

> $sqlServer = az sql server create -g $group.Name -n "DemoSqlServer$random" `
                              --enable-ad-only-auth `
                              --external-admin-principal-type User `
                              --external-admin-name $currentUser.DisplayName `
                              --external-admin-sid $currentUser.Id `
                              --assign-identity --identity-type SystemAssigned `
                              | ConvertFrom-Json

> $db = az sql db create -g $group.Name -n DemoDb `
                         -s $sqlServer.Name `
                         --service-objective Basic -y `
                         | ConvertFrom-Json

As you can see, I am creating a SQL Server instance, with only Azure AD authentication enabled. I’m also setting the currently signed-in user as the admin of the server to make sure that I can access it ones it has been created. Finally, I also make sure to assign a system assigned managed identity to the server.

Note: It might be a better idea to use and AD Group for the SQL Server AD admin instead of a single account, as this makes it possible to allow multiple people to access the server as admins. But this is a demo, and I’m the only one that needs to access it.

Comment: If you are not familiar with managed identities, they are basically users in the AD that can be given permissions just like any other user. And they come in 2 flavors, system assigned and user assigned. System assigned identities are dynamically created by the system, and assigned to the resource as it is being created. User assigned identities are manually created resources that can be assigned to a resource during creation. This allows the identity to be pre-configured with permissions for example, before it is assigned to a resource. (As you will see later in this post)

Once the SQL Server is up and running, I go ahead and create a database called DemoDb on that server.

2. Setting up the Web App

Once the SQL Server and database resources are in place, I can go ahead and create an App Service plan, and a Web App.

> $appSvcPlan = az appservice plan create -g $group.Name -n DemoAppSvcPlan `
                                          --is-linux --sku F1 `
                                          | ConvertFrom-Json

> $webApp = az webapp create -g $group.Name -n "DemoWebApp$random" `
                             -p $appSvcPlan.Name `
                             --assign-identity "[system]" `
                             --runtime "DOTNETCORE:7.0" `
                             | ConvertFrom-Json

I’m creating a free, Linux-based App Service plan, and a dotnet-based Web App in that plan. And once again, I make sure to assign a system assigned managed identity to the Web App. This is the identity that is going to be used to access the database.

Now that I have the web app infrastructure in place, I need to add a connection string to the Web App. Because, even if we are going to use managed identities to access the database, we still need to tell the Web App where the database is located, and that it should use managed identity authentication.

So, to do that, I run the following commands

> $connstring = "Server=$($sqlServer.FullyQualifiedDomainName),1433;Database=$($db.Name);Authentication=Active Directory Default"

> az webapp config connection-string set -g $group.Name `
                                         -n $webApp.Name -t SQLAzure `
                                         --settings MyConnString=$connstring

As you can see, the connection string looks a lot like a “normal” connection string. But instead of User ID and Password parameters, it has an Authentication parameter that is set to Active Directory Default. This is what tells the code running in the Web App to use managed identity authentication.

3. Creating a database user for the managed identity

The last step is to add a database user for Web App’s MSI. This requires a few steps.

First of all, I need to open up the SQL Server’s firewall to allow me to talk to it. And while I’m doing that, I might as well open it up for any Azure resources that might need to talk to the SQL Server as well.

So, to open the firewall for myself, as well as any Azure resource, I run the following commands

> $myIp = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

> az sql server firewall-rule create -g $group.Name -n SetUp `
                                   -s $sqlServer.Name `
                                   --start-ip-address $myIp `
                                   --end-ip-address $myIp `
                                   | ConvertFrom-Json

> az sql server firewall-rule create -g $group.Name -n "Allow Azure Services" `
                                   -s $sqlServer.Name `
                                   --start-ip-address 0.0.0.0 `
                                   --end-ip-address 0.0.0.0

Ok…that should be all the firewall changes needed for it to work.

Note: You might want to keep a reference to the SetUp rule and remove it when the set-up has been completed. But as you know, this is a demo, and I will delete the entire set up once I am done.

With the firewall opened, I can go ahead and add the database user for the Web App’s system assigned MSI. And to do that, I need to use an access token, as I have limited authentication to AD accounts only. Luckily, it is very easy to get hold of an access token for the current user, using the Azure CLI.

> $token = az account get-access-token --resource="https://database.windows.net" `
                                       --query accessToken --output tsv

To create a database user based on an AD user (managed identity), I need to use the CREATE USER statement, adding FROM EXTERNAL PROVIDER. Like this

> $query = "CREATE USER [$($webApp.Name)] FROM EXTERNAL PROVIDER"

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName `
                -Database $db.Name -AccessToken $token -Query $query

This will create a DB user based on the AD user with the provided name.

Note: System assigned MSI’s get the same name as the resource that they are being used by. So, I can simply use the Web App’s name as the username. However, it is also possible to use a custom database username, by providing WITH OBJECT_ID=<OBJECT ID>.

And then I can assign the appropriate permissions to that user…

In this case, as it is a demo, I am going to be very lazy, and simply add the user to the db_datareader role. But in a real world scenario, you would probably need to use a more complex set up.

> $query = "ALTER ROLE db_datareader ADD MEMBER [$($webApp.Name)]"

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName `
                -Database $db.Name -AccessToken $token -Query $query

4. Accessing the database from the Web App

The last step is to verify that the access to the database works. And for this, I have created a really simple ASP.NET Core API that has a single API endpoint that looks like this

app.MapGet("/", async () =>
{
    using (var conn = new SqlConnection(app.Configuration.GetConnectionString("MyConnString")))
    {
        try
        {
            await conn.OpenAsync();
            await conn.CloseAsync();

            return $"Success talking to {conn.DataSource}!";
        }
        catch (Exception ex)
        {
            return "Failed to connect to SQL. Exception: " + ex.GetBaseException().Message;
        }
    }
});

After deploying that to the Web App, I can go ahead and open up a browser and verify that it works

Now that I now that it works, I can go ahead and tear it all down by simply running

> az group delete -g SqlDemo -y

Ok, so that seemed fairly “simple”! Or at least fairly manageable… But wait…there is more…or rather… But wait, here be dragons!

Creating the infrastructure using a Service Principal

The thing is, that even if everything has worked fine this far, we should probably take into account that we often tend to create our infrastructure using some form of automation. And when we do that, the creation is often managed by a Service Principal, rather than using our own credentials. And that is where the dragons seem to rear their ugly heads.

To demonstrate this, I’ll go ahead and do the same thing I just did, but using a Service Principal instead.

Creating a Service Principal

The first step in this case, is to create a Service Principal.

But, since I also want to give it permission to deploy resources to a resource group, I first need to create the group.

> $group = az group create -g SqlDemo -l WestEurope | ConvertFrom-Json

I can then create the SP, and assign it to the owner role of that group, using the following command

> $principal = az ad sp create-for-rbac --name SqlDemoUser `
                                        --role owner --scopes $group.Id `
                                        | ConvertFrom-Json

Now that I have an SP, I need to make sure that it’s allowed to query the Microsoft Graph API to get hold of users in the AD.

Why do I need this you ask? Well, as you run the SQL command to create the user, it needs to access the AD to get hold of the user’s object ID. And to do that, it needs to use the Microsoft Graph API.

Note: The reason that I didn’t need to do this step in the previous demo, is that I am an admin in the AD. So I have full access to everything already…

To give the SP access to query the API, I need to give it the Directory.Read.All permissions. And to do that, I need to run the following commands

> $graph = az ad sp list --query "[?appDisplayName=='Microsoft Graph'].{AppId:appId, Id:id} | [0]" | ConvertFrom-Json

> $directoryReadPermission = az ad sp show --id $graph.AppId --query "appRoles[?value=='Directory.Read.All']" | ConvertFrom-Json

> az ad app permission add --id $principal.AppId --api $graph.AppId --api-permissions "$($directoryReadPermission.Id)=Role" | Out-Null

Let’s go through what this code does… First, it gets the appId and id of the Microsoft Graph API. It then gets the id of the Directory.Read.All permission for that app. And finally, it adds that permission to the SP.

Comment: Yes, it is a bit cumbersome, but that’s just the way it is unfortunately.

When you run the az ad app permission add command, the CLI will output the following message

WARNING: Invoking az ad app permission grant --id 20bf3386-4d86-4c87-9eff-6de8c3cdba40 --api 00000003-0000-0000-c000-000000000000 is needed to make the change effective

However, you can actually ignore this, as the command you really need to run is

> az ad app permission admin-consent --id $principal.AppId

This adds admin consent to the application, which is needed to approve the permission.

Note: You might want to give it a minute or two before you run the az ad app permission admin-consent, as it sometimes takes a minute or two for the permission change to be performed. Unfortunately, the command won’t fail if the permission hasn’t been assigned yet, so you might need to run it a couple of times before it actually works.

Logging in as the Service Principal

With the SP in place, I can now go ahead and log in as that SP, using the following command

> az login --service-principal -u $principal.AppId `
                               -p $principal.Password `
                               --tenant $principal.Tenant

And to make sure that the permission has been properly assigned, and consented, I can try running

> az ad user list

[
    {
        ..
    },
    ...
]

And that seems to work…

So, now that I’m logged in as the SP, let’s see what happens then I try to create the same infrastructure as before.

Setting up the infrastructure

To set up the infrastructure, I will run more or less the same commands as before. However, there are some minor tweaks when it comes to the SQL Server creation, as I need to update the --external-admin-sid parameter to use the SP’s AppId instead of Id as I used before.

> $random = ([char[]]([char]'a'..[char]'z') + 0..9 | sort {get-random})[0..6] -join ''

> $sqlServer = az sql server create -g $group.Name -n "DemoSqlServer$random" `
                              --enable-ad-only-auth `
                              --external-admin-principal-type User `
                              --external-admin-name $principal.DisplayName `
                              --external-admin-sid $principal.AppId `
                              --assign-identity --identity-type SystemAssigned `
                              | ConvertFrom-Json

> $db = az sql db create -g $group.Name -n DemoDb `
                         -s $sqlServer.Name `
                         --service-objective Basic -y `
                         | ConvertFrom-Json

> $appSvcPlan = az appservice plan create -g $group.Name -n DemoAppSvcPlan `
                                          --is-linux --sku F1 `
                                          | ConvertFrom-Json

> $webApp = az webapp create -g $group.Name -n "DemoWebApp$random" `
                             -p $appSvcPlan.Name `
                             --assign-identity "[system]" `
                             --runtime "DOTNETCORE:7.0" `
                             | ConvertFrom-Json

> $connstring = "Server=$($sqlServer.FullyQualifiedDomainName),1433;Database=$($db.Name);Authentication=Active Directory Default"

> az webapp config connection-string set -g $group.Name `
                                         -n $webApp.Name -t SQLAzure `
                                         --settings MyConnString=$connstring

> $myIp = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

> az sql server firewall-rule create -g $group.Name -n SetUp `
                                   -s $sqlServer.Name `
                                   --start-ip-address $myIp `
                                   --end-ip-address $myIp `
                                   | ConvertFrom-Json

> az sql server firewall-rule create -g $group.Name -n "Allow Azure Services" `
                                   -s $sqlServer.Name `
                                   --start-ip-address 0.0.0.0 `
                                   --end-ip-address 0.0.0.0

> $token = az account get-access-token --resource="https://database.windows.net" `
                                       --query accessToken --output tsv

> $query = "CREATE USER [$($webApp.Name)] FROM EXTERNAL PROVIDER"

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName `
                -Database $db.Name -AccessToken $token -Query $query

Ok, so far, everything except that tiny little change to the --external-admin-sid, is exactly as it was before. However, when I start trying to access the database, things starts to fall apart.

When I run

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName -Database $db.Name -AccessToken $token -Query $query

it fails with a message saying

Invoke-SqlCmd: Server identity does not have Azure Active Directory Readers permission. Please follow the steps here : https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal

Ok…didn’t expect that! And wait…the SP does have the Directory.Read.All permission! Go home Azure, you are drunk!

Actually…it’s not! However, there is a bit of weirdness here in my opinion. The reason for the error is the following…

When you run a command against Azure SQL using a user account, it executes the commands as that user. So, when I run CREATE USER [...] FROM EXTERNAL PROVIDER everything is fine, as it will access the Graph API as me, the logged-in user.

And you would assume it would do the same thing when you run the command as the SP, but it doesn’t.

When you run a SQL command using a Service Principal, the Graph API access will actually be done by the SQL Server’s assigned identity. So, even if the SP I’m using has the Directory.Read.All permission, it won’t work, as the SQL Server’s principal doesn’t…. Yeah… Not too obvious in my opinion…

If you are following along, I would suggest you remove the infrastructure you just set up, as it obviously won’t work. But, since you are logged in as the SP, you will need to revert to your own login to be able to remove the AD app.

> az login

> az group delete -g SqlDemo -y

> az ad app delete --id $principal.AppId

Note: Yes, you could remove the resources and the resource group using the SP, as it was set as owner. But you need to revert to your own account to remove the SP (AD app) itself…

Solving the issue

With the constraints I just mentioned, I need to make sure that the SQL Server’s principal has Directory.Read.All permission in the Graph API. This can be handled in a couple of ways.

The easiest way, is to simply give the assigned principal the required permission. However, this only really works in an interactive scenario, where an admin can go and do this manually. In an automated situation, you don’t want to have a manual step like this in the middle of everything. And the only way to remove the manual step is to give the SP that runs the automation the permission to assign roles to other principals. And that is really not a great idea from a security point of view, as it could be exploited to elevate permissions etc. So I don’t want to do that.

The other solution, which I find to be a lot better, is to manually create a managed identity with the required permissions, up front. And then assign that principal to the SQL Server instance as a user assigned managed identity when it is created.

Other solutions

There are other solutions floating around “out there”. The most popular seems to be to use an undocumented T-SQL command to create the database user.

The command looks like this CREATE USER [...] WITH SID=0xXXXX, TYPE=E. This allows us to manually specify the users SID, instead of having the SQL Server go to the AD to look it up. Thus removing the need to have the Directory.Read.All permission. However, according to this GitHub Issue it isn’t a supported command, and will likely be removed in the future. So I wouldn’t really want to depend on that solution…

Comment: It kind of sucks that it might be removed, without any real replacement, as it solves this problem quite nicely. But relying on a feature that is potentially removed in the future, seems like a bad idea. Also, you need to write some code to generate the SID value, which isn’t that hard, but still…

The previously mentioned GitHub Issue also mentions the ability to use CREATE USER [...] FROM EXTERNAL PROVIDER WITH OBJECT_ID='...'. However, that command unfortunately has exactly the same limitation as using it without the WITH OBJECT_ID part. So, it doesn’t really solve anything.

Note: The WITH OBJECT_ID can be used if you want to use another name for the user in the database than the one used in the AD

I really hope that this is solved in a nicer way in the future, as it seems like a very common thing to do. And having to rely on manually created resources for something like this, feels like a massive design flaw…

The solution

As mentioned before, the “best” solution at the moment, in my mind, is to use a pre-configured managed identity. And besides being an annoying manual step, it isn’t that bad.

Setting up a managed identity

To set up a managed identity, all I need to do, is run an az identity create command.

> $group = az group create -g SqlDemo -l WestEurope | ConvertFrom-Json

> $identity = az identity create --name SqlDemoIdentity -g $group.Name | ConvertFrom-Json

As you can see, I start out by creating a resource group to hold the resources I’m about to create, and then create a managed identity.

The cool thing with managed identities like this, is that they become Azure resources that you can easily find and work with in the Portal as well. As opposed to SPs, which are somewhat weirdly hidden away in the AD settings under App Registrations.

Once the identity is in place, I need to give it permission to read users in the Microsoft Graph API. And the first part of that is identical to what I did when I gave the SP permission

> $graph = az ad sp list --query "[?appDisplayName=='Microsoft Graph'].{AppId:appId, Id:id} | [0]" --all | ConvertFrom-Json

> $directoryReadPermission = az ad sp show --id $graph.AppId `
                                           --query "appRoles[?value=='Directory.Read.All']" `
                                           | ConvertFrom-Json

Note: If you want to lock it down a bit more, you can assign User.Read.All, GroupMember.Read.All and Application.Read.All instead of Directory.Read.All.

Unfortunately, the next step is not as simple… The Azure CLI doesn’t have a command for assigning AD permissions to an identity. It only supports assigning AD permissions to SPs (AD Apps). For identities like this, it only knows how to assign Azure permissions, unfortunately.

Luckily, Azure is API-driven, so we can just call the corresponding REST-endpoint to add the permission.

> az rest -m POST `
          -u https://graph.microsoft.com/v1.0/servicePrincipals/$($identity.PrincipalId)/appRoleAssignments `
          --headers "{\`"Content-Type\`":\`"application/json\`"}" --body "{\`"principalId\`": \`"$($identity.PrincipalId)\`", \`"resourceId\`": \`"$($graph.Id)\`", \`"appRoleId\`": \`"$($directoryReadPermission.id)\`"}" `
          | Out-Null

Yeah…unfortunately PowerShell makes escaping JSON a bit “interesting”. It requires you to use \`" to escape a " character.

Switching to a Service Principal

Now that there is a managed identity with the required permissions in place, I can go ahead and create the infrastructure. However, I once again want to run as an SP to mimic an automated pipeline. So, I first need to create an SP and log in as that.

> $principal = az ad sp create-for-rbac --name SqlDemoUser `
                                        --role owner --scopes $group.Id `
                                        | ConvertFrom-Json

> az login --service-principal -u $principal.AppId `
                               -p $principal.Password `
                               --tenant $principal.Tenant

However, as you can see, I’m not giving it any extra permissions at all this time. So, if I were to run

az ad user list

It errors out, with an Insufficient privileges to complete the operation message.

Setting up the resources

Now that there is a managed identity to use for the SQL Server, and I’m logged in as an SP with no permissions, I can go ahead and create the resources.

> $random = ([char[]]([char]'a'..[char]'z') + 0..9 | sort {get-random})[0..6] -join ''

> $sqlServer = az sql server create -g $group.Name -n "DemoSqlServer$random" `
                              --enable-ad-only-auth `
                              --external-admin-principal-type User `
                              --external-admin-name $principal.DisplayName `
                              --external-admin-sid $principal.AppId `
                              --assign-identity --identity-type UserAsigned `
                              --user-assigned-identity-id $identity.Id `
                              --primary-user-assigned-identity-id $identity.Id `
                              | ConvertFrom-Json

> $db = az sql db create -g $group.Name -n DemoDb `
                         -s $sqlServer.Name `
                         --service-objective Basic -y `
                         | ConvertFrom-Json

> $appSvcPlan = az appservice plan create -g $group.Name -n DemoAppSvcPlan `
                                          --is-linux --sku F1 `
                                          | ConvertFrom-Json

> $webApp = az webapp create -g $group.Name -n "DemoWebApp$random" `
                             -p $appSvcPlan.Name `
                             --assign-identity "[system]" `
                             --runtime "DOTNETCORE:7.0" `
                             | ConvertFrom-Json

> $connstring = "Server=$($sqlServer.FullyQualifiedDomainName),1433;Database=$($db.Name);Authentication=Active Directory Default"

> az webapp config connection-string set -g $group.Name `
                                         -n $webApp.Name -t SQLAzure `
                                         --settings MyConnString=$connstring

> $myIp = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

> az sql server firewall-rule create -g $group.Name -n SetUp `
                                   -s $sqlServer.Name `
                                   --start-ip-address $myIp `
                                   --end-ip-address $myIp `
                                   | ConvertFrom-Json

> az sql server firewall-rule create -g $group.Name -n "Allow Azure Services" `
                                   -s $sqlServer.Name `
                                   --start-ip-address 0.0.0.0 `
                                   --end-ip-address 0.0.0.0

The only changes here, are the addition of the --user-assigned-identity-id and --primary-user-assigned-identity-id parameters to the az sql server create command, and the switch from SystemAssigned to UserAssigned for the --identity-type parameter.

Comment: You can assign multiple managed identities to Azure resources, if you want to. That’s why you have the --primary-user-assigned-identity-id parameter on the SQL Server.

Note: A more common use case for using multiple user assigned identities, would be when using a Web App. This allows you to assign different permissions to different parts of the application, while still using MSIs.

Note: To define the identity to use when accessing SQL Server, you can simply provide a User ID parameter in the connection string. Like this Server=<SERVER NAME>;Database=<DB NAME>;Authentication=Active Directory Default;User ID=<Client/App Id of the managed identity>. However, in a single identity scenario, this parameter is not needed.

The differences, although small, are the thing that makes it possible to create the database user without any problems.

> $token = az account get-access-token --resource="https://database.windows.net" `
                                       --query accessToken --output tsv
> $query = "CREATE USER [$($webApp.Name)] FROM EXTERNAL PROVIDER"

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName `
                -Database $db.Name `
                -AccessToken $token `
                -Query $query

> $query = "ALTER ROLE db_datareader ADD MEMBER [$($webApp.Name)]"

> Invoke-SqlCmd -ServerInstance $sqlServer.FullyQualifiedDomainName `
                -Database $db.Name `
                -AccessToken $token `
                -Query $query

Once that is all in place, I can deploy the extremely simple API, and verify the access.

And once I know that it works, I can remove the created resources by running

> az login

> az group delete -g SqlDemo -y

> az ad app delete --id $principal.AppId

Conclusion

Looking at the code, it isn’t that complicated to get it going. However, there are a few things to remember to get it working. And that took a bit of time for me to get figured out to be honest. But once you know how to do it, it should be fairly simple to get it working in the future.

The code is, as usual, available on GitHub for everything I have shown in this post. Including Bicep templates to deploy the resources if you prefer that. It is available at https://github.com/ChrisKlug/azure-sql-with-managed-identities.

Note: You still need to create the user assigned identity manually, using a PowerShell script or similar, even if you set up the resources using Bicep.

And as usual, if you have any questions, comments or suggestions, feel free to ping me on Twitter at @ZeroKoll!

zerokoll

Chris

Developer-Badass-as-a-Service at your service