Error authenticating to Azure SQL with second User-Assigned Managed Identity

[David-Engel]

Filing this issue here for @squarerigger so that it doesn't get lost. Originally filed at https://github.com/MicrosoftDocs/sql-docs/issues/9303.

Describe the bug

Starting from the code sample at Connect to Azure SQL with Azure AD authentication and SqlClient - ADO.NET Provider for SQL Server:

The article is almost right (Using Active Directory Managed Identity authentication). However, using the recommended code 'as is' in an environment slightly different than the author's and using two AD entities instead of one in an Azure function app (one for the first step involving a Service Bus, another for more restrictive Azure SQL database access) leads to an inner error and task cancellation when trying to perform the second step (Azure SQL authentication). I have tried six close alternatives using Microsoft docs, with the same inner error exception.

The recommended code fails (each a small variation) conn = new SqlConnection("Server=.database.windows.net; Database=; Authentication=Active Directory Default; Encrypt=True; User Id="); conn = new SqlConnection("Server=.database.windows.net,1433; Database=; Authentication=Active Directory Default; Encrypt=True; User Id="); conn = new SqlConnection("Server=tcp:.database.windows.net; Database=; Authentication=Active Directory Default; Encrypt=True; User Id="); conn = new SqlConnection("Server=tcp:.database.windows.net,1433; Database=; Authentication=Active Directory Default; Encrypt=True; User Id="); conn = new SqlConnection("Server=tcp:.database.windows.net,1433; Database=; Authentication=Active Directory Default; Encrypt=True; User Id=; TrustServerCertificate=True");

Bottom line --- the Azure Default credential is failing to switch over to the new Client ID that is provided in the connection string. Perhaps the ID of the first AD user has been cached and is being used instead of the newly provided id.

Expected behavior

No error. Successful connection.

Further technical details

My environment: a non-domain-connected network (a standalone workstation) using Visual Studio with one Active Directory account logged in, using an Azure function app, succeeding in accessing the Service Bus with the initial AD account, then trying to open Azure SQL database using a different, User-Assigned managed identity. (Both AD accounts have the required setup in Azure - as evidenced by success when I forced the AD ClientID to be used in the code that follows.) Azure.Identity 1.9.0 Microsoft.Data.SqlClient 5.1.1 Azure.Core 1.32.0 Microsoft.Identity.Client 4.49.1 Microsoft.Identity.Client.Extensions.Msai 2.25.3 Microsoft.IdentityModel.Abstractions 6.24.0 Microsoft.IdentityModel.Protocols 6.24.0 Microsoft.IdentityModel.Protocols.Openidconnect 6.24.0 Microsoft.IdentityModel.Tokens 6.24.0 System.IdentityModel.Tokens.Jwt 6.24.0

This article provided code that works: https://techcommunity.microsoft.com/t5/apps-on-azure-blog/connect-app-service-with-azure-sql-database-with-managed/ba-p/3288300

string userAssignedClientId = ""; //Give Client ID of User Managed Identity
var conn = new SqlConnection(connectionString);
var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = userAssignedClientId });
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default"}));
conn.AccessToken = token.Token;

[David-Engel]

@squarerigger - If you want to add any additional info to your scenario, please add it here. If you have a complete example repro, that is most likely to get quicker results.

[ventii]

I may have encountered the same unexpected behaviour:

Setup

I have a .net kubernetes deployment which runs with the following environment variables to enable Managed Identity Authentication

This allows me to create sql connections using active directory default, as I know that Microsoft.Data.SqlClient will use DefaultAzureCredentials to authenticate using the clientId specified as environment variable:

If I want to authenticate using a second user-assigned managed identity, we expected that we can achieve that by specifying the clientId as UserId in the connection builder as follows:

However, the client id specified in the connection builder is ignored and the sql connections are created using the clientId set as environment variable.

Is it perhaps not actually possible to specify a clientId SqlConnectionStringBuilder when using SqlAuthenticationMethod.ActiveDirectoryDefault authentication?

[ErikEJ]

@ventii It sounds like you are looking for this: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-managed-identity-authentication

[squarerigger]

This looks like the standard documentation that fails to address the task of using one kind of authentication (Visual Studio - based using a regular user id to access a queue, for instance) then changing the client id pointing to a user managed identity to access SQL Server as we discussed in our defect report.

---

From: Erik Ejlskov Jensen @.> Sent: Monday, February 12, 2024 6:20:23 AM To: dotnet/SqlClient @.> Cc: Richard Koontz @.>; Mention @.> Subject: Re: [dotnet/SqlClient] Error authenticating to Azure SQL with second User-Assigned Managed Identity (Issue #2155)

@ventiihttps://github.com/ventii It sounds like you are looking for this: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-managed-identity-authentication

— Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/2155#issuecomment-1938768579, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AM7DTZTARFWY22UOHC5DXNLYTIQKPAVCNFSM6AAAAAA4Y6CVDGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZYG43DQNJXHE. You are receiving this because you were mentioned.Message ID: @.***>

[ventii]

@ErikEJ yes, we do manage to set the clientId explicitly when using Managed Identity Authentication, It does not work however when keeping Active Directory Default.

It might be my mistake and misinterpretation of the documentation to assume that it would work perhaps