How to fix exception Microsoft.Data.SqlClient.SqlException (0x80131904): Failed to authenticate the user ""

Question

Hello,

I am learning AKS and ACR through my test microservices.
I am trying to connect AKS pod (deployed .NET microservice container) to Azure SQL Database, but I keep getting this exception after the AKS pod is created:
`Microsoft.Data.SqlClient.SqlException (0x80131904): Failed to authenticate the user my-username in Active Directory (Authentication=ActiveDirectoryIntegrated).

Error code 0xintegrated_windows_auth_not_supported_managed_user

Integrated Windows Auth is not supported for managed users. See https://aka.ms/msal-net-iwa for details.

A brief explanation of my configuration:
Connection string: No matter what type of SQL DB connection string I use (Microsoft Entra passwordless authentication, Microsoft Entra password authentication, Microsoft Entra integrated authentication), I get the same exception.
Containerization - I publish the app, build the image, tag it and push it to ACR. AKS is connected to ACR.
For the deploy configuration I have only 2 yml files:

deployment.yml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: identityapi
spec:
  replicas: 1
  selector:
    matchLabels:
      app: identityapi
  template:
    metadata:
      labels:
        app: identityapi
    spec:
      containers:
      - name: identityapi-container
        image: bluetoothspeakerstore.azurecr.io/identityapi:BSS
        ports:
        - containerPort: 5268
      imagePullSecrets:
      - name: arhsbulgaria
        

service.yml:

apiVersion: v1
kind: Service
metadata:
  name: identityapi-service
spec:
  type: LoadBalancer
  selector:
    app: identityapi
  ports:
    - protocol: TCP
      port: 81  # External port
      targetPort: 5268  # Internal container port

The AKS also is configured with VNet, which is in the Firewall rules exceptions.
My locally hosted app with the connection string type Microsoft Entra passwordless authentication successfully connects to the db.

I tried everything, every article and nothing. I came across this blog:
https://programmingwithwolfgang.com/aad-authentication-for-applications-running-in-aks-to-access-azure-sql-databases/

Where I followed the steps including creation of managed identity, the query through the database and the creation of the yml files. I created 2 separate yml files which I apply to the deployment (I don't use Helm):
aadpodidentity.yml:

apiVersion: "aadpodidentity.k8s.io/v1"
kind: AzureIdentity
metadata:
  name: managedidentity-aks
spec:
  type: 0
  resourceID: my-res-id
  clientID: my-client-id

aadpodidentitybinding.yml:

apiVersion: "aadpodidentity.k8s.io/v1"
kind: AzureIdentityBinding
metadata:
  name: identityapi-azure-id-binding
spec:
  azureIdentity: managedidentity-aks
  selector: identityapi

Still, the error is the same.
I tried through the Azure UI to add Contributor roles for this managed identity in both AKS Cluster and AzureSQL, still no effect.

Can anyone advise what am I doing wrong, maybe the whole AKS-to-AzureSQL is not a standard procedure.
Any help is welcome. I repeat that I am using a test app.

Answer 1

Hello again,

This authentication flow was the proper one to use, at least in my case. All the tutorials that I followed were correct, it was just that I did not properly write the yml file, where the actual authentication data is extracted.
Here are the edited yml configurations that fixed my problem:

service.yml:

apiVersion: v1
kind: Service
metadata:
  name: identityapi-service
spec:
  type: LoadBalancer
  selector:
    app: aadtest1
  ports:
    - protocol: TCP
      port: 81  # External port
      targetPort: 5268  # Internal container port

deployment.yml:

apiVersion: v1
kind: Pod
metadata:
  name: aadtest1
  labels:
    aadpodidbinding: sqlaad
spec:
  containers:
  - name: identityapi-container
    image: bluetoothspeakerstore.azurecr.io/identityapi:BSS
    imagePullPolicy: Always
    ports:
    - containerPort: 5268
    env:
    - name: SERVER_NAME
      value: bss-previewdb.database.windows.net
    - name: DATABASE_NAME
      value: BSS-Db
  imagePullSecrets:
  - name: arhsbulgaria

aadpodidentity.yml:

apiVersion: "aadpodidentity.k8s.io/v1"
kind: AzureIdentity
metadata:
  name: sqlaad1
spec:
  type: 0
  resourceID: myResourceId
  clientID: myClientId

aadpodidentitybinding.yml:

apiVersion: "aadpodidentity.k8s.io/v1"
kind: AzureIdentityBinding
metadata:
  name: sqlaadbinding1
spec:
  azureIdentity: sqlaad1
  selector: sqlaad

Basically the main issue was in my deployment.yml file where it needed to be specified as "Pod" and not deployment.
I am very thankful to the creator of this blog:

https://trstringer.com/connect-k8s-apps-msi/