SQL Server AAD Azure Auth method doesn't work if multiple User assigned identies are existing.

Jan747 commented 5 months ago

Relevant telegraf.conf

inputs:
    - sqlserver:
        interval: "30s"
        servers:
          -"Server=dbserver.database.windows.net;Port=1433;database=dbname;hostNameInCertificate=*.database.windows.net;TrustServerCertificate=true;app name=telegraf;log=1;"
        auth_method: "AAD"
        database_type: "AzureSQLDB"
        exclude_query: 
          - "AzureSQLDBSchedulers"
          - "AzureSQLDBRequests"

Logs from Telegraf

[inputs.sqlserver] Error in plugin: error creating AAD token provider for system assigned Azure managed identity : adal: Refresh request failed. Status Code = '400'. Response body: {"error":"invalid_request","error_description":"Multiple user assigned identities exist, please specify the clientId / resourceId of the identity in the token request"}

System info

Telegraf 1.25.0-alpine; Kubernetes 1.30; Azure VM with two MIs

Docker

No response

Steps to reproduce

Create Azure VM and install Kubernetes
Assign two identities to the created VM.
Create Azure SQL DB
Deploy and setup Telegraf

Expected behavior

The option to select the desired Managed Identity(MI) or to specify its name in the Conf, if more then one MI is assigned to a vm.

At the top of the error log it is called clientId / resourceId.

Actual behavior

Authentication to the SQL DB isn't possible.

Additional info

No response

powersj commented 5 months ago

Hi,

The error is coming during this call to refreshToken(). Following that down, I am thinking it comes from the call to NewServicePrincipalTokenFromMSI where we hardcode the resourceID as https://database.windows.net/.

Does that seem like the value that needs to be user-configurable?

Jan747 commented 5 months ago

Hello @powersj ,

thank you for your hints. I think in my case we need a new variable userAssignedID. And create a case if userAssigenedID is given it should use NewServicePrincipalTokenFromMSIWithUserAssignedID to get the new token instead of NewServicePrincipalTokenFromMSI.

Also some other finding: The base libaray https://github.com/Azure/go-autorest/tree/main/autorest/adal is out off support. "This module will go out of support by March 31, 2023. For authenticating with Azure AD, use module azidentity instead."

So a migration from adal to azidentiy is needed?

Jan747 commented 5 months ago

Hey I created a PR. Can someone review it. I am not a golang developer please have a look.

influxdata / telegraf