Deploying a DacPac to Azure SQL with Azure Pipelines and Managed Identities

We’ve all been there; we need to automate our database changes to each environment in our pipeline. There are a multitude of ways to do it, and what works for you may not work for me and vice-versa, but I generally like to simplify my life as much as possible.

In this post I’ll show you how, after a lot of trial and error, I managed to both automate and secure my database schema changes with Azure Pipelines. My main requirements were two-fold;

1. I can not have username/password combinations in any of my deployment tasks/variables. Too much of a security risk (not to mention maintenance),
2. I need to give access to different groups depending on the environment I’m deploying into (i.e. developers should not have access in production to sensitive data)

When I started out, I figured “Hey, this is such a common thing, there must be an easy built-in way to do it”. I was wrong. It’s not that it’s overly complicated to do, it’s just that the path to get there was a winding road with plenty of pot-holes along the way. Thankfully this blog post helped to point me in the right direction and inspired my approach, but it also used a bunch of PowerShell and client secrets in the process which I was looking to avoid.

Getting Ready

Alright, so let’s get to it. First a quick list of prerequisites:

1. You’ll obviously need an Azure DevOps account
2. You’ll need a Service Connection using an App Registration in your Azure AD which has at least “Contributor” privileges on the resource group you’re looking to deploy into. It will also need the “Azure Active Directory Graph -> Directory.Read.All” permission. I’ll explain that in Step 3. (Note: this is not the same as the Microsoft.Graph permission)
3. You’ll need 2 Azure AD Groups: one for your Active Directory Admin on your Azure SQL Server, and one to add your web applications Managed Identity to so you can give it access within the Azure SQL Database.
4. A DacPac from your SqlProject. For the purpose of this article, it should also have the creation of a Database Role as a post-deploy script within the DacPac. It should not assign any users/groups in this role within the DacPac (we’ll do that later).

I’m assuming you already have either an App Service or a Function App deployed (either works), along with an Azure SQL instance. Otherwise, go back and create those. Be sure to activate the Managed Identity on your App Service/Function App.

The Process

As I found out, the process actually requires 4 distinct steps (although 2 of them are very similar)

4 Steps arranged in a Task Group for easy re-use

Let’s go into each of them in detail here

Objective 1

We need to deploy the DacPac without any credentials in our pipeline.

1) Get Access Token for DacPac Deployment

This is the crux of the “no username/password” mantra: Access Tokens! If you do any front-end/API development, you’re probably familiar with the concept of JWTs and that is the method we’re going to be using in this article.

The devil is in the details though, and since we don’t want to use any usernames, passwords, client secrets, or certificates (essentially, anything you would use a Key Vault for), I decided to leverage the fact that my deployment is already using a Service Connection with access to my resource group.

First, I needed to make sure the App Registration of my Service Connection was a member of that Azure AD Group (AAD Group) that I assigned as my Azure AD Admin on my SQL Server. Go ahead and do that. If you haven’t already added the AAD Group as the Admin on your SQL Server, check the screenshot below.

On the SQL Server (and not the DB). Don’t forget to hit Save!

Now that your Service Connection has the right to modify the schema in your DB (since you did make it an Administrator), we’ll request a token using it’s credentials.

But wait, we don’t want any credentials in out pipeline, so how do we do that? That’s where the “Azure CLI” task comes into play.

The Azure CLI task runs in the context of your service connection, which means it already is authenticated as your service connection. You don’t need to re-authenticate with an “az login”.

Now I’m not sure why they did it this way, but while I was doing this, the Azure CLI task running on a Windows machine used Batch scripting rather than PowerShell. Thankfully Microsoft has updated this since, but the code snippet below was before that was an option.

FOR /F “tokens=* USEBACKQ” %%F IN (`az account get-access-token --resource=https://database.windows.net/ -query accessToken`) DO (
SET var=%%F
)
echo ##vso[task.setvariable variable=sqlToken;]%var%

The key to this snippet comes from this answer on StackOverflow because honestly, it had been a long time since I used Batch scripting so I was very grateful to find this.

I’ll explain it quick: the important parts are the

az account get-access-token --resource=https://database.windows.net/ --query accessToken 

and the

echo ##vso[task.setvariable variable=sqlToken;]%var%

The former asks Active Directory for a token with access to an Azure SQL instance (any), and the second assigns the output (which is the accessToken here) to a pipeline variable named sqlToken so we can use it in the next task.

It’s not pretty, but it works.

2) Deploy DB DacPac

This is actually the easiest part of the process in a lot of ways. It’s where I expected everything to be built in originally, but when I opened that “Authentication Type” dropdown, and Managed Identity was not an option, it was one of those “Uh Oh” moments.

We’ll be using the Azure SQL Database Deployment task.

At first I thought I may be able to get away with “Active Directory — Integrated” as the Authentication Type, but unfortunately it seems like that uses the identity of the Machine running your deployment, rather than the Service Connection actually doing the deployment. So that’s not an option.

Then I saw that the JDBC driver (for Java Applications) supports “ActiveDirectoryMSI” as an authentication type, but unfortunately that didn’t work either.

Thankfully as of version 18 (at least I’m pretty sure it’s 18) of SqlPackage.exe, /AccessToken is a valid argument, and hence step 1 of this process where I requested an access token.

