Refreshing Azure Active Directory access token in ASP.NET Core with dependency injection for SQL Database

0

I have an ASP.NET Core website (with Razor pages) that is using an Azure SQL Database with Entity Framework Core. I create the database context in the Startup.cs for dependency injection. The tricky part is that I'm using Azure Active Directory authentication against the database with Managed Identity from my App Service. So my connection string does not have any credentials in it and looks like this:

"DatabaseConnectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydb;Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

In the Startup.cs I'm getting an access token using the managed identity:

public void ConfigureServices(IServiceCollection services)
{
    // other stuff...

    var dbConnection = new SqlConnection(Configuration["DatabaseConnectionString"])
    {
        AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result
    };
    services.AddDbContext<AlpinehutsDbContext>(options => options.UseSqlServer(dbConnection));
}

This all works like a charm. However, after some time (I'm not sure how long exactly) the token seems to expire and then I get the following error and the database connection does no longer work until I restart the entire app service:

2019-07-30 08:31:25.635 +00:00 [Information] Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker: Executing handler method AlpinHutsDashboard.Pages.HutsModel.OnGetAsync - ModelState is Valid
2019-07-30 08:31:25.635 +00:00 [Information] Microsoft.EntityFrameworkCore.Infrastructure: Entity Framework Core 2.2.6-servicing-10079 initialized 'AlpinehutsDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
2019-07-30 08:31:25.861 +00:00 [Error] Microsoft.EntityFrameworkCore.Database.Connection: An error occurred using the connection to database 'mydb' on server 'tcp:myserver.database.windows.net,1433'.System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()--- End of stack trace from previous location where exception was thrown ---at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)ClientConnectionId:17e2a3f3-9dba-4c30-baff-5fa9e5e082c6Error Number:18456,State:1,Class:14ClientConnectionId before routing:7dc72f29-efa2-420d-8f28-8d63de1c059cRouting Destination:f0ae6a8cac94.tr5.westeurope1-a.worker.database.windows.net,11017

So my question is: How to refresh the token when I initially only get it on startup?

c#
azure
asp.net-core
entity-framework-core
azure-sql-database
asked on Stack Overflow Jul 30, 2019 by silent

1 Answer

0

For your issue, it is caused by that you passing the dbConnection to AlpinehutsDbContext. When initiailizing AlpinehutsDbContext, it will always use the dbConnection. And then, if the connection is invalid, it will throw error.

You could try to initialize dbConnection when initializing AlpinehutsDbContext like

services.AddDbContext<AlpinehutsDbContext>(options =>
{
    var dbConnection = new SqlConnection(Configuration["DatabaseConnectionString"])
    {
        AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result
    };
    options.UseSqlServer(dbConnection);
});

For this way, it will retrive the neweast token when initialize AlpinehutsDbContext.

answered on Stack Overflow Jul 31, 2019 by Tao Zhou

User contributions licensed under CC BY-SA 3.0