3

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.

secretply
  • 55
  • 6
  • 1
    From what I understand looking at [this link](https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cefcore%2Cdotnet#3-modify-your-project), you just need to set `Authentication` to `Active Directory Default` in your connection string. They have specifically mentioned that `DefaultAzureCredential` caches the token and retrieves it before expiration. – prinkpan Feb 01 '23 at 04:03

1 Answers1

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

prinkpan
  • 2,117
  • 1
  • 19
  • 32
  • Thank you for the Microsoft documentation link. Based on initial changes, everything seems to be working but obviously won't know for sure until about 24 hours or so but everything makes sense that tokens will be cached and refreshed automatically with `Authentication` in the connection string. – secretply Feb 01 '23 at 05:31
  • @secretply, I hope you got a chance to check this after 24 hours! Please share the update so it will be helpful for someone who finds this answer later. – prinkpan Feb 02 '23 at 03:43
  • 1
    I had to make some changes that restarted the function app but it has been about 36 hours now and I am not receiving any exceptions or error messages related to SQL authentication or token expiration. @Priyank Panchal Thank you for the help. – secretply Feb 03 '23 at 13:03