SQL Server Authentication and Authorization on Azure with Managed Identities

Deploying infrastructure to Azure is easy enough these days with Azure Powershell, CLI or Bicep. Making interactions between infrastructure components secure is also facilitated in Azure with Managed Identities for example. Databases however, often have their own authentication and authorization internally which is out of reach for Bicep and ARM templates. How can you secure your dynamically created SQL Server databases in Azure?

Context

For this post we need to place ourselves in a context where there is a database and some (web) application involved that need to communicate with each other in a secure way. Because Managed Identities is a promoted solution within Azure for secure interaction for all kinds of Azure products, we want to use that as well. We rather not have separate credentials for just the database connection. Connection Strings with secrets in them are also out of the question, because our imaginary company states that that is not compliant.

The landscape looks like this:

In this drawing we’ve already set up a connection with a Service Bus that uses a managed identity + a role assignment to exclusively give access to the app service.
Now we want to do something similar for the SQL Server connection. Sure, a database username and password could be stored in Azure Key Vault and the App Service could reference those secrets. But to me, it feels kind of strange that for the interaction with Service Bus no such construction is needed. So in this post I’ll not elaborate on that solution.
As said in the introduction, SQL Server doesn’t have Role Assignment functionality to give access to a specific database, let alone limit the actions the connection may perform on the database.
Users and authorizations are configured in the Security section of the database.
Our goal is to add our Managed Identity as a user and provide it with the ‘db_owner’ role.

Register users in SQL Server

A Managed Identity source of truth is within Azure AD. If we want to add a Managed Identity as a user to a SQL database we are not making a copy, we are just making a reference where the incoming identity can be looked up.

To add users to a database, we have to perform a SQL command in the database itself. There is simply no other way. This will cause a classic Chicken and Egg problem. When a database is created it’s completely empty including the registered users. So who is allowed to add the first user when no one is allowed access? Well that not entirely true, when the SQL Server itself is configured an administrator has to be appointed to perform the initial actions. It will have access to all databases under that SQL Server. This can be a basic username and password combination, or an existing Azure AD user can be assigned as administrator. This will be our entry point to configure the application-level authorizations.

A SQL Command can be executed through multiple routes. But we don’t want to give this responsibility to a human. Everything should be automated by design.
During my own implementation I’ve noticed that there are two types of commands that can be executed that will have the same end result

Option 1: From External

This approach is the most ‘smooth’ looking way of adding a user that originates from Azure AD

CREATE USER [NameOfManagedIdentity] FROM EXTERNAL PROVIDER;

It is as simple as that. Well, so I thought..
What happens under the hood is that basically we’re saying to SQL Server “Here is a name of a managed identity, it originates from in an external provider (read, Azure AD). Go look it up and store the details internally.”
There are a few pre-requisites to get this working though.

The SQL Command itself has to be performed under a session of an identity in Azure AD
The identity performing the query needs to have the ‘Azure Active Directory Graph > Directory.ReadAll’ permission

Unfortunately, I didn’t get this implementation working properly myself at first. Although I executed the command under an identity registered in Azure AD, with the required permissions and even more as described in Microsofts own documentation. It didn’t have enough permission to lookup the details of the Managed Identity during the execution of the command. It kept falling with the same error message.
After a while I discovered that it doesn’t matter what privileges the identity has that executes the SQL command, the identity of the SQL Server itself has to be granted Directory Reader access in order to lookup the AD user and register it within SQL Server.

Option 2: Calculating the SID

Because option 1 didn’t work for me at first. Luckily there was an alternative way to register the Managed Identity in a SQL database.

CREATE USER [$managedIdentityName] WITH DEFAULT_SCHEMA=[dbo], SID =
$sid, TYPE = E;

The AD lookup is needed to retrieve a unique identifier to store with the new SQL User this is called a SID, when later authentication attempts take place, the identity can be verified. This identifier can be calculated locally without the need of an Azure AD within SQL Server. This way, there is no need for a runtime lookup during the registration, yet the managed identity of the application will still be recognized and allowed access. In the example below a complete Powershell script is shown to calculate the SID and execute the SQL Query.

param ( 
[Parameter(Mandatory)][ValidateNotNullOrEmpty()][string]$managedIdentityName,    [Parameter(Mandatory)][ValidateNotNullOrEmpty()][string]$sqlServerHost,
[Parameter(Mandatory)][ValidateNotNullOrEmpty()][string]$databaseName
)

$ErrorActionPreference = 
"Stop"

# The calculation of the SID manually has been chosen after the AD integrated user lookup didn't work

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

    return "0x" + $byteGuid
}

