Azure SQL Authentication Active Directory Default Slow

Question

We are transitioning from using usernames and passwords in Visual Studio to "Authentication=Active Directory Default" in our Azure SQL connection strings for EF Core. We are experiencing startup times taking 3 times as long since this change as it appears the token acquisition is really delaying things, possibly down to attempting to authenticate different ways that are unavailable.

There appears to be ways to add settings to environmental variables to disable mechanisms other than Visual Studio as the method but I haven't gotten them to improve things.

Is anyone aware of a fix for this issue?

https://github.com/Azure/azure-sdk-for-net/blob/e9cbf9b31df595b2b34268fa91e14be9d57bdfd3/sdk/identity/Azure.Identity/src/Credentials/DefaultAzureCredentialOptions.cs#L213

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

https://github.com/dotnet/SqlClient/issues/1473

UPDATE

In case it's of any use to anyone, I started to build out an interceptor. I haven't fully implemented this yet but in case it's helpful I'll leave it here.

You can register this as like this: https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors#registering-interceptors

public class AadAuthenticationInterceptor : DbConnectionInterceptor
    {
        public override InterceptionResult ConnectionOpening(
            DbConnection connection,
            ConnectionEventData eventData,
            InterceptionResult result)
            => throw new InvalidOperationException("Open connections asynchronously when using AAD authentication.");

        public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
            DbConnection connection,
            ConnectionEventData eventData,
            InterceptionResult result,
            CancellationToken cancellationToken = default)
        {
            var sqlConnection = (SqlConnection)connection;

            var token = await GetAzureTokenCache();
            sqlConnection.AccessToken = token;
            return result;
        }


        public static async Task<string> GetAzureTokenCache()
        {
            AzureTokenItem tkn = new AzureTokenItem();
            
            var AppDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\YourFolder\\";
            if (File.Exists(AppDataFolder + "AzureTokenCache.txt"))
            {
                var tokenText = File.ReadAllText(AppDataFolder + "AzureTokenCache.txt");
                var tmpTkn = JsonSerializer.Deserialize<AzureTokenItem>(tokenText);
                if (tmpTkn.ExpiresOn > DateTime.Now)
                {
                    return tmpTkn.Token;
                }
            }

            var tokenCredential = new DefaultAzureCredential(new DefaultAzureCredentialOptions
            {
                // Disable the token credential that we don't use
                ExcludeEnvironmentCredential = true,
                ExcludeInteractiveBrowserCredential = true,
                ExcludeAzurePowerShellCredential = true,
                ExcludeSharedTokenCacheCredential = true,
                ExcludeVisualStudioCodeCredential = true,
                ExcludeManagedIdentityCredential = true,
                ExcludeAzureCliCredential = true,
            });

            
            var azureTkn = await tokenCredential.GetTokenAsync(new TokenRequestContext(new[] { "https://database.windows.net/" }));

            tkn = new AzureTokenItem
            {
                ExpiresOn = azureTkn.ExpiresOn.LocalDateTime,
                Token = azureTkn.Token
            };

            Directory.CreateDirectory(AppDataFolder);
            File.WriteAllText(AppDataFolder + "AzureTokenCache.txt", JsonSerializer.Serialize(tkn));
            
            return tkn.Token;
        }
    }
    public class AzureTokenItem
    {
        public DateTime ExpiresOn { get; set; }
        public string Token { get; set; }
    }

Answer 1

I feel you pain. I got it ok-ish by implementing a custom authentication provider and explicitly configuring the auth sources to use:

public class MySqlAuthenticationProvider : SqlAuthenticationProvider
{
    public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
    {
        var context = new TokenRequestContext(new[] { "https://database.windows.net/" });
        var options = new DefaultAzureCredentialOptions() {
            ExcludeManagedIdentityCredential = true
            // Exclude more.. 
        };
        var token = await new DefaultAzureCredential(options)
            .GetTokenAsync(context, default);
        return new SqlAuthenticationToken(token.Token, token.ExpiresOn);
    }

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

You wire this up in program start-up by:

SqlAuthenticationProvider.SetProvider(
    SqlAuthenticationMethod.ActiveDirectoryDefault,
    new MySqlAuthenticationProvider()
);

This way I got initial connection speed on VS auth from ~15+ sec to ~6 sec.

This is still slow, but bearable, especially if warmed up asynchronously while other parts of app start up.