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

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.

CodePudding user response：

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.

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