EF Core Connection to Azure SQL with Managed Identity

Question

I am using EF Core to connect to a Azure SQL Database deployed to Azure App Services. I am using an access token (obtained via the Managed Identities) to connect to Azure SQL database.

Here is how I am doing that:

Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    //code ignored for simplicity
    services.AddDbContext<MyCustomDBContext>();

    services.AddTransient<IDBAuthTokenService, AzureSqlAuthTokenService>();
}

MyCustomDBContext.cs

public partial class MyCustomDBContext : DbContext
{
    public IConfiguration Configuration { get; }
    public IDBAuthTokenService authTokenService { get; set; }

    public CortexContext(IConfiguration configuration, IDBAuthTokenService tokenService, DbContextOptions<MyCustomDBContext> options)
        : base(options)
    {
        Configuration = configuration;
        authTokenService = tokenService;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = Configuration.GetConnectionString("defaultConnection");
        connection.AccessToken = authTokenService.GetToken().Result;

        optionsBuilder.UseSqlServer(connection);
    }
}

AzureSqlAuthTokenService.cs

public class AzureSqlAuthTokenService : IDBAuthTokenService
{
    public async Task<string> GetToken()
    {
        AzureServiceTokenProvider provider = new AzureServiceTokenProvider();
        var token = await provider.GetAccessTokenAsync("https://database.windows.net/");

        return token;
    }
}

This works fine and I can get data from the database. But I am not sure if this is the right way to do it.

My questions:

1. Is this a right way to do it or will it have issues with performance?
2. Do I need to worry about token expiration? I am not caching the token as of now.
3. Does EF Core has any better way to handle this?

Answer 1

Is this a right way to do it or will it have issues with performance?

That is the right way. OnConfiguring is called for each new DbContext, so assuming you don't have any long-lived DbContext instances, this is the right pattern.

Do I need to worry about token expiration? I am not caching the token as of now.

AzureServiceTokenProvider takes care of caching.

Does EF Core has any better way to handle this?

The AAD Auth methods for SqlClient in .NET Core are documented here.

Answer 2

While the approach is generally correct in the sense that there is no other way than having to write custom code that sets the AccessToken of the connection, there is a couple of issues in your implementation that could be avoided by using a DbConnectionInterceptor as I will describe below. Those two issues are:

1. You took the responsibility of creating the connection object yourself. But you don't dispose it. Disposal will be tricky in your implementation, and that's why you might have skipped it.
2. Your code is blocking, as you use .Result to block while waiting for the access token.

A better alternative is to use interceptors, which EF Core supports. You will start with a DbContext like this:

public class MyCustomDbContextFactory : IMyCustomDbContextFactory
{
    private readonly string _connectionString;
    private readonly AzureAuthenticationInterceptor _azureAuthenticationInterceptor;
    public MyCustomDbContextFactory(DbContextFactoryOptions options, AzureAuthenticationInterceptor azureAuthenticationInterceptor)
    {
        _connectionString = options.ConnectionString;
        _azureAuthenticationInterceptor = azureAuthenticationInterceptor;
    }
    public MyCustomDbContext Create()
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyCustomDbContext>();
        optionsBuilder
            .UseSqlServer(_connectionString)
            .AddInterceptors(_azureAuthenticationInterceptor);
        return new MyCustomDbContext(optionsBuilder.Options);
    }
}

And this is the interceptor implementation:

public class AzureAuthenticationInterceptor : DbConnectionInterceptor
{
    private const string AzureDatabaseResourceIdentifier = "https://database.windows.net";
    private readonly AzureServiceTokenProvider _azureServiceTokenProvider;
    public AzureAuthenticationInterceptor(AzureServiceTokenProvider azureServiceTokenProvider) : base()
    {
        _azureServiceTokenProvider = azureServiceTokenProvider;
    }
    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
    {
        if (connection is SqlConnection sqlConnection)
        {
            sqlConnection.AccessToken = await GetAccessToken();
        }
        return result;
    }
    public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
    {
        if (connection is SqlConnection sqlConnection)
        {
            sqlConnection.AccessToken = GetAccessToken().Result;
        }
        return result;
    }
    private Task<string> GetAccessToken() => _azureServiceTokenProvider.GetAccessTokenAsync(AzureDatabaseResourceIdentifier);
}

