c# - 使用托管标识运行 EF Core 迁移的 Azure DevOps 管道

Question

最近，我迁移了一个旧项目以使用托管标识来访问 Azure SQL Server。这在部署的 Azure 应用服务和本地运行时都可以正常工作。现在的问题是我们有一个用于 CI/CD 的 Azure DevOps Pipeline，其中一项任务涉及运行数据库迁移。在我进行更改后，迁移任务失败，我只能假设这是因为它无法获取访问 token ，尽管输出(即使启用了详细设置)也没有真正给我太多线索。

关于我迄今为止所做工作的一些背景。管 Prop 有有效的服务主体连接，当前该连接仅具有 user_impersonation 委托(delegate)权限。不确定是否需要在这里添加其他内容。

服务连接是自定义 DevSqlAdmins Active Directory 组的成员DevSqlAdmins 组被设置为我的 SQL 服务器的贡献者。

最后，数据库用户 devsqladmins 创建为外部组，应与 Active Directory 组绑定(bind)。

以下是运行迁移的 DevOps Pipeline 作业的输出。

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:\Windows\system32\cmd.exe" /D /E:ON /V:OFF /S /C "CALL "D:\a\_temp\0dca2055-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:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj'.
Using startup project 'D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj'.
Writing 'D:\a\1\s\MY_PROJECT.Models\obj\MY_PROJECT.Models.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\VssAdministrator\AppData\Local\Temp\tmp9BBC.tmp /verbosity:quiet /nologo D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj
Writing 'D:\a\1\s\MY_PROJECT.Models\obj\MY_PROJECT.Models.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\VssAdministrator\AppData\Local\Temp\tmp9FF3.tmp /verbosity:quiet /nologo D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj
Build started...
dotnet build D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj /verbosity:quiet /nologo
D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(66,46): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(123,30): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(277,30): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.InternalUsageDiagnosticAnalyzer cannot be created from C:\Users\VssAdministrator\.nuget\packages\microsoft.entityframeworkcore.analyzers\7.0.3\analyzers\dotnet\cs\Microsoft.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:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj]

Build succeeded.

D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(66,46): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(123,30): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
D:\a\1\s\Nex-Core.Utilities\Helpers\AzureHelper.cs(277,30): warning CS0168: The variable 'ex' is declared but never used [D:\a\1\s\Nex-Core.Utilities\Nex-Core.Utilities.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.InternalUsageDiagnosticAnalyzer cannot be created from C:\Users\VssAdministrator\.nuget\packages\microsoft.entityframeworkcore.analyzers\7.0.3\analyzers\dotnet\cs\Microsoft.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:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj]
CSC : warning CS8032: An instance of analyzer Microsoft.EntityFrameworkCore.UninitializedDbSetDiagnosticSuppressor cannot be created from C:\Users\VssAdministrator\.nuget\packages\microsoft.entityframeworkcore.analyzers\7.0.3\analyzers\dotnet\cs\Microsoft.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:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj]
    5 Warning(s)
    0 Error(s)

Time Elapsed 00:03:18.85
Build succeeded.
dotnet exec --depsfile D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0\MY_PROJECT.Models.deps.json --additionalprobingpath C:\Users\VssAdministrator\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --additionalprobingpath "C:\Program Files (x86)\Microsoft\Xamarin\NuGet" --runtimeconfig D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0\MY_PROJECT.Models.runtimeconfig.json C:\Users\VssAdministrator\.dotnet\tools\.store\dotnet-ef\7.0.3\dotnet-ef\7.0.3\tools\net6.0\any\tools\netcoreapp2.0\any\ef.dll database update --assembly D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0\MY_PROJECT.Models.dll --project D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj --startup-assembly D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0\MY_PROJECT.Models.dll --startup-project D:\a\1\s\MY_PROJECT.Models\MY_PROJECT.Models.csproj --project-dir D:\a\1\s\MY_PROJECT.Models\ --root-namespace MY_PROJECT.Models --language C# --framework net6.0 --working-dir D:\a\1\s --verbose
Using assembly 'MY_PROJECT.Models'.
Using startup assembly 'MY_PROJECT.Models'.
Using application base 'D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0'.
Using working directory 'D:\a\1\s\MY_PROJECT.Models'.
Using root namespace 'MY_PROJECT.Models'.
Using project directory 'D:\a\1\s\MY_PROJECT.Models\'.
Remaining arguments: .
Using configuration file 'D:\a\1\s\MY_PROJECT.Models\bin\Debug\net6.0\MY_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\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\ActiveDirectoryAuthenticationProvider.cs:line 160
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<<GetFedAuthToken>b__1>d.MoveNext() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2404
--- End of stack trace from previous location ---
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 0
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPool.NetCoreApp.cs:line 18
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 779
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1759
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1162
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1130
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 122
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 341
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 39
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1844
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.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

我将添加身份验证方法在连接字符串中指定为Server=tcp:my_project.database.windows.net;初始目录=development;持久安全信息=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;身份验证=\"Active Directory 默认值\"; 那么，我是否需要以某种方式自己获取访问 token ，或者是否需要配置某些内容，以便我的 DbContext 类可以从管道的服务主体连接获取访问 token 。

Answer 1

我为我的 Azure SQL DB 创建了一个 Azure VM 托管标识，如下所示:-

创建了一个 Azure VM 作为托管标识，如下所示:-

允许 VM 作为托管标识访问 Azure SQL:-

我从本地计算机上使用 Active Directory 托管身份运行代码，但无法检索访问 token 。

现在，我尝试从我的虚拟机(托管身份)运行相同的连接，并得到如下输出:-

您需要在连接字符串中使用Active Directory MSI或Active Directory托管身份，以使托管身份正常工作并获取访问 token ，请参阅以下代码:-

'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. ```

如果您使用自托管代理来运行 Azure DevOps 任务，请确保验证您的 Azure SQL 数据库“网络”选项卡中是否允许使用 DevOps 自托管代理的 IP 地址以及托管身份应用服务或 VM知识产权。请参阅以下内容:-

您可以引用下面的任务来使用 DevOps 任务获取访问 token 并通过 EF 核心托管标识连接到您的 Azure SQL:-

引用:-

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