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 :)

Answer 1

The answer in my case was that I was using a 3rd party library that referenced an old version of Microsoft.Data.SqlClient.

WorkloadIdentityCredential has only been supported since v5.1.4 of this library as documented here. Adding an explicit reference to this version or above made the setup described in my OP just work :)