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 slightly 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:
- If not done already, assign a managed identity to the application in Azure;
- Grant the necessary permissions to this identity on the target Azure SQL database;
- 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:
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:
- Using credentials exposed through environment variables;
- Using credentials of an Azure managed identity;
- Using the account that is logged in to Visual Studio;
- Using the account that is logged in to the Visual Studio Code Azure Account extension.
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:
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?
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 is 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.
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 blocking 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!