Avatar

Mickaël Derriey's blog

© 2024 Mickaël Derriey

Powered by Jekyll and GitHub Pages
Theme based on Hyde by @mdo with modifications by @todthomson

How to connect to Azure SQL with AAD authentication and Azure managed identities

17 Jul 2020

Introduction

We’re trying to improve the security posture of our internal applications.

One aspect of this is how we deal with sensitive information, like database connection strings, API keys, or AAD client secrets. The approach we’re using is to store these in Key Vault instances, which can be accessed by the applications that require them, thanks to Azure managed identities.

In the case of Azure SQL, however, we’re using a slighty different technique, by leveraging Azure Active Directory authentication, and more specifically token-based authentication. Instead of using a connection string that contains a username and a password, we’re using the following strategy:

  1. If not done already, assign a managed identity to the application in Azure;
  2. Grant the necessary permissions to this identity on the target Azure SQL database;
  3. Acquire a token from Azure Active Directory, and use it to establish the connection to the database.

The main benefit comes from the fact that we don’t need to manage and protect the credentials required to connect to the database. We think this is more secure, because the less sensitive information to protect, the less chance of them being accessed by unauthorised parties. After all, isn’t the best password one that doesn’t exist in the first place?

In this post, we’ll talk about how one can connect to Azure SQL using token-based Azure Active Directory authentication, and how to do so using Entity Framework Core.

Connecting to Azure SQL using Azure Active Directory authentication

As mentioned before, this approach doesn’t use the traditional way of having a connection string that contains a username and a password. Instead, the credentials are replaced with an access token, much like you would use when you call an API. Here’s a simple example:

public static async Task Main(string[] args)
{
    var connectionStringBuilder = new SqlConnectionStringBuilder
    {
        DataSource = "tcp:<azure-sql-instance-name>.database.windows.net,1433",
        InitialCatalog = "<azure-sql-database-name>",
        TrustServerCertificate = false,
        Encrypt = true
    };

    await using var sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString)
    {
        AccessToken = await GetAzureSqlAccessToken()
    };

    await sqlConnection.OpenAsync();
    var currentTime = await sqlConnection.ExecuteScalarAsync<DateTime>("SELECT GETDATE()");

    Console.WriteLine($"The time is now {currentTime.ToShortTimeString()}");
}

private static async Task<string> GetAzureSqlAccessToken()
{
    // See https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-sql
    var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net//.default" });
    var tokenRequestResult = await new DefaultAzureCredential().GetTokenAsync(tokenRequestContext);

    return tokenRequestResult.Token;
}

As previously mentioned, the connection string doesn’t contain a username or a password, only the Azure SQL instance and database we want to connect to. The authentication is performed via an access token that we associate with the SQL connection.

Acquiring the token is done with the help of the Azure.Identity NuGet package through the DefaultAzureCredential class. The killer feature of that class is, that it tries to acquire an access token from different sources, including:

For more information, check out the Azure SDK for .NET GitHub repository.

Integrating AAD authentication with Entity Framework Core

Many of our internal applications use Entity Framework Core to access data. One impact is that the example shown above isn’t viable anymore, because EF Core manages the lifetime of SQL connections, meaning it creates and disposes of connections internally. While this is a big advantage, it means we need to find a way to “inject” an access token in the SQL connection before EF Core tries to use it.

The good news is that EF Core 3.0 introduced the concept of interceptors, which had been present in EF 6 for a long time. Interestingly, I could only find a mention of this capability in the release notes of EF Core 3.0, but not in the EF Core docs.

The AddInterceptors method used in the example expects instances of IInterceptor, which is a marker interface, making it hard to discover types that implement it. Using the decompiler of your choice — ILSpy in my case — we can easily find them:

Implementations of the IInterceptor interface

The DbConnectionInterceptor type seems like a fit. Luckily, it exposes a ConnectionOpeningAsync method which sounds just like what we need!

⚠ Update

If you use synchronous methods over your DbContext instance, like ToList(), Count(), or Any(), you need to override the synchronous ConnectionOpening method of the interceptor.

See more details in this new post!

Let’s get to it, shall we?

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseSqlServer(Configuration.GetConnectionString("<connection-string-name>"));
            options.AddInterceptors(new AadAuthenticationDbConnectionInterceptor());
        });
    }
}

public class AadAuthenticationDbConnectionInterceptor : DbConnectionInterceptor
{
    public override async Task<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken)
    {
        var sqlConnection = (SqlConnection)connection;

        //
        // Only try to get a token from AAD if
        //  - We connect to an Azure SQL instance; and
        //  - The connection doesn't specify a username.
        //
        var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
        if (connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && string.IsNullOrEmpty(connectionStringBuilder.UserID))
        {
            sqlConnection.AccessToken = await GetAzureSqlAccessToken(cancellationToken);
        }

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

    private static async Task<string> GetAzureSqlAccessToken(CancellationToken cancellationToken)
    {
        // See https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-sql
        var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net//.default" });
        var tokenRequestResult = await new DefaultAzureCredential().GetTokenAsync(tokenRequestContext, cancellationToken);

        return tokenRequestResult.Token;
    }
}

The configuration of the EF Core DbContext is ordinary, with the exception of the registration of our interceptor.

The interceptor itself is straightforward as well; we can see that the way we acquire a token is similar to the previous example. One interesting aspect is that we try to detect whether we even need to get an access token, based on the SQL Server instance we connect to, and whether the connection string specifies a username.

