Support User-Assigned Managed Identity Auth to Azure SQL

[wkelly74]

What happened?

We have multiple container apps trying to connect to an Azure SQL Database. These container apps all use the same user-assigned managed identity (UAMI) and system-assigned managed identity is not enabled. We have permissioned this UAMI in Azure SQL. When trying to auth into Azure SQL with it (by just omitting a username and password from the connection string as directed in documentation) we get the error message indicated in the relevant log output.

We would expect to be able to authenticate with this credential and access SQL with the UAMI. This gives us the benefit of not having to add and remove system-assigned managed identities in SQL Security as container apps get added or removed.

Version

0.7.6

What database are you using?

Azure SQL

What hosting model are you using?

Container Apps

Which API approach are you accessing DAB through?

REST, GraphQL

Relevant log output

Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity <EntityName> with underlying database object source: <DatabaseObject> due to: ManagedIdentityCredential authentication failed: Service request failed.

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[seantleonard]

Thanks for opening this issue. Adding to the backlog.

---

For future reference when we or another contributor takes this up:

1. Add runtime config property to capture Managed Identity resource or client id.
2. Update the DefaultAzureCredential constructor in the QueryExecutor classes to take a DefaultAzureCredentialOptions with either ManagedIdentityResourceId or ManagedIdentityClientId property assigned based on values provided by an additional runtime config property.

Ref: https://learn.microsoft.com/en-us/dotnet/api/azure.identity.defaultazurecredentialoptions?view=azure-dotnet#:~:text=ManagedIdentityClientId,ManagedIdentityClientId%20should%20not%20be%20configured.

[jarrydpage]

I thought I'd take a look at implementing this as I have the same requirements (UMI, ACA, and DAB):

https://github.com/jarrydpage/data-api-builder/tree/dev/jarrydpage/user-managed-identity

Real simple addition at the moment, adding in an additional runtime property:

{
    "runtime": {
        "identity": {
            "managed-identity": "aaca856b-xxxx-xxxx-xxxx-7296fb9ae9f5"
        }
    }
}

This uses a standard AZURE_CONNECTION_STRING: Server=tcp:xxx.database.windows.net,1433; Initial Catalog=xxx; Authentication=Active Directory Default;

However, when looking into this, I did come across the documentation for SqlClient which mentions an alternative way to configure this:

https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-managed-identity-authentication

Server=demo.database.windows.net; Initial Catalog=xxx; Authentication=Active Directory Managed Identity; User Id=ClientIdOfManagedIdentity;

I was able to confirm this works with connectivity to an Azure SQL database from a container app with a user-assigned managed identity. This may be a better alternative than trying to accomodate all potential options, unless there's a change in the behaviour of SqlClient in the future?

[seantleonard]

@jarrydpage thank you for taking a look at this and writing up a POC! Much appreciated. Just to make sure I understand, you were able to get UAMI to work without changing DAB code and instead, only needed to provide the managed identity clientID + specific authN type in the connection string?

Authentication=Active Directory Managed Identity; User Id=ClientIdOfManagedIdentity;

[jarrydpage]

@seantleonard yup, that's correct. I was able to successfully connect using the latest version of DAB at the time.

[bkelly3]

yup, that's correct. I was able to successfully connect using the latest version of DAB at the time.

@jarrydpage @seantleonard - Great find. I got this to work in one of our test environments too.

[JerryNixon]

@jarrydpage was hoping you'd submit a PR. :) No?

[seantleonard]

@jerryNixon this would be a docs update to mention setting User Id=ClientIdOfManagedIdentity; in the connecton string. So far, for MSSQL, no code change needed.

[tdutch1]

Using info provided by @jarrydpage, I have DAB configured in an Azure Container Instance to use a UAMI for Azure SQL. Would be super nice to have this documented. I know it's on the list... :)