Azure SQL: Dependency Injection with EFCore 3.x for AAD Authentication

While we're trying to connect our client application (console, windows, or web application) using Azure SQL database its preferred to use access token-based AAD authentication. 

Per preferred practice to avoid NEW keyword to create objects we generally use dependency injection to create objects. 

To inject the DbContext using Entity Framework Core (3.x) we have an overridden method OnConfigure() which can be used to set up the UserSQLServer() configuration to create the SQL connection. But to use UseSqlServer extension of DbContextOptionBuilder we've to create the DbConnection (or SQLConnection) after opening the SQL-connection (conn.Open()). 

The problem with this approach is there is no option to dispose or close the SQL connection which we opened while creating the DbContext through DI. The below code demonstrates the general approach to achieve this.

 
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	// if it's AAD authentication, then get the SQLConnection and set AccessToken every time
	//  in all other cases (WindowsAuth, other SQL auth...), we will go with regular EFCore userSqlServer configuration
	if (_sqlConfigValues.IsAADAuthEnabled)
	{
		var sqlConnection = _sqlConnectionHelper.GetSqlConnection(_sqlConfigValues);
		optionsBuilder.UseSqlServer(sqlConnection, option =>
		{
			option.CommandTimeout(_sqlConfigValues.CommandTimeoutInSeconds);
		}
		);
	}
	else
	{
		if (!optionsBuilder.IsConfigured)
		{
			var connectionString = _sqlConnectionHelper.GetSqlConnectionString(_sqlConfigValues);
			optionsBuilder.UseSqlServer(connectionString, option =>
			{
				option.CommandTimeout(_sqlConfigValues.CommandTimeoutInSeconds);
			}
		);
		}
	}
}
    

Your GetSqlConnection method will be something like this

  public SqlConnection GetSqlConnection(SqlConfigValues sqlConfigValues)
{
	var sqlConnectionString = GetSqlConnectionString(sqlConfigValues);	
	var sqlConnection = new SqlConnection(sqlConnectionString);

	// AAD auth then set the token
	if (sqlConfigValues.IsAADAuthEnabled)
	{
		var accessToken =_tokenFactory.GetDBAccessToken();
		sqlConnection.AccessToken = accessToken;
	}

	// let's open the connection
	sqlConnection.Open();

	return sqlConnection;
}
  

Your SQL ConfigValues class definition is something like this

  namespace YourNamespace.ConfigValues
{
    public class SqlConfigValues
    {
        public string DatabaseServer { get; private set; }

        public string DatabaseName { get; private set; }
        public int ConnectionRetryCount { get; private set; }
        public int CommandTimeoutInSeconds { get; private set; }
        public int ConnectionTimeoutInSeconds { get; private set; }

        public bool IsAADAuthEnabled { get; private set; }
        public bool IsWindowsAuthEnabled { get; private set; }

        //
        public string SqlUserName { get; private set; }
        public string SqlPassword { get; private set; }
    }
}
    

To resolve this issue, Opening the SQL-connection every time while creating the DB context object through DI, we can take the help of DbConnectionINterceptor like this. 

   public class AzureAuthInterceptor : DbConnectionInterceptor
    {
        private readonly ITokenFactory _tokenFactory;
        public AzureAuthInterceptor(ITokenFactory tokenFactory)
        {
            _tokenFactory = tokenFactory;
        }

        public override async Task ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            if (!(connection is SqlConnection conn))
            {
                return result;
            }

            conn.AccessToken =  await _tokenFactory.GetDBAccessTokenAsync();
            
            return result;
        }
    }
    

And in your TokenFactory class and respective ITokenFactory

 

 public async Task GetDBAccessTokenAsync()
        {
            // it will use MSI 
            AzureServiceTokenProvider provider = new AzureServiceTokenProvider();
            var accessToken = await provider.GetAccessTokenAsync("https://database.windows.net/")
                .ConfigureAwait(false);

            return accessToken;
        }
  

Now in your Startup.cs, you can use something like this  

  services.AddDbContext((serviceProvider, options) =>
            {
                var sqlConnHelper = serviceProvider.GetService();
                var tokenFactory = serviceProvider.GetService();
                options.UseSqlServer(sqlConnHelper.GetSqlConnectionString(sqlConfigValues))   // here we're just passing the connection string not the SqlConnection object
                    .AddInterceptors(new AzureAuthInterceptor(tokenFactory));  // this code will take care of getting the accessToken when required.
            });
    

This code is tested with EFCore 3.1.8/7/6 versions.