And this is how to configure your services:

services.AddSingleton(new DbContextFactoryOptions(connection_string));
services.AddSingleton(new AzureAuthenticationInterceptor(new AzureServiceTokenProvider()));

And finally, this is how to instantiate DbContext objects in your repository:

public async Task<IEnumerable<MyCustomEntity>> GetAll()
{
using var context = _notificationsDbContextFactory.Create();  // Injected in ctor
var dbos = await context.MyCustomEntity.ToListAsync();
return ... // something;
}

Answer 3

For those who still fall on the same problem, I've solved the problem by using a DbInterceptor so I can asynchronously get the token without blocking the application. I had opened an issue on EF Core repo but I've closed with the solution:

https://github.com/dotnet/efcore/issues/21043

I hope it help.

Answer 4

Upvoted.

This is an add-on answer to Romar's excellent answer. This was extremely useful to us and allowed us to eliminate user credentials within the ConnectionString. However, this left us with the problem of needing to retrieve the Access Token using a secret, which is sensitive information that we also do not want to include in the appsettings file. Consequently, we traded one problem for another.

There are other posts on the web that deal with this issue. So, I am posting a combined and comprehensive answer that completely removes sensitive data from the appsettings file. Note: you need to migrate the secret into the KeyVault. In this case, we named it AzureSqlSecret. This is in order to retrieve the database user's credentials.

The Entities class constructor that calls the AzureAuthenticationInterceptor is as follows:

public ProjectNameEntities() :
    base(new DbContextOptionsBuilder<ProjectNameEntities>()
        .UseSqlServer(ConfigurationManager.ConnectionStrings["ProjectNameEntities"].ConnectionString)
        .AddInterceptors(new AzureAuthenticationInterceptor())
        .Options)
{ }

AzureAuthenticationInterceptor:

#region NameSpaces
using Azure.Core;
using Azure.Identity;
using Azure.Security.KeyVault.Secrets;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Configuration;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
#endregion

namespace <ProjectName>.DataAccess.Helpers
{
    public class AzureAuthenticationInterceptor : DbConnectionInterceptor
    {
        #region Constructor
        public AzureAuthenticationInterceptor()
        {
            SecretClientOptions objSecretClientOptions;
            string strAzureKeyVaultResourceIdentifier;
            string strAzureKeyVault;
            string strAzureKeyVaultUri;

            strAzureKeyVaultResourceIdentifier = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:KeyVault"];
            strAzureKeyVault = ConfigurationManager.AppSettings["Azure:KeyVaults:TaxPaymentSystem"];
            strAzureKeyVaultUri = strAzureKeyVaultResourceIdentifier.Replace("{0}", strAzureKeyVault);

            // Set the options on the SecretClient. These are default values that are recommended by Microsoft.
            objSecretClientOptions = new SecretClientOptions()
            {
                Retry =
                {
                    Delay= TimeSpan.FromSeconds(2),
                    MaxDelay = TimeSpan.FromSeconds(16),
                    MaxRetries = 5,
                    Mode = RetryMode.Exponential
                }
            };

            this.SecretClient = new SecretClient(
                vaultUri: new Uri(strAzureKeyVaultUri),
                credential: new DefaultAzureCredential(), 
                objSecretClientOptions
                );

            this.KeyVaultSecret = this.SecretClient.GetSecret("AzureSqlSecret");
            this.strKeyVaultSecret = this.KeyVaultSecret.Value;

            this.strAzureResourceIdentifierAuthentication = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:Authentication"];
            this.strAzureResourceIdentifierDatabase = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:DataBase"];
            this.strClientId = ConfigurationManager.AppSettings["Azure:DatabaseUsername:ClientId"];
            this.strTenantId = ConfigurationManager.AppSettings["Azure:TenantId"];                
        }
        #endregion

