azure-devops entity-framework-core azure-functions entity-framework-migrations sql-server-authentication

Receiving TaskCanceledException when using passwordless authentication with Azure Functions and Entity Framework with SQL Server

I am using Isolated Azure Functions and SQL Server with Entity Framework. This is configuration I am having inside my Program.cs of corresponding Azure Function App:

var connectionString = Configuration.GetConnectionString("AzureSQL");

services.AddDbContext<ReportDataDbContext>(options =>
{
    options
        .UseSqlServer(connectionString)
        .EnableSensitiveDataLogging(queryParamsLogging);
});

My Azure Function has UserManagedIdentity assigned and my SQL Server has assigned to this UserManagedIdentity too. From my pipeline in Azure DevOps I am trying to apply migration via http request to Azure Function.

  displayName: 'Applying Migrations EF'
  inputs:
    pwsh: true
    targetType: inline
    script: |
      $env = "${{ parameters.env }}"
      # step 1 receive auth token to call az function
      $applyMigrationFunctionUrl = "https://api-$env.azazello.pl/${{ parameters.apiTypeUrl }}/migrations/apply/${{ parameters.apiTypeUrl }}"
      
      echo "Receiving application token..."
      $body = @{
          client_id = "${{ parameters.clientId }}"
          scope = "${{ parameters.authAllowedAudience }}/.default"
          client_secret = "${{ parameters.secretId}}"
          grant_type = "client_credentials"
      }
      
      $tenantId = "${{ variables.tenantId }}"
      $azToken = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -ContentType "application/x-www-form-urlencoded" -Body $body | Select-Object -ExpandProperty access_token

      echo "Token received."
      $headers = @{
          'Authorization' = "Bearer $azToken"
      }
      # step 2 call apply migrations endpoint of corresponding az-function
      $response = Invoke-RestMethod -ConnectionTimeoutSeconds 180 -OperationTimeoutSeconds 180 -RetryIntervalSec 20 -MaximumRetryCount 3  -Uri $applyMigrationFunctionUrl -Method Post -Headers $headers

Endpoint code:


   [Function(nameof(ApplyMigrationsAllReporting))]
   public async Task<IActionResult> ApplyMigrationsAllReporting(
   [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "migrations/apply/api/reporting")]
   HttpRequest httpRequest,
   CancellationToken cancellationToken)
   {
      _logger.LogWarning(
      "[ApplyMigrationsAllReporting] Applying all pending migrations. CancellationToken: {CancellationToken}", cancellationToken);
      await _dbContext.Database.MigrateAsync(cancellationToken);
      return new OkResult();
   }

The problem is: sporadically after deployment the first request can be failed with the following error:

System.Threading.Tasks.TaskCanceledException:
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenInternalAsync>d__70.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenInternalAsync>d__70.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection+<OpenAsync>d__66.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1+ConfiguredTaskAwaiter.GetResult (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator+<>c__DisplayClass20_0+<<ExistsAsync>b__0>d.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator+<>c__DisplayClass20_0+<<ExistsAsync>b__0>d.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1+ConfiguredTaskAwaiter.GetResult (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1+ConfiguredTaskAwaiter.GetResult (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository+<ExistsAsync>d__24.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1+ConfiguredTaskAwaiter.GetResult (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator+<MigrateAsync>d__15.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=8.0.5.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Test.Azure.ReportApi.Http.DbMigrationFunctions+<ApplyMigrationsAllReporting>d__3.MoveNext (Test.Azure.ReportApi, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null: D:\a\1\s\ReportApi\Test.Azure.ReportApi\Http\DbMigrationsFunctions.cs:32)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Test.Azure.ReportApi.DirectFunctionExecutor+<ExecuteAsync>d__3.MoveNext (Test.Azure.ReportApi, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null: D:\a\1\s\ReportApi\Test.Azure.ReportApi\obj\Release\net8.0\Microsoft.Azure.Functions.Worker.Sdk.Generators\Microsoft.Azure.Functions.Worker.Sdk.Generators.FunctionExecutorGenerator\GeneratedFunctionExecutor.g.cs:50)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.Azure.Functions.Worker.OutputBindings.OutputBindingsMiddleware+<Invoke>d__0.MoveNext (Microsoft.Azure.Functions.Worker.Core, Version=1.18.0.0, Culture=neutral, PublicKeyToken=551316b6919f366c: D:\a\_work\1\s\src\DotNetWorker.Core\OutputBindings\OutputBindingsMiddleware.cs:13)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore.FunctionsHttpProxyingMiddleware+<Invoke>d__4.MoveNext (Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore, Version=1.2.1.0, Culture=neutral, PublicKeyToken=551316b6919f366c: D:\a\_work\1\s\extensions\Worker.Extensions.Http.AspNetCore\src\FunctionsMiddleware\FunctionsHttpProxyingMiddleware.cs:48)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e)
   at Microsoft.Azure.Functions.Worker.FunctionsApplication+<InvokeFunctionAsync>d__10.MoveNext (Microsoft.Azure.Functions.Worker.Core, Version=1.18.0.0, Culture=neutral, PublicKeyToken=551316b6919f366c: D:\a\_work\1\s\src\DotNetWorker.Core\FunctionsApplication.cs:89)

My connection string is: Server=tcp:sqlMy,1433;Authentication=Active Directory Default;Encrypt=True;User Id=GUID_OF_UserManagedIdentity;Database=MyDb;MultipleActiveResultSets=True;Connection Timeout=60;TrustServerCertificate=True

I added retry in powershell invoke-restmethod (code above in powershell), and with retry everything works fine in sum -> first request may fail sporadically, but with retry the second request(next try with fully the same data) executes successfully. So, the connection is fine with db. UserManagedIdentity and corresponding user in database has all required roles(db_reader,db_writer,ddl_admin).

Goal: I would like to avoid this retry in powershell and understand why this task is cancelled when I try to migrate database even if there are no pending migration, just sporadically fail after deployment...

It started to be occurred when I tried using passwordless authentication, before that, I was using sql authnetication and everything was fine

I have found similar topic but without answer :( :

https://learn.microsoft.com/en-gb/answers/questions/1454998/running-database-update-for-entityframeworkcore-re

I appreciate any help and advices!

Solution

I was also facing a similar issue with TaskCanceledException in my project. The second option below helped me to resolve my case. Referring to this issue, you can try several things to fix your issue:

Try to remove all packages referenced to Microsoft.Extensions.* if you do not use ASP.NET Core integration in your isolated functions.
If you use ASP.NET Core integration, try adding <PublishReadyToRun>true</PublishReadyToRun> and <RuntimeIdentifier>linux-x64</RuntimeIdentifier> or <RuntimeIdentifier>win-x64</RuntimeIdentifier> to the *.csproj file of your Azure function project.