Managed Identity via connection string not working

[richardoliverpearce]

Describe the bug

Hi, I'm attempting use the new 2.1 release to provide Managed Identity connection string support to no success:

• It works fine when using the method of creating an AccessToken using Microsoft.Identity.
• The Managed Identity is System Assigned.
• I've tried using
  • Authentication=Active Directory Managed Identity.
  • Authentication=Active Directory MSI.

Thanks

Exception message:
One or more errors occurred. (One or more errors occurred. (Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"78dc831f-e5fd-4aa6-990d-7c798128be81"}))

Stack trace:
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()--- End of stack trace from previous location where exception was thrown

Trace Logging:
2020-11-23T12:09:07.069 [Information] <prov.DbConnectionHelper.ConnectionString_Set|API> 4, 'Server=myinstance.database.windows.net;Database=mydatabase;Authentication=Active Directory Managed Identity;'
2020-11-23T12:09:07.072 [Information] <sc.TdsParserStateObjectFactory.CreateTdsParserStateObject|INFO> AppContext switch 'Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows' not enabled, native networking implementation will be used.
2020-11-23T12:09:07.073 [Information] <sc.TdsParser.Connect|SEC> Active Directory Managed Identity authentication
2020-11-23T12:09:07.077 [Information] <sc.TdsParser.Connect|SEC> Sending prelogin handshake
2020-11-23T12:09:07.077 [Information] <sc.TdsParser.SendPreLoginHandshake|INFO> ClientConnectionID 953610ca-f186-4b64-8bff-9f108703c5e1, ActivityID dcdd22a8-4c4b-42e9-bbf2-77c55bc5e643:1
2020-11-23T12:09:07.077 [Information] <sc.TdsParser.Connect|SEC> Consuming prelogin handshake
2020-11-23T12:09:07.088 [Information] <sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
2020-11-23T12:09:07.088 [Information] <sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
2020-11-23T12:09:07.090 [Information] <sc.TdsParser.TryRun|SEC> Received login acknowledgement token
2020-11-23T12:09:07.090 [Information] <sc.SqlInternalConnectionTds.LoginNoFailover> Routed to myinstance.database.windows.net
2020-11-23T12:09:07.091 [Information] <sc.TdsParserStateObjectFactory.CreateTdsParserStateObject|INFO> AppContext switch 'Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows' not enabled, native networking implementation will be used.
2020-11-23T12:09:07.091 [Information] <sc.TdsParser.Connect|SEC> Active Directory Managed Identity authentication
2020-11-23T12:09:07.094 [Information] <sc.TdsParser.Connect|SEC> Sending prelogin handshake
2020-11-23T12:09:07.094 [Information] <sc.TdsParser.SendPreLoginHandshake|INFO> ClientConnectionID d504231e-8a7f-41bb-80be-5e4120b9dd01, ActivityID ff485a55-dafe-4901-b1fe-1e8879b18ff2:2
2020-11-23T12:09:07.095 [Information] <sc.TdsParser.Connect|SEC> Consuming prelogin handshake
2020-11-23T12:09:07.099 [Information] <sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
2020-11-23T12:09:07.099 [Information] <sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
2020-11-23T12:09:07.102 [Information] <sc.TdsParser.TryProcessFedAuthInfo> Processed FEDAUTHINFO token stream: STSURL: https://login.windows.net/C670B60D-C4C7-4DEC-8C1F-AC0DA3E72BEE, SPN: https://database.windows.net/
2020-11-23T12:09:07.103 [Information] <sc.SqlInternalConnectionTds.OnFedAuthInfo> 9, Generating federated authentication token
2020-11-23T12:09:07.103 [Information] AcquireTokenAsync | This environment is identified as an Azure App Service environment. Proceeding to acquire access token from Endpoint URL: http://127.0.0.1:41544/MSI/token/
2020-11-23T12:09:07.103 [Information] AcquireTokenAsync | Identity Object id received and will be used for acquiring access token
2020-11-23T12:09:07.107 [Information] AcquireTokenAsync | Request to acquire access token failed with status code BadRequest
2020-11-23T12:09:07.107 [Information] AcquireTokenAsync | Error occurred while acquiring access token: Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"05e0bec7-08d4-499c-80b4-6a899cc38d26"}
2020-11-23T12:09:07.107 [Information] <sc.SqlError.SqlError|ERR> infoNumber=0, errorState=0, errorClass=20, errorMessage='Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"05e0bec7-08d4-499c-80b4-6a899cc38d26"}', procedure='', lineNumber=0
2020-11-23T12:09:07.107 [Information] AcquireTokenAsync | Error occurred while acquiring access token: Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"05e0bec7-08d4-499c-80b4-6a899cc38d26"}
2020-11-23T12:09:07.108 [Information] <sc.SqlInternalConnectionTds.LoginFailure|RES|CPOOL> 9
2020-11-23T12:09:07.109 [Information] <sc.SqlConnection.OpenAsyncRetry|Info> 4

To reproduce

Works as expected when attaching debugger to Azure Functions instance:

var credential = new DefaultAzureCredential();
var token = await credential.GetTokenAsync(new TokenRequestContext(new[] {"https://database.windows.net/.default"}), cancellationToken);

await using var connection = new SqlConnection("Server=myinstance.database.windows.net;Database=mydatabase;");
{
    connection.AccessToken = token.Token;
    await connection.OpenAsync(cancellationToken);
}

Fails when attaching to debugger Azure Functions instance:

await using var connection = new SqlConnection("Server=myinstance.database.windows.net;Database=mydatabase;Authentication=Active Directory Managed Identity;");
{
    await connection.OpenAsync(cancellationToken);
}

