How to connect to SQL Server using workload identity in AKS using .NET framework?

If your error message says "login failed for identity principal user," it indicates that the permissions you have in SQL Server for the identity principal are insufficient. The identity has to have sufficient permissions to the SQL server. Use workload identity to set up and connect your SQL Server in the AKS cluster. To do so, ensure you have a managed identity available.

az identity create --resource-group arkorg --name downtime-identity

Note down the client ID and resource ID of the above-managed identity and assign the Managed Identity to the AKS cluster.

az aks update --resource-group arkorg --name downtime --enable-managed-identity --assign-identity <resource-id-of-downtime-identity>

Create an Azure AD User in SQL Server

Grant Necessary Permissions to the Managed Identity

CREATE USER [<client-id-of-downtime-identity>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<client-id-of-downtime-identity>];
ALTER ROLE db_datawriter ADD MEMBER [<client-id-of-downtime-identity>];

Update Your .NET Framework Application. Look at the Active Directory Managed Identity authentication section for better clarity.

Update your deployment YAML with the azure.workload.identity/client-id annotation

apiVersion: apps/v1
kind: Deployment
metadata:
  name: sql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sql-connection-app
  template:
    metadata:
      labels:
        app: sql-connection-app
      annotations:
        azure.workload.identity/client-id: 03843c85-abcd-efg-hijk-51f37blmnop
    spec:
      containers:
      - name: sql-connection-app
        image: arkoacr.azurecr.io/sql-connection-app:latest
        ports:
        - containerPort: 80

Reference:

• Similar issue on Github
• Connect to Azure SQL with Microsoft Entra authentication and SqlClient
• Use Microsoft Entra Workload ID with Azure Kubernetes Service (AKS)