$applicationId = (Get-AzADServicePrincipal -DisplayName $managedIdentityName).AppId

Write-Host "For identity name $managedIdentityName belongs $applicationId as application id"

$sid = ConvertTo-Sid -ApplicationId $applicationId

$ddlstmt = "
IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name ='$managedIdentityName')
BEGIN
CREATE USER [$managedIdentityName] WITH DEFAULT_SCHEMA=[dbo], SID = $sid, TYPE = E;
END
IF IS_ROLEMEMBER('db_owner','$managedIdentityName') = 0
BEGIN
ALTER ROLE db_owner ADD MEMBER [$managedIdentityName]
END
"

Write-Host "Get an access token for managed identities for Azure resources"

$access_token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net').Token

 Write-Host "Create SQL connection string"
 $conn = New-Object System.Data.SqlClient.SQLConnection

$conn.ConnectionString =
"Data Source=$sqlServerHost;Initial Catalog=$databaseName;Connect Timeout=30"

$conn.AccessToken = $access_token

Write-host "Connect to database $database"
$conn.Open()

Write-host "SQL DDL command to be executed" $ddlstmt
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($ddlstmt,
$conn)      

Write-host "Executing command"
$command.ExecuteNonQuery()

Write-host "Close connection"
$conn.Close()

Going from top to bottom, the first thing that springs out is the function ConvertTo-Sid. This function is defined for later use to calculate the SID from an application ID.

For the procedure itself first the managed identity is looked up in Azure AD. Because it is an entry in Azure AD an application id registered with it. This application id is needed to calculate the SID using a byte array conversion.
Once the SID is known the SQL command can be built up. First the User is created in SQL Server, then the correct role can be assigned to this user

CREATE USER [$managedIdentityName] WITH DEFAULT_SCHEMA=[dbo], SID = $sid, TYPE = E;

ALTER ROLE db_owner ADD MEMBER [$managedIdentityName]

And that is basically the only thing that needs to happen in SQL Server. The rest of the script is purely executing this command against the database. Notice the access token that is retrieved.

$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net').Token

Get-AzAccessToken will use the current Powershell Identity to get the token for in this case database interaction.
This token is then added to the connection just before the SQL command is executed:

$conn.AccessToken = $access_token

Executing the procedure

The last important step is to execute the above powershell under the identity which is also administrator of the SQL Server. What worked for me is to create a Service Principal to assign this role and to store its credentials in a Azure Devops Service Connection.
This way you can create a pipeline in Azure Devops with the AzurePowershell Task that can run the above Powershell using the service connection. Essentially running the Powershell as the SQL Server Administrator.

- task: AzurePowerShell@5
  displayName: Configure database access
  inputs:
    azureSubscription: 'AzureServiceConnection'
    scriptPath: Scripts/configureDatabaseAccess.ps1
    scriptArguments: >-
      -managedIdentityName 'MyManagedIdentity'
      -SQLServerHost blog-dbserver.database.windows.net
      -DatabaseName 'BlogDb'
    pwsh: true
    azurePowerShellVersion: latestVersion

The end result is a registered managed identity in SQL Server:

Query result after users are created and roles has been assigned

In this example two external users have been added, which are Managed Identities registered in Azure AD.

Connecting to the database

On the application side, things now got simpler. There are only two things needed

The managed identity registered in the SQL database needs to associated with the App Service
The connection string should mention to only use Azure AD without credentials to connect to the database.

The managed identity can be easily associated with the App Service by either the Azure Portal or by configuring the following Bicep

resource managedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2022-01-31-preview' existing = {
  name: 'myManagedIdentity'
}

resource appService 'Microsoft.Web/sites@2021-03-01' = {
  name: appServiceName
  location: location
  properties: {
              ...
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${managedIdentity.id}': {}
  }
}

The connection string of the App Service should look like this

Server=tcp:dbserver.database.windows.net,1433;Initial Catalog=BlogDb;User Id=GUID-OF-MANAGED-IDENTITY;Authentication=Active Directory Managed Identity;

Now your app service should be able to retrieve an Azure AD access token by itself and make itself known to the SQL Server instance.

Conclusion

In the end, I’m very satisfied that it is possible to use a Managed Identity also for SQL Server integration. One less secret to worry about in either Key Vault or the App settings of the application. What struck me during the implementation is the amount of disclaimers, warnings and not always obvious instructions in the Microsoft documentation. It still doesn’t feel mature enough to qualify it as ‘Plug ‘n Play’. I’m glad I’ve got both approaches working in order to get the project I was working on moving.
Because other blogposts on this topic are in my view not in depth enough, I had the urge to write this article to help others struggling with this. I hope this will help!