Azure DevOps Pipeline Running EF Core Migrations with Managed Identity

DillonDrobena
February 16, 2023
171 views
1 vote
2 Answers

Recently I migrated an old project to use Managed Identity in order to access an Azure SQL Server. This works fine on both the deployed Azure App Service and when running locally. The problem now is we have an Azure DevOps Pipeline for CI/CD and one of the tasks involves running the database migrations. After my changes the migrations task is failing and I can only assume it is because it’s unable to obtain an Access Token, although the output, even with the verbose setting enabled, doesn’t really give me much of a clue.

Some background on what I’ve done so far.
The pipeline has a valid service principal connection, currently the connection only has the user_impersonation delegated permission. Not sure if I need to add anything else here.

The service connection is a member of a custom DevSqlAdmins Active Directory group
The DevSqlAdmins group is set as a Contributor to my SQL server.

Finally there is the database user devsqladmins created as an External Group that should be tied to the Active Directory group.

Here is the output from my DevOps Pipeline job for running the migration.

Starting: Run Migrations
==============================================================================
Task         : Command line
Description  : Run a command line script using Bash on Linux and macOS and cmd.exe on Windows
Version      : 2.212.0
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/command-line
==============================================================================
Generating script.
Script contents: shell
dotnet ef database update --project MY_PROJECT.Models/MY_PROJECT.Models.csproj -v
========================== Starting Command Output ===========================
"C:Windowssystem32cmd.exe" /D /E:ON /V:OFF /S /C "CALL "D:a_tempdca2055-13bb-4bf3-b01f-171c32526237.cmd""

Welcome to .NET 6.0!
---------------------
SDK Version: 6.0.100

Telemetry
---------
The .NET tools collect usage data in order to help us improve your experience. It is collected by Microsoft and shared with the community. You can opt-out of telemetry by setting the DOTNET_CLI_TELEMETRY_OPTOUT environment variable to '1' or 'true' using your favorite shell.

Read more about .NET CLI Tools telemetry: https://aka.ms/dotnet-cli-telemetry

----------------
Installed an ASP.NET Core HTTPS development certificate.
To trust the certificate run 'dotnet dev-certs https --trust' (Windows and macOS only).
Learn about HTTPS: https://aka.ms/dotnet-https
----------------
Write your first app: https://aka.ms/dotnet-hello-world
Find out what's new: https://aka.ms/dotnet-whats-new
Explore documentation: https://aka.ms/dotnet-docs
Report issues and find source on GitHub: https://github.com/dotnet/core
Use 'dotnet --help' to see available commands or visit: https://aka.ms/dotnet-cli
--------------------------------------------------------------------------------------
Using project 'D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj'.
Using startup project 'D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj'.
Writing 'D:a1sMY_PROJECT.ModelsobjMY_PROJECT.Models.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:UsersVssAdministratorAppDataLocalTemptmp9BBC.tmp /verbosity:quiet /nologo D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj
Writing 'D:a1sMY_PROJECT.ModelsobjMY_PROJECT.Models.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:UsersVssAdministratorAppDataLocalTemptmp9FF3.tmp /verbosity:quiet /nologo D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj
Build started...
dotnet build D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj /verbosity:quiet /nologo
D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(66,46): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(123,30): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(277,30): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.InternalUsageDiagnosticAnalyzer cannot be created from C:UsersVssAdministrator.nugetpackagesmicrosoft.entityframeworkcore.analyzers7.0.3analyzersdotnetcsMicrosoft.EntityFrameworkCore.Analyzers.dll : Could not load file or assembly 'Microsoft.CodeAnalysis, Version=4.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. The system cannot find the file specified.. [D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj]

Build succeeded.