Expected behavior

Be able to call OpenAsync() without throwing an exception.

Further technical details

Microsoft.Data.SqlClient version: 2.1.0
.NET target: Core 3.1
SQL Server version: Azure SQL Database
Operating system: Azure Function App Runtime ~3 (tried both Windows and Linux plans)

[cheenamalhotra]

Hi @richardoliverpearce

Could your also try with ManagedIdentityCredential and let us know:

var credential = new ManagedIdentityCredential();
var token = await credential.GetTokenAsync(new TokenRequestContext(new[] {"https://database.windows.net/.default"}), cancellationToken);

await using var connection = new SqlConnection("Server=myinstance.database.windows.net;Database=mydatabase;");
{
    connection.AccessToken = token.Token;
    await connection.OpenAsync(cancellationToken);
}

As this is what closely aligns with "Active Directory Managed Identity' or "MSI" authentication mode.

[richardoliverpearce]

Hi @cheenamalhotra, the code snippet with ManagedIdentityCredential works fine too.

Just to make sure I have no other dependencies conflicting, I have also tried a with new VS functions project, which is also returning the same error.

One or more errors occurred. (One or more errors occurred. (Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"aa506604-e29f-4356-a6e9-6105aa47b04b"}))

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

namespace FunctionApp1
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            try
            {
                await using var connection =
                    new SqlConnection(
                        "Server=myinstance.database.windows.net;Database=mydatabase;Authentication=Active Directory Managed Identity");
                {
                    await connection.OpenAsync();
                }
            }
            catch (Exception e)
            {

            }

            return new OkObjectResult(null);
        }
    }
}

Thanks,

Richard

[cheenamalhotra]

Firstly, thanks for capturing traces! That seems to have captured the problem here.
I do see you do not pass any "User Id" in your connection string, but this in traces is unexpected:

2020-11-23T12:09:07.103 [Information] AcquireTokenAsync | Identity Object id received and will be used for acquiring access token

You'd get this when "User Id" is not null, and in this it seems to be blank ""?

SqlClient/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/AzureManagedIdentityAuthenticationProvider.cs

Lines 91 to 96 in 0d4c9bb

	// If user assigned managed identity is specified, include object ID parameter in request
	if (parameters.UserId != default)
	{
	objectIdParameter = $"&object_id={Uri.EscapeDataString(parameters.UserId)}";
	SqlClientEventSource.Log.TryTraceEvent("AcquireTokenAsync | Identity Object id received and will be used for acquiring access token {0}", parameters.UserId);
	}

You could test this by passing Object Id of your Identity in "User Id" connection property, please use that as a workaround if that should work, while we investigate why it's going as blank there and handle it appropriately.

[richardoliverpearce]

Hi @cheenamalhotra, ok brilliant news. Unfortunately I believe I've tried that also (still system assigned) - spent hours trying combinations :) Not to worry, for now, I'll stick with the AccessToken property method.

Thanks,

Richard

[andygjp]

Hi,

I'm using System Assigned identity and I'm experiencing the same issue. I tried setting "User Id" equal to the Object Id and it still failed. In the end I wrapped the ActiveDirectoryManagedIdentity and ActiveDirectoryMSI authentication providers with my own that sets userId to null:

public class ManagedIdentityProvider : SqlAuthenticationProvider
{
    private class NullUserParameters : SqlAuthenticationParameters
    {
        public NullUserParameters(SqlAuthenticationMethod authenticationMethod, string serverName,
            string databaseName, string resource, string authority, Guid connectionId)
            : base(authenticationMethod, serverName, databaseName, resource, authority, userId: null,
                password: null, connectionId)
        {
        }
    }

    private readonly SqlAuthenticationProvider provider;

    public ManagedIdentityProvider(SqlAuthenticationProvider provider) =>
        this.provider = provider;

    public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
    {
        if (string.IsNullOrWhiteSpace(parameters.UserId))
        {
            return await provider.AcquireTokenAsync(new NullUserParameters(
                parameters.AuthenticationMethod, parameters.ServerName, parameters.DatabaseName,
                parameters.Resource, parameters.Authority, parameters.ConnectionId));
        }

        try
        {
            return await provider.AcquireTokenAsync(parameters);
        }
        catch (Exception ex)
        {
            throw new Exception($"Auth failed for user '{parameters.UserId}'.", ex);
        }
    }

    public override bool IsSupported(SqlAuthenticationMethod authenticationMethod) =>
        provider.IsSupported(authenticationMethod);
}

Hope that helps.

Cheers,
Andy

[Nisden]

Having the same issue on Azure App Service Containers running .NET 5 however I made a workaround inspired by andygjp.

SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryManagedIdentity, new WorkAroundSqlAuthenticationProvider());

...

public class WorkAroundSqlAuthenticationProvider : SqlAuthenticationProvider
{
        private static readonly AzureServiceTokenProvider tokenProvider = new AzureServiceTokenProvider();

        public async override Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
        {
            var token = await tokenProvider.GetAuthenticationResultAsync(parameters.Resource);

            return new SqlAuthenticationToken(token.AccessToken, token.ExpiresOn);
        }

        public override bool IsSupported(SqlAuthenticationMethod authenticationMethod)
        {
            return authenticationMethod == SqlAuthenticationMethod.ActiveDirectoryManagedIdentity;
        }
}

[karinazhou]

@Nisden Thank you for providing the workaround. This issue will be in the version 2.1.1.

[cheenamalhotra]

Issue is fixed with v2.1.1