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

davesheldon commented 3 months ago

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

Set up a managed identity with a federated credential and database access
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
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 commented 2 months ago

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

stale[bot] commented 4 weeks ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 7 days if no further activity occurs. Thank you for your contributions.

stale[bot] commented 2 weeks ago

This issue has been automatically closed due to inactivity.

tapas28 commented 1 week ago

Hi Team, We are also trying to use MSSQL trigger for scaling application and we are trying to authenticate the SQL DB using Azure Service Principle. But no luck. We are keeping connection string in Kubernet Secret and trying to use this. but no luck. Getting login failed error like "ERROR mssql_scaler Found error pinging mssql: mssql: login error: Login failed for user xxxxxxxxx". Any solution or work around for this? But every thing is working fine when we are using SQL Authentication instead of service principal. Below is the connection string format we are using - kubectl create secret generic my-mssql-secrets --from-literal mssql-connection-string="server=azuse2sqlmixxxxx.xxxxxxx.database.windows.net;Authentication=ActiveDirectoryServicePrincipal;Initial Catalog=yyyyyy;User Id=99775ec3-xxxxxx-xxxx-xxx;Password=xxxxxxxx;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;" Please help here if you have any solution or work around.

JorTurFer commented 2 days ago

Hi Team, We are also trying to use MSSQL trigger for scaling application and we are trying to authenticate the SQL DB using Azure Service Principle. But no luck. We are keeping connection string in Kubernet Secret and trying to use this. but no luck. Getting login failed error like "ERROR mssql_scaler Found error pinging mssql: mssql: login error: Login failed for user xxxxxxxxx". Any solution or work around for this? But every thing is working fine when we are using SQL Authentication instead of service principal. Below is the connection string format we are using - kubectl create secret generic my-mssql-secrets --from-literal mssql-connection-string="server=azuse2sqlmixxxxx.xxxxxxx.database.windows.net;Authentication=ActiveDirectoryServicePrincipal;Initial Catalog=yyyyyy;User Id=99775ec3-xxxxxx-xxxx-xxx;Password=xxxxxxxx;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;" Please help here if you have any solution or work around.

Is this related with workload identity?

kedacore / keda