D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(66,46): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(123,30): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
D:a1sNex-Core.UtilitiesHelpersAzureHelper.cs(277,30): warning CS0168: The variable 'ex' is declared but never used [D:a1sNex-Core.UtilitiesNex-Core.Utilities.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.InternalUsageDiagnosticAnalyzer cannot be created from C:UsersVssAdministrator.nugetpackagesmicrosoft.entityframeworkcore.analyzers7.0.3analyzersdotnetcsMicrosoft.EntityFrameworkCore.Analyzers.dll : Could not load file or assembly 'Microsoft.CodeAnalysis, Version=4.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. The system cannot find the file specified.. [D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.UninitializedDbSetDiagnosticSuppressor cannot be created from C:UsersVssAdministrator.nugetpackagesmicrosoft.entityframeworkcore.analyzers7.0.3analyzersdotnetcsMicrosoft.EntityFrameworkCore.Analyzers.dll : Could not load file or assembly 'Microsoft.CodeAnalysis, Version=4.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. The system cannot find the file specified.. [D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj]
    5 Warning(s)
    0 Error(s)

Time Elapsed 00:03:18.85
Build succeeded.
dotnet exec --depsfile D:a1sMY_PROJECT.ModelsbinDebugnet6.0MY_PROJECT.Models.deps.json --additionalprobingpath C:UsersVssAdministrator.nugetpackages --additionalprobingpath "C:Program Files (x86)Microsoft Visual StudioSharedNuGetPackages" --additionalprobingpath "C:Program Files (x86)MicrosoftXamarinNuGet" --runtimeconfig D:a1sMY_PROJECT.ModelsbinDebugnet6.0MY_PROJECT.Models.runtimeconfig.json C:UsersVssAdministrator.dotnettools.storedotnet-ef7.0.3dotnet-ef7.0.3toolsnet6.0anytoolsnetcoreapp2.0anyef.dll database update --assembly D:a1sMY_PROJECT.ModelsbinDebugnet6.0MY_PROJECT.Models.dll --project D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj --startup-assembly D:a1sMY_PROJECT.ModelsbinDebugnet6.0MY_PROJECT.Models.dll --startup-project D:a1sMY_PROJECT.ModelsMY_PROJECT.Models.csproj --project-dir D:a1sMY_PROJECT.Models --root-namespace MY_PROJECT.Models --language C# --framework net6.0 --working-dir D:a1s --verbose
Using assembly 'MY_PROJECT.Models'.
Using startup assembly 'MY_PROJECT.Models'.
Using application base 'D:a1sMY_PROJECT.ModelsbinDebugnet6.0'.
Using working directory 'D:a1sMY_PROJECT.Models'.
Using root namespace 'MY_PROJECT.Models'.
Using project directory 'D:a1sMY_PROJECT.Models'.
Remaining arguments: .
Using configuration file 'D:a1sMY_PROJECT.ModelsbinDebugnet6.0MY_PROJECT.Models.dll.config'.
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'MY_PROJECT.Models'...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string[])' was found on class 'Program'.
No application service provider was found.
Finding DbContext classes in the project...
Found DbContext 'MY_PROJECTDBEntities'.
Using context 'MY_PROJECTDBEntities'.
Finding design-time services referenced by assembly 'MY_PROJECT.Models'...
Finding design-time services referenced by assembly 'MY_PROJECT.Models'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'MY_PROJECT.Models'...
No design-time services were found.
Creating DbConnection.
Created DbConnection. (90ms).
Migrating using database 'development-production-copy' on server 'tcp:MY_PROJECT.database.windows.net'.
Opening connection to database 'development-production-copy' on server 'tcp:MY_PROJECT.database.windows.net'.
An error occurred using the connection to database 'development-production-copy' on server 'tcp:MY_PROJECT.database.windows.net'.
'MY_PROJECTDBEntities' disposed.
Disposing connection to database 'development-production-copy' on server 'tcp:MY_PROJECT.database.windows.net'.
Disposed connection to database '' on server '' (1ms).
Microsoft.Data.SqlClient.SqlException (0x80131904): A task was canceled.
 ---> System.Threading.Tasks.TaskCanceledException: A task was canceled.
   at Azure.Identity.AzureCliCredential.RequestCliAccessTokenAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
   at Azure.Identity.AzureCliCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
   at Azure.Identity.AzureCliCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.AzureCliCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
   at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters) in D:a_work1ssrcMicrosoft.Data.SqlClientsrcMicrosoftDataSqlClientActiveDirectoryAuthenticationProvider.cs:line 160
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<<GetFedAuthToken>b__1>d.MoveNext() in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataSqlClientSqlInternalConnectionTds.cs:line 2404
--- End of stack trace from previous location ---
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataSqlClientSqlInternalConnectionTds.cs:line 0
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionPool.NetCoreApp.cs:line 18
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionPool.cs:line 779
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionPool.cs:line 1759
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionPool.cs:line 1162
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionPool.cs:line 1130
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataProviderBaseDbConnectionFactory.cs:line 122
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcCommonsrcMicrosoftDataProviderBaseDbConnectionInternal.cs:line 341
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcCommonsrcMicrosoftDataProviderBaseDbConnectionClosed.cs:line 39
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataSqlClientSqlConnection.cs:line 1844
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in D:a_work1ssrcMicrosoft.Data.SqlClientnetcoresrcMicrosoftDataSqlClientSqlConnection.cs:line 1333
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext _, TState s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:d6f7446e-be0f-491e-b450-168b30cd4b59
ClientConnectionId before routing:16fa382e-12b7-4748-9486-4bcf9e64f0dd
Routing Destination:b2cb5b1e94e5.tr274.eastus1-a.worker.database.windows.net,11042
A task was canceled.
##[error]Cmd.exe exited with code '1'.
Finishing: Run Migrations