First, choose “Connection String” as your authentication type, and then put a connection string without any User Id/Password, nor any Integrated Security. Just the bare-bones string.

Then just below you’ll see the Additional SqlPackage.exe Arguments box where we’re going to assign the Access token

/AccessToken:$(sqlToken)

And now our DacPac deployment will happen using the identity of our Service Connection. The Devs don’t need to keep any credentials in pipeline variables (or worse, hardcoded) and we’re able to deploy our schema to each environment.

Objective 1 complete.

Objective 2

We need to give our applications access to their specific environment. No Development environments reading the production database if you know what I mean.

We’ll handle this by using a different AAD group per environment, which you can then follow whatever your company’s procedure is to add your Managed Identity(ies) to that group and it will grant them access automatically.

3) Get Azure AD Group Object Id

Now this seems like a pretty weird step, and I agree it is. Since our database is hooked up to the Azure Active Directory (thanks to our AAD Admin on the SQL Server), normally I would be able to do something like

CREATE USER <aad group name> FROM EXTERNAL PROVIDER

But this doesn’t work when you log into the database with an access token. You’ll get an error along the lines of “<aad group name> not found”, which is pretty cryptic, and very confusing, since you know it exists.

So if we can’t use “FROM EXTERNAL PROVIDER”, how exactly do we add the group?

For now we’ll grab the Object Id of the Azure AD Group (based on it’s group name since that is human readable) and assign it to another pipeline variable.

This is why we need the Azure Active Directory Graph -> Directory.Read.All permission on our App Registration. This is listed under “Supported Legacy APIs” on the API Permissions pane of your App registration. Unfortunately the Microsoft Graph -> Directory.Read.All does not seem to provide the access we need (that is to say, looking up a group object id with the azure cli as a service principal)

This code will look familiar:

FOR /F “tokens=* USEBACKQ” %%F IN (`az ad group show — group “$(aadGroupName)” — query objectId`) DO (
SET var=%%F
)
echo ##vso[task.setvariable variable=aadGroupObjectId;]%var%

So now we have our Object Id stored as the pipeline variable

$(aadGroupObjectId)

4) Add Azure AD Group to the Database

Here’s where I finally had to resort to some custom PowerShell scripts which I was looking to avoid if only for ease of maintenance.

The blog post from my introduction was the key here in that the author noticed that the SID generated for an Azure AD User has a special format and used PowerShell to generate the SID from the ObjectId of the group. The code for generating the SID came from this StackOverflow answer.

I’ll post the full PowerShell of this task below since it’s a little long and I still want to explain the rest of it first.

The second big hurdle after getting the SID was that InvokeSqlCmd does not support Access tokens. So the same task that we use to deploy the DacPac (Azure SQL Database Deployment) with an access token, could not be used to simply run our CREATE USER command because it’s other two options (inline script and script file) do not support access tokens.

Thankfully, PowerShell does support connecting to the DB with an access token, so the final script looks like this. Something to note is the “Type” we assign to our user; X is an AAD Group, E is an AAD User.

We also assign the newly created user to the DB role we created with our DacPac. I like to follow the “Least Required Permissions” principal, so rather than just saying “db_owner”, I’ll generally use a custom role which has permissions like db_reader, db_writer, and db_execute.

Write-Verbose “Get SID”
$objId=$(aadGroupObjectId).Trim(‘“‘)
function ConvertTo-Sid {
param (
[string]$objectId
)
[guid]$guid = [System.Guid]::Parse($objectId)
foreach ($byte in $guid.ToByteArray()) {
$byteGuid += [System.String]::Format(“{0:X2}”, $byte)
}
return “0x” + $byteGuid
}
$SID=ConvertTo-Sid($objId)
Write-Verbose “Create SQL connectionstring”
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = “Server=$(db_server);Initial Catalog=$(db_name);Encrypt=True;TrustServerCertificate=False;Connection Timeout=30”
$conn.AccessToken = $(sqlToken)
Write-Verbose “Connect to database and execute SQL script”
$conn.Open()
$query = "IF NOT EXISTS (SELECT [name]
FROM [sys].[database_principals]
WHERE [type] = N’X’ AND [name] = N’$(aadGroupName)’)
BEGIN
CREATE USER [$(aadGroupName)] WITH DEFAULT_SCHEMA=[dbo], SID = $SID, TYPE= X;
ALTER ROLE [$(dbRoleName)] ADD MEMBER [$(aadGroupName)];
END
"
Write-Host $query
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)
$Result = $command.ExecuteNonQuery()
$conn.Close()

Objective 2 Complete

Wrapping Up

To summarize everything we just did:

1. We deployed our DacPac file using an Access Token which we obtained by leveraging the Service Connection from our Azure DevOps instance.
2. We assigned a different group per environment to a role in our database, allowing us to add 1 or more Managed Identities to the groups without affecting the security of our other environments.

By doing it this way, the developers never need to know any of the credentials for the database and we remove the possibility of having plain text usernames, passwords, and client secrets in one or more deployment pipelines. We don’t even want to keep them as “private” variables on our pipeline, because someone would need to have known them originally and we’re using Azure AD anyways.

If you believe as I do that this is something that should probably have a built-in task, be sure to vote for my Feature request over at the developer community.