MSSQL trigger can't connect to SQL Server on Azure using azure-workload auth

[davesheldon]

Report

We have a managed identity keda-dev that we use successfully via the azure-workload provider to auth against ServiceBus. However, when attempting to use the same managed identity via TriggerAuthentication with the mssql trigger type against an Azure-hosted database (and after adding the appropriate DB permissions), the SQL login is failing.

Expected Behavior

I didn't see anything in the documentation that says whether this is expected to work or not work, but I would generally expect the mssql trigger type to support the azure-workload auth provider, especially when the database being targeted is hosted on Azure.

Actual Behavior

We are receiving an error from KEDA: error establishing mssql connection: mssql: login error: Login failed for user ''.

Steps to Reproduce the Problem

1. Set up a managed identity with a federated credential and database access
2. Create scaled job with a trigger of type mssql and an azure-workload auth provider, using the managed identity's client id as the identityId
3. Observe the scaled job's failure to connect to the database

Logs from KEDA operator

2024-08-27T15:31:00Z	INFO	Reconciling ScaledJob	{"controller": "scaledjob", "controllerGroup": "keda.sh", "controllerKind": "ScaledJob", "ScaledJob": {"name":"core-notifications-processor","namespace":"dev"}, "namespace": "dev", "name": "core-notifications-processor", "reconcileID": "9c9e8bfc-19c4-4700-bf9b-448bc341d51c"}
2024-08-27T15:31:00Z	ERROR	mssql_scaler	Found error pinging mssql: mssql: login error: Login failed for user ''.	{"type": "ScaledJob", "namespace": "dev", "name": "core-notifications-processor", "error": "mssql: login error: Login failed for user ''."}
2024-08-27T15:31:00Z	ERROR	scale_handler	error resolving auth params	{"type": "ScaledJob", "namespace": "dev", "name": "core-notifications-processor", "triggerIndex": 0, "error": "error establishing mssql connection: mssql: login error: Login failed for user ''."}
2024-08-27T15:31:00Z	ERROR	Error getting scalers	{"controller": "scaledjob", "controllerGroup": "keda.sh", "controllerKind": "ScaledJob", "ScaledJob": {"name":"core-notifications-processor","namespace":"dev"}, "namespace": "dev", "name": "core-notifications-processor", "reconcileID": "9c9e8bfc-19c4-4700-bf9b-448bc341d51c", "error": "error establishing mssql connection: mssql: login error: Login failed for user ''."}
2024-08-27T15:31:00Z	ERROR	Failed to ensure ScaledJob is correctly created	{"controller": "scaledjob", "controllerGroup": "keda.sh", "controllerKind": "ScaledJob", "ScaledJob": {"name":"core-notifications-processor","namespace":"dev"}, "namespace": "dev", "name": "core-notifications-processor", "reconcileID": "9c9e8bfc-19c4-4700-bf9b-448bc341d51c", "error": "error establishing mssql connection: mssql: login error: Login failed for user ''."}
2024-08-27T15:31:00Z	ERROR	Reconciler error	{"controller": "scaledjob", "controllerGroup": "keda.sh", "controllerKind": "ScaledJob", "ScaledJob": {"name":"core-notifications-processor","namespace":"dev"}, "namespace": "dev", "name": "core-notifications-processor", "reconcileID": "9c9e8bfc-19c4-4700-bf9b-448bc341d51c", "error": "error establishing mssql connection: mssql: login error: Login failed for user ''."}

KEDA Version

2.14.1

Kubernetes Version

1.29

Platform

Microsoft Azure

Scaler Details

MSSQL

Anything else?

Here are the manifests I'm using, with some information redacted...

apiVersion: keda.sh/v1alpha1
kind: ScaledJob
metadata:
  name: core-notifications-processor
  labels:
    app: core-notifications-processor
spec:
  jobTargetRef:
    parallelism: 1
    activeDeadlineSeconds: 3600
    backoffLimit: 3
    template:
      metadata:
        labels:
          app: REDACTED
      spec:
        serviceAccountName: REDACTED
        containers:
        - name: REDACTED
          image: ebacr.azurecr.io/REDACTED
          resources:
            limits:
              memory: "4Gi"
              cpu: "1.0"
            requests:
              memory: "128Mi"
              cpu: "100m"
          env:
          - name: ConnectionStrings__Db
            value: Server=tcp:REDACTED.database.windows.net,1433;Initial Catalog=REDACTED;Authentication=Active Directory Managed Identity;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Command Timeout=30;
  triggers:
  - type: mssql
    metadata:
      connectionStringFromEnv: ConnectionStrings__Db
      query: |
        REDACTED
      targetValue: "1000"
      activationTargetValue: "1"
    authenticationRef:
        name: core-notifications-processor-auth
  pollingInterval: 5
  successfulJobsHistoryLimit: 5
  failedJobsHistoryLimit: 5
  maxReplicaCount: 5

apiVersion: keda.sh/v1alpha1
kind: TriggerAuthentication
metadata:
  name: core-notifications-processor-auth
spec:
  podIdentity:
    provider: azure-workload
    identityId: REDACTED

[JorTurFer]

Hello,
MSSQL Scaler doesn't support managed identities authentication. This can be a nice feature to implement if you're willing to do it.