Deploying Azure SQL AD Auth with Managed Identities via Azure DevOps

Using Azure AD to authenticate a resource such as an App Service or Function App against a SQL database is great. Removing the need for credentials in connection strings is, just by itself, enough of a reason to make the switch away from using a SQL account to manage your app’s access to a database.

There are some configuration steps to achieving this, some of which are one-offs and some of which will impact your automated release pipeline. I’ll cover the one-off items briefly and then discuss what we added to our pipeline to ensure that AD auth is always configured correctly for our various apps, without the need to reference any credentials or secrets.

This article assumes a system-assigned managed identity for the app, however the process should be similar for a user-assigned identity (albeit with an extra ARM template resource and different principal name).

Initial Configuration

The first thing you’ll need to create is an Azure AD Group to hold your SQL Admins. We will assign this group as the AD Admin for the SQL server during deployment using our ARM template, however, for the time being it must exist and contain any AD users that should be SQL Admins in your particular setup.

The second thing is a Service Connection from Azure DevOps that’s linked to an App Registration in your Azure AD. This App Registration must have the following properties:

1. Contributor role on the Azure subscription to which you are deploying. This gives it access to create a resource group and the resources within it.
2. Azure Active Directory Graph -> Directory.Read.All permission. This allows it to find the service principal for your app’s managed identity in Azure AD — more on this later.
3. Membership of your SQL Admins AD group. This will allow it to create new SQL users and assign them roles as part of your deployment pipeline — more on this later.

ARM Template Resources

For the purpose of this article, we’ll set up an Azure App Service that speaks to an Azure SQL database living on an Azure SQL server. The SQL server will have as its AD Admin the SQL Admins AD group you already created.

The App Service will have a system-assigned managed identity, which is essentially a service principal that’s bound to the App Service and lives in Azure AD under the name of the App Service. Its lifecycle matches that of the App Service, so if the App Service is deleted, so is the managed identity.

The managed identity is how the App Service will authenticate against the SQL database in order to query the data.

The core ARM template configuration for the SQL Server is:

{
  "type": "Microsoft.Sql/servers",
  "properties": {
    "administrators": {
      "administratorType": "ActiveDirectory",
      "principalType": "Group",
      "login": "<name of your SQL Admins group>",
      "sid": "<object ID of your SQL Admins group>",
      "tenantId": "<your Azure tenant ID>",
      "azureADOnlyAuthentication": true
    }
  }
}

NB: The ARM snippets I include are not fully complete, but stripped back to more clearly demonstrate the key pieces of configuration for this setup. Therefore otherwise important properties such as name and dependsOn are omitted.

The database requires no special configuration in the ARM template outside of ensuring its name is prefixed with the SQL server name and a forward slash to make sure it’s created on our deployed Azure SQL server instance. (You’ll want to deploy your specific DACPAC once the Azure SQL database resource has been created as well.)

The core ARM template configuration for the App Service is:

{
  "type": "Microsoft.Web/sites",
  "name": "<name of your App Service>",
  "kind": "app",
  "identity": {
    "type": "SystemAssigned"
  }
}

This is leaving out a lot for brevity, but the identity property is how you ensure that your App Service will be deployed with a system-assigned managed identity.

I include the name field, as this is relevant because it’s also going to be the name of the managed identity that gets assigned to your App Service.

SQL Access Token

In order for our app service to speak to our database using its managed identity, we must grant the managed identity access to the database and assign it the necessary roles to query the data.

To do this manually is very simple. You can just run the following query against your deployed database to create a SQL user that represents your managed identity and assign it the correct roles:

CREATE USER [<name of your App Service>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<name of your App Service>];
ALTER ROLE db_datawriter ADD MEMBER [<name of your App Service>];
GO

However, we don’t want to have to do this manually every time — we’d much rather automate it as part of the deployment pipeline.

Doing this requires running a SQL query from within the context of the pipeline, which in turn requires a valid access token for our deployment service principal (i.e., the service principal for the App Registration representing our Service Connection that was set up in the initial configuration — this is the auth context our deployment pipeline runs under whenever it’s connected to our Azure tenant).

Obtaining a token might, under different circumstances, require passing credentials or a secret from Key Vault or similar. Fortunately however, because the deployment service principal has already authenticated against Azure AD in order to be executing pipeline tasks against our Azure tenant, we don’t need to re-authenticate and can instead request a token for executing SQL as part of our pipeline.

Therefore, to request a token and execute a SQL query we need to use a DevOps task that runs in the context of the Service Connection, and also allows us to connect to a database and execute queries. The Azure PowerShell task is perfect for this:

Azure PowerShell DevOps task

The PowerShell to obtain the token and open a connection to the database is as follows:

param(
 [Parameter(Mandatory)]
 [string]$sqlServerName,
 [Parameter(Mandatory)]
 [string]$sqlDatabaseName,
 [Parameter(Mandatory)]
 [string]$appServiceName
)
# Retrieve the access token
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$connectionString = "Server=tcp:$sqlServerName.database.windows.net,1433;Initial Catalog=$sqlDatabaseName;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = $connectionString
$conn.AccessToken = $token
$conn.Open()

Another way in which the Azure PowerShell task is handy is that it pulls in the Az module automatically, giving us access to commands such as Get-AzAccessToken without requiring any extra Install or Import steps

So now we have our token and an open connection to our database. At this point we should be able to just run our SQL query line-for-line as we have it above, right? Sadly it’s not quite that simple…

SQL Query

CREATE USER [<name of your App Service>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<name of your App Service>];
ALTER ROLE db_datawriter ADD MEMBER [<name of your App Service>];
GO