During local development, there’s a high chance developers will connect to a local SQL database, so we don’t need a token in this case. Imagine also that for some reason, we revert back to using a connection string that contains a username and password; in that case as well, getting a token is not needed.

Going further: resolving interceptors with Dependency Injection

⚠ Update

Good news! There’s a much simpler and terser solution to resolve interceptors from the dependency injection container — please check out this new post.

I strongly recommend that you not use the solution described below, as it involves much more code and hasn’t been fully tested.

Interceptors are a great feature, but at the time of writing, the public API only allows you to add already constructed instances, which can be limiting. What if our interceptor needs to take dependencies on other services?

Registering the interceptors in the application service provider doesn’t work, because EF Core maintains an internal service provider, which is used to resolve interceptors.

I found a way by reverse engineering how EF Core itself is built. However, as you’ll see, the solution is quite involved, and I haven’t fully tested it. As a result, please carefully test it before using this method.

When configuring the DbContext, we can register an extension which has access to the internal service provider; hence, we can use it to register additional services, in this case our interceptor. However, this internal provider doesn’t have as many registered services as a provider used in an ASP.NET Core application. For example, this provider doesn’t have the commonly used ILogger<T> service registered.

Our goal is then to register our interceptor in the internal provider, but somehow have it be resolved from the application provider, so we can take advantage of all the services registered in the latter. To achieve this, we can leverage an extension provided by EF Core, called CoreOptionsExtension, which has a reference to the application service provider — the one we use to register services in the ConfigureServices method of the Startup class of ASP.NET Core applications.

The following implementation is based on the internal CoreOptionsExtension used in EF Core.

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        // 1. Register our interceptor as "itself"
        services.AddScoped<AadAuthenticationDbConnectionInterceptor>();

        // 2. Add our extension to EF Core
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseSqlServer(Configuration.GetConnectionString("<connection-string-name"));
            ((IDbContextOptionsBuilderInfrastructure)options).AddOrUpdateExtension(new AppOptionsExtension());
        });
    }
}

public class AppOptionsExtension : IDbContextOptionsExtension
{
    private DbContextOptionsExtensionInfo _info;

    public DbContextOptionsExtensionInfo Info => _info ??= new ExtensionInfo(this);

    public void ApplyServices(IServiceCollection services)
    {
        // 3. Get application service provider from CoreOptionsExtension, and
        // resolve interceptor registered in step 1, so we can register it in the
        // internal service provider as IInterceptor
        services.AddScoped<IInterceptor>(provider =>
        {
            var applicationServiceProvider = provider
                .GetRequiredService<IDbContextOptions>()
                .FindExtension<CoreOptionsExtension>()
                .ApplicationServiceProvider;

            return applicationServiceProvider.GetRequiredService<AadAuthenticationDbConnectionInterceptor>();
        });
    }

    public void Validate(IDbContextOptions options)
    {
    }

    private class ExtensionInfo : DbContextOptionsExtensionInfo
    {
        public ExtensionInfo(IDbContextOptionsExtension extension) : base(extension)
        {
        }

        public override bool IsDatabaseProvider => false;
        public override string LogFragment => null;
        public override long GetServiceProviderHashCode() => 0L;
        public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
        {
        }
    }
}

public class AadAuthenticationDbConnectionInterceptor : DbConnectionInterceptor
{
    private readonly ILogger _logger;

    // In this case we inject an instance of ILogger<T>, but you could inject any service
    // that is registered in your application provider
    public AadAuthenticationDbConnectionInterceptor(ILogger<AadAuthenticationDbConnectionInterceptor> logger)
    {
        _logger = logger;
    }

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

        //
        // Only try to get a token from AAD if
        //  - We connect to an Azure SQL instance; and
        //  - The connection doesn't specify a username.
        //
        var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
        if (connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && string.IsNullOrEmpty(connectionStringBuilder.UserID))
        {
            try
            {
                sqlConnection.AccessToken = await GetAzureSqlAccessToken(cancellationToken);
                _logger.LogInformation("Successfully acquired a token to connect to Azure SQL");
            }
            catch (Exception e)
            {
                _logger.LogError(e, "Unable to acquire a token to connect to Azure SQL");
            }
        }
        else
        {
            _logger.LogInformation("No need to get a token");
        }

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

    private static async Task<string> GetAzureSqlAccessToken(CancellationToken cancellationToken)
    {
        if (RandomNumberGenerator.GetInt32(10) >= 5)
        {
            throw new Exception("Faking an exception while tying to get a token");
        }

        // See https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-sql
        var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net//.default" });
        var tokenRequestResult = await new DefaultAzureCredential().GetTokenAsync(tokenRequestContext, cancellationToken);

        return tokenRequestResult.Token;
    }
}

Conclusion

In this post, we covered how we can use Azure Active Directory authentication to connect to Azure SQL, focusing on the token-based aspect of it, since we’re trying to reduce the amount of sensitive information an application needs to deal with.

We also went over a nice way to integrate AAD authentication with Entity Framework Core, by leveraging interceptors. The first benefit of using this approach is that we let EF Core manage SQL connections internally. The second advantage of using interceptors is that they are asynchronous, which allows us not to have to resort to block on asynchronous operations.

Finally, we investigated how we can inject services in our interceptors. The solution we explored involves quite a bit of ceremony, which makes it pretty heavy. I opened an issue on the EF Core repository, we’ll see if the team finds a way to make this more friendly. Please let me know on Twitter if you know of an easier way to achieve this.

I hope you liked this post!

Related Posts