How to recreate or refresh an access token for database connections in Azure functions?

Question

I am trying to use an Entity Framework database context for use with managed identity credentials in an Azure function. I have been able to establish a database connection. The issue is that I am not able to keep the database connection established whenever the database context is used in the Azure function.

The following code is what I have tried from the following reference: https://github.com/dotnet/efcore/issues/11928#issuecomment-455312550

Program.cs

var host = new HostBuilder()
    .ConfigureFunctionsWorkerDefaults()
    .ConfigureServices(s =>
    {
        s.AddDbContext<MyDbContext>(options => options.UseSqlServer("connectionstring"));
    })
    .Build();

host.Run();

MyDbContext.cs

public MyDbContext(DbContextOptions options) : base(options)
{
    SqlConnection mySQLConnection = (SqlConnection) Database.GetDbConnection();
    mySQLConnection.AccessToken = new DefaultAzureCredential().GetToken(new TokenRequestContext(new[]
    {
        "https://database.windows.net/.default"
    })).Token;
}

This is the exception I receive:

Exception: System.InvalidOperationException: Not allowed to change the 'AccessToken' property. The connection's current state is open.

I have also tried to do the following:

Program.cs

var host = new HostBuilder()
    .ConfigureFunctionsWorkerDefaults()
    .ConfigureServices(s =>
    {
        SqlConnection mySQLConnection = new SqlConnection("connectionstring");

        mySQLConnection.AccessToken = new DefaultAzureCredential().GetToken(new TokenRequestContext(new[]
        {
            "https://database.windows.net/.default"
        })).Token;

        s.AddDbContext<MyDbContext>(options => options.UseSqlServer(mySQLConnection));
    })
    .Build();

host.Run();

The above does work without getting the exception mentioned before but I get the following error once the token has expired:

Login failed for user '<token-identified principal>'. Token is expired

I would like to know how a new token can be created or refreshed automatically as I thought Microsoft.Data.SqlClient or .GetToken() would be able to do this. Any help would be greatly appreciated.

Answer 1

Since you are using EF core, all you need to do is change your connection string to include Authentication=Active Directory Default;. This will internally use DefaultAzureCredential which will cache and refresh the token for you.

Also, add the package reference

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.0" />

The solution that you have implemented is useful for Entity Framework prior to core. You can modify your code to make a simple SQL connection with new connection string.

More details can be found at this Microsoft documentation