If we try running our SQL query via PowerShell at this point, it will fail. It seems that the FROM EXTERNAL PROVIDER command won’t work if accessing the database in this way, and it will fail to resolve the managed identity you’re trying to give access to.

There is another way though! If you run the above query manually and then use this SQL to query the database for all principals with access, you’ll see how the user has been granted its access:

SELECT [name], [type], [sid]
FROM [sys].[database_principals]

Type E principal with SID

Line 6 above is our managed identity user. You’ll notice two things: firstly that the type is ‘E’, and secondly that the SID format is different to other users/roles (it’s shorter).

Type E refers to an Azure AD user (Azure AD being the External Provider in our FROM EXTERNAL PROVIDER query). There are other types, such as ‘X’ for an Azure AD group, but this is the type we’re interested in here.

As for the SID, it’s much shorter because the SID for a SQL user representing an Azure service principal is based on the Application ID for that principal. Therefore, we can reverse engineer this process and generate the correct SID ourselves in the PowerShell script!

So there are three things here. Our script needs to:

1. Retrieve the Application ID for the service principal of our app’s managed identity
2. Convert that ID into a SID that our SQL database understands
3. Run a script to create the SQL user, making use of the SID rather than FROM EXTERNAL PROVIDER which doesn’t work

Retrieving the service principal’s Application ID can be done using the GetAzADServicePrincipal command:

$spId = (Get-AzADServicePrincipal -DisplayName $appServiceName).ApplicationId

This is where our Service Connection’s App Registration requires the Directory.Read.All permission for the Azure Active Directory Graph as mentioned earlier.

For the SID conversion, we’ll make use of a utility function that converts a GUID into a SID:

function ConvertTo-Sid {
    param (
        [string]$id
    )
    [guid]$guid = [System.Guid]::Parse($id)
    foreach ($byte in $guid.ToByteArray()) {
        $byteGuid += [System.String]::Format("{0:X2}", $byte)
    }
    return "0x" + $byteGuid
}
$sid = ConvertTo-Sid -id $spId

And lastly, the SQL query to create the user is as follows:

$query =
    "IF NOT EXISTS (
        SELECT TOP (1) [name]
        FROM [sys].[database_principals]
        WHERE [type] = N'E' AND [name] = N'$appServiceName'
    )
    BEGIN
        CREATE USER [$appServiceName] WITH DEFAULT_SCHEMA=[dbo], SID = $sid, TYPE = E;
        ALTER ROLE db_datareader ADD MEMBER [$appServiceName];
        ALTER ROLE db_datawriter ADD MEMBER [$appServiceName];
    END"

So if we insert all of this into our Azure PowerShell script, we have the following complete script:

param(
 [Parameter(Mandatory)]
 [string]$sqlServerName,
 [Parameter(Mandatory)]
 [string]$sqlDatabaseName,
 [Parameter(Mandatory)]
 [string]$appServiceName
)
function ConvertTo-Sid {
    param (
        [string]$id
    )
    [guid]$guid = [System.Guid]::Parse($id)
    foreach ($byte in $guid.ToByteArray()) {
        $byteGuid += [System.String]::Format("{0:X2}", $byte)
    }
    return "0x" + $byteGuid
}
# Retrieve the App ID of the service principal
$spId = (Get-AzADServicePrincipal -DisplayName $appServiceName).ApplicationId
# Convert App ID to SID
$sid = ConvertTo-Sid -id $spId
# Retrieve the access token
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$connectionString = "Server=tcp:$sqlServerName.database.windows.net,1433;Initial Catalog=$sqlDatabaseName;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = $connectionString
$conn.AccessToken = $token
$query =
    "IF NOT EXISTS (
        SELECT TOP (1) [name]
        FROM [sys].[database_principals]
        WHERE [type] = N'E' AND [name] = N'$appServiceName'
    )
    BEGIN
        CREATE USER [$appServiceName] WITH DEFAULT_SCHEMA=[dbo], SID = $sid, TYPE = E;
        ALTER ROLE db_datareader ADD MEMBER [$appServiceName];
        ALTER ROLE db_datawriter ADD MEMBER [$appServiceName];
    END"
$conn.Open()
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)
$command.ExecuteNonQuery() > $null
$conn.Close()

You can then run this from your YAML pipeline and pass in the parameters as you would with any other Azure PowerShell task:

- task: AzurePowerShell@5
  displayName: "Grant Managed Identity SQL Access"
  inputs:
    azureSubscription: <your Service Connection name>
    azurePowerShellVersion: LatestVersion
    ScriptPath: '<path to the PS script>'
    ScriptArguments: >-
      -sqlServerName $(SQLServerName)
      -sqlDatabaseName $(SQLDatabaseName)
      -appServiceName $(AppServiceName)

Conclusion

So that’s us done. We’ve now configured a pipeline to deploy an App Service that speaks to a SQL database purely using Azure AD auth, and we didn’t even have to reference any credentials or secrets in our pipeline along the way 🎉

You could easily extend this to work for a user-assigned managed identity, or an AD group (user type ‘X’), or a vanilla AD user (user type ‘E’ with UPN as name). In those cases you’d have to use GetAzADGroup and GetAzADUser respectively rather than getting the service principal of your managed identity, but the surrounding configuration is much the same.

It’s also worth mentioning that it’s possible to do this stuff using Azure CLI as opposed to the Az PowerShell module, however, I found that I was getting an error when trying to open the SQL connection from within the context of an Azure CLI task (rather than the Azure PowerShell task), so I opted for the PS route instead. You could of course have a separate Azure CLI task for getting the token and then pass this into an Azure PowerShell task, but it’s preferable to not have to manage the token as a pipeline variable, however briefly!

Photo by Campaign Creators on Unsplash