I will add that the authentication method is specified in the connection string as
Server=tcp:my_project.database.windows.net;Initial Catalog=development;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Default"; So do I need to somehow obtain the access token myself, or does something need to be configured such that my DbContext class can fetch the access token from the pipeline’s service principal connection.

Answers

Chosen as BEST ANSWER
- DillonDrobena
- February 24, 2023 at 4:57 pm
- 0 votes
0
I ended up resolving the issue. First, I realized I didn't add my Pipelines YAML file to my original question, but previously the command to run migrations was
```
- task: CmdLine@2
  inputs:
    script: |
      dotnet ef database update --project MyProj.Models/MyProj.Models.csproj
  displayName: 'Run Migrations'
```
I updated it instead to run Azure CLI
```
- task: AzureCLI@2
  displayName: 'Run Migrations'
  inputs:
    azureSubscription: 'MySub'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
        az --version
        az account show
        dotnet ef database update --project MyProj.Models/MyProj.Models.csproj
```
Also I was only able to get Active Directory Default as the authentication method to work in order to run the project both locally and on my Azure App Service. Because of this I had to update everything to support EF Core 7.

Finally, I was only adding my service principal to the db_datareader and db_datawriter which I believe doesn't give permission to ALTER database tables. So instead I added my service principal to the db_owner role. I'm sure there's a role that has less access than db_owner that also grants permissions to alter tables, but it that level of access works for me.

(Edit)

- SiddheshDesai
- February 24, 2023 at 1:21 pm
- 0 votes
0
I created one managed identity of my Azure VM like below for My Azure SQL DB :-

Created an Azure VM as managed identity like below:-

Allowed VM to access Azure SQL as managed identity:-

I ran the code with Active Directory Managed Identity from my local machine, And I was not able to retrieve the access token.

Now, I tried to run the same connection from my VM which is managed identity and got output like below:-

You need to use Active Directory MSI or Active Directory Managed Identity in your connection string to make the managed identity work and get the access token, Refer below code :-
```
'development-production-copy' on server
'tcp:MY_PROJECT.database.windows.net'. 'MY_PROJECTDBEntities'
disposed. Disposing connection to database
'development-production-copy' on server
'tcp:MY_PROJECT.database.windows.net'. Disposed connection to database
'' on server '' (1ms). Microsoft.Data.SqlClient.SqlException
(0x80131904): A task was canceled.  --->
System.Threading.Tasks.TaskCanceledException: A task was canceled. ```
```
If you’re using Self hosted agent to run the Azure DevOps task, Make sure you verify if the IP address of your DevOps self hosted agent is allowed in your Azure SQL database Networking tab along with your managed Identity app service or VM’s IP. Refer Below:-

You can refer the task below to get use the DevOps task to get the access token and connect to your Azure SQL via EF core managed identity:-

Reference:-

Using Azure Active Directory authentication with SqlClient – ADO.NET Provider for SQL Server | Microsoft Learn

Look ma, no passwords – using Entity Framework Core with Azure Managed Identity, App Service/Functions and Azure SQL DB | ErikEJ’s blog
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.