6

There seem to be three main ways of connecting to the database with EF Core and Azure AD integrated authentication.

Method 1: Classic approach

In the DbContext, put this:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     var conn = new SqlConnection(ConnString);
     conn.AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;
     optionsBuilder.UseSqlServer(conn);
 }

This works both locally on the developer's machine and also in Azure, but has this dodgy async-over-sync use of .Result as there is no async version of OnConfiguring. Using .Result to call async-over-sync is not recommended.

Method 2: Use a connection interceptor

public class AadAuthenticationDbConnectionInterceptor : DbConnectionInterceptor
{
    public override async Task<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken)
    {
        var sqlConnection = (SqlConnection)connection;
        sqlConnection.AccessToken = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/");

        return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
    }

    public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
    {
        var sqlConnection = (SqlConnection)connection;
        sqlConnection.AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").GetAwaiter().GetResult();

        return base.ConnectionOpening(connection, eventData, result);
    }

 }

This also works both locally and in Azure, but there is still the dodgy async-over-sync use of .Result as EF calls the non-async ConnectionOpening method if you perform any non-async EF operations.

Method 3: Specify Authentication type in connection string

If you are using Microsoft.Data.SqlClient v2.1.0 or newer, you can use two new authentication types: Active Directory Managed Identity and Active Directory Interactive.

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     var connString = "Server=tcp:xxxxxx.database.windows.net,1433;Database=yyyyy;Authentication=Active Directory Managed Identity";
     optionsBuilder.UseSqlServer(conn);
 }

This takes care of everything for you and doesn't have the async-sync issues. However, it requires you to know whether the code is running in Azure or not to select the authentication type; and for local development the Active Directory Interactive mode is very annoying as it pops up a login prompt every time you start the application.

And the question is...

Is there a way to get this working that doesn't have the problem of sync-async and also doesn't have the problem of a login popup every time you run the app?

gallivantor
  • 1,091
  • 2
  • 14
  • 21
  • 1
    It will be possible very soon with SqlClient 3.0 preview 3 https://github.com/dotnet/SqlClient/pull/1079/files#diff-19c599846b612a9b9563ca53899864740d92cf153e5ad325f9550d0cc6ccab00R23 – ErikEJ May 20 '21 at 12:40
  • Having a synchronous network call in an async method is much worse than having calling `.Result()` to synchronously wait for the result of a Task in a synchronous method. So I don't think the connection interceptor has any meaningful problems. – David Browne - Microsoft May 20 '21 at 14:55
  • @DavidBrowne-Microsoft is there no risk in this situation of deadlocks etc due to the thread that resumes from the async call getting blocked by the call to Result? – gallivantor May 20 '21 at 23:28

1 Answers1

1

@ErikEJ answered this in the comments.

The solution is to wait for v3.0 of the Microsoft.Data.SqlClient which introduces Active Directory Default authentication type. With that, we'll be able to do:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     var connString = "Server=tcp:xxxxxx.database.windows.net,1433;Database=yyyyy;Authentication=Active Directory Default";
     optionsBuilder.UseSqlServer(conn);
 }

and it sounds like that will provide a neat solution to this.

gallivantor
  • 1,091
  • 2
  • 14
  • 21