Efcore: Setting AAD AccessToken blocks the thread

[galvesribeiro]

Hello folks!

I need to use EF Core with Azure SQL databases that are protected using Azure AD principals. For example, Azure Managed Identities from Azure App Services and Azure Functions.

The only way I've found over the docs is to at the ctor do something like this:

public SampleContext(DbContextOptions<SampleContext> options, IHostingEnvironment host)
        : base(options)
    {
        if (host.IsDevelopment()) return;
        var conn = (SqlConnection)Database.GetDbConnection();
        conn.AccessToken = (new AzureServiceTokenProvider())
            .GetAccessTokenAsync("https://database.windows.net/").Result;
    }

You can do something similar at OnConfiguring as long as you set the AccessToken property. For reference, this SO question is the most elaborated where someone that appears to be from MSFT suggest that it is the correct approach.

It works. But the problem is that it is making a blocking call on that .Result. Even if you use other methods to acquire the token, they still end up firing a networking call and will block there. If you have DbContexts scoped, it is a pretty bad performance hit.

So, to avoid that blocking call to .Result, can someone tell me if there is a way to set the AccessToken somehow differently?

Thanks!

[galvesribeiro]

Ok, I've found a way to make it work without blocking. Create a DbConnectionInterceptor and override ConnectionOpeningAsync() to get the connection and set the SqlConnection.AccessToken asynchronously.

A naive implementation would be something like this:

public class AADTokenInjectorDbInterceptor : DbConnectionInterceptor
    {
        private static readonly string[] _scopes = new[] { "https://database.windows.net/.default" };
        private readonly TokenCredential _credentials;
        private AccessToken _accessToken;

        public AADTokenInjectorDbInterceptor(IOptions<AADIdentityOptions> options)
        {
            this._credentials = options.Value.Credentials;
        }

        public override async Task<InterceptionResult> ConnectionOpeningAsync(
            DbConnection connection,
            ConnectionEventData eventData,
            InterceptionResult result,
            CancellationToken cancellationToken = default)
        {
            await this.RefreshToken();
            var sqlConnection = (SqlConnection)connection;
            sqlConnection.AccessToken = this._accessToken.Token;
            return result;
        }

        private async ValueTask RefreshToken()
        {
            if (this._accessToken.ExpiresOn != default &&
                DateTimeOffset.UtcNow < this._accessToken.ExpiresOn)
            {
                return;
            }

            this._accessToken = await this._credentials.GetTokenAsync(
                new TokenRequestContext(_scopes),
                CancellationToken.None
            );
        }
    }

...

services.AddDbContext<MyDbContext>((sp, options) =>
                options.UseSqlServer(connectionStringWithoutUserAndPassword, sql => sql.MigrationsAssembly(migrationsAssembly))
                    .AddInterceptors(sp.GetRequiredService<AADTokenInjectorDbInterceptor>()));

Need just make it thread-safe to cover the singleton cases. This is using the Azure.Identity.TokenCredential which can be a ClientSecretCredential or a ManagedIdentityCredential in this sample, so you are able to run locally or in an Azure service using Managed Identities.

The ideal world, would be that either Entity Framework or the (new) Microsoft.Data.SqlClient would support set the TokenCredential and manage token acquirer / refresh, but at least what I'm doing works.

I hope it help someone that fall in the same problem.