        #region Methods
        public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
            DbConnection objDbConnection,
            ConnectionEventData objEventData,
            InterceptionResult objReturn,
            CancellationToken objCancellationToken = default)
        {
            _ILogger.Debug("Reached the Async Interceptor method");

            if (objDbConnection is SqlConnection objSqlConnection)
            {
                objSqlConnection.AccessToken = GetAccessToken();
            }

            return objReturn;
        }

        public override InterceptionResult ConnectionOpening(
            DbConnection objDbConnection,
            ConnectionEventData objConnectionEventData,
            InterceptionResult objReturn)
        {
            _ILogger.Debug("Reached the non-Async Interceptor method");

            if (objDbConnection is SqlConnection objSqlConnection)
            {
                objSqlConnection.AccessToken = GetAccessToken();
            }

            return objReturn;
        }

        private string GetAccessToken()
        {
            AuthenticationContext objAuthenticationContext;
            AuthenticationResult objAuthenticationResult;
            ClientCredential objClientCredential;

            objAuthenticationContext = new AuthenticationContext(string.Format("{0}/{1}"
                                                                                , this.strAzureResourceIdentifierAuthentication
                                                                                , this.strTenantId));
            objClientCredential = new ClientCredential(this.strClientId, this.strKeyVaultSecret);
            objAuthenticationResult = objAuthenticationContext.AcquireTokenAsync(this.strAzureResourceIdentifierDatabase, objClientCredential).Result;
            return objAuthenticationResult.AccessToken;
        }
        #endregion

        #region Properties
        readonly <ProjectName>.Common.Logging.ILogger _ILogger = <ProjectName>.Common.Logging.LogWrapper.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private SecretClient SecretClient;
        private KeyVaultSecret KeyVaultSecret;
        private string strAzureResourceIdentifierDatabase;
        private string strAzureResourceIdentifierAuthentication;
        private string strKeyVaultSecret;
        private string strClientId;
        private string strTenantId;
        #endregion
    }
}

Answer 5

For developers using .NET Framework for Managed Identity, the below code might be helpful for getting the entity connection:

app.config:

<add key="ResourceId" value="https://database.windows.net/" />
<add key="Con" value="data source=tcp:sampledbserver.database.windows.net,1433;initial catalog=sampledb;MultipleActiveResultSets=True;Connect Timeout=30;" />

c# file

using System;
using System.Configuration;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Azure.Services.AppAuthentication;

public static EntityConnection GetEntityConnectionString()
{
    MetadataWorkspace workspace = new MetadataWorkspace(
       new string[] { "res://*/" },
       new Assembly[] { Assembly.GetExecutingAssembly() });

    SqlConnection sqlConnection = new SqlConnection(Con);

    var result = (new AzureServiceTokenProvider()).GetAccessTokenAsync(ResourceId).Result;

    sqlConnection.AccessToken = result ?? throw new InvalidOperationException("Failed to obtain the access token");

    EntityConnection entityConnection = new EntityConnection(
        workspace,
        sqlConnection);

    return entityConnection;
}

Answer 6

After the arrival of Microsoft.Data.SqlClient - new version of Entity framework core connector to sql - its very simple now:

Install-Package Microsoft.Data.SqlClient -Version 4.0.1

Add connection string to Dotnet core application like below:

"Server=tcp:<server-name>.database.windows.net;Authentication=Active Directory Default; Database=<database-name>;"

Then use it for conencting to Azure SQL using managed identity via Azure SQL connection like below:

            using (SqlConnection _connection = new SqlConnection(sqlConnectionString))
            {
                _connection.Open();

                // do some stuff with the sqlconnection to read or write record in SQL.

                _connection.Close();

                return true;
            }

Refer here for detailed article