Unable to connect to Azure SQL from Kubernetes (AKS) web app container using workload identity

Question

I have an Azure Kubernetes cluster hosting a web app which I want to talk to an Azure SQL database.

I have verified that the connection to SQL works fine when using a password-based connection string like this:

Server=tcp:{DatabaseServerName}.database.windows.net,1433;Initial Catalog={DatabaseName};User ID={UserName};Password=\"{Password}\";Persist Security Info=False;Integrated Security=false;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;

The app also already has a workload identity already working to connect to other resources (e.g. key vault). In other words I have a service account with the azure.workload.identity/client-id annotation set to the client ID of a managed identity (let's call this {UserClientId}), the pod has the label azure.workload.identity/use=true, and the managed identity itself is correctly set up with the federated credentials. The pod itself has the following environment variables added:

AZURE_AUTHORITY_HOST : https://login.microsoftonline.com/
AZURE_CLIENT_ID : {UserClientId}
AZURE_FEDERATED_TOKEN_FILE : /var/run/secrets/azure/tokens/azure-identity-token
AZURE_TENANT_ID : {MyTenantId}

DefaultAzureCredentials in the app successfully obtains tokens that work with resources that has appropriate RBAC roles assigned.

With all this working I assumed getting the managed identity working with a SQL connection would be fairy easy because I've done this before in Azure functions.

I start by registering the managed identity as an external user on the database as follows:

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = '{AppUserName}')
BEGIN
    CREATE USER [{AppUserName}] WITH DEFAULT_SCHEMA=[dbo], SID = {AppUserSid}, TYPE = E
END

IF IS_ROLEMEMBER('db_owner','{AppUserName}') = 0
BEGIN
    ALTER ROLE db_owner ADD MEMBER [{AppUserName}]
END

Here, {AppUserName} is the actual name of the managed identity, and {AppUserSid} is a representation of its client ID ({UserClientId} above) run through this Powershell function:

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

With that done I just swap the connection string I mentioned at the top of this post for the following:

Server=tcp:{DatabaseServerName}.database.windows.net,1433;Initial Catalog={DatabaseName};User ID={UserClientId};Authentication=Active Directory Managed Identity;Persist Security Info=False;Integrated Security=false;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;

So, the user ID becomes the client ID of the workload identity's managed identity. I thought everything then would just work. But instead I get the following SQL exception:

{
    "id": "28795643",
    "outerId": "5601771",
    "type": "Microsoft.Data.SqlClient.SqlException",
    "message": "Received a non-retryable error. Identity Response Code: BadRequest, Response: {\"error\":\"invalid_request\",\"error_description\":\"Identity not found\"}",
    "severityLevel": "Error",
    "parsedStack": [
        {
            "level": 0,
            "method": "Microsoft.Data.SqlClient.AzureManagedIdentityAuthenticationProvider+<AcquireTokenAsync>d__13.MoveNext",
            "assembly": "Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5",
            "line": 0
        }
    ]
}

Key part: Identity not found

So can anyone help me understand what I've missed? To pre-empt some questions, just to clarify:

• The connection works fine when I switch to a password-based connection string so we don't need to talk about networking
• The workload identity already works fine to connect to other Azure resources

Many thanks in advance for your time, I appreciate it's a long post :)