Azure Pipeline调用控制台程序无法通过AD/Entra连接Azure SQL

问题背景 #

我在Azure Pipeline中调用控制台.exe执行EF Core迁移，数据库要求通过Azure AD/Entra认证（连接字符串包含Authentication=Active Directory Default;）。DevOps项目已在Azure AD中配置服务主体并赋予订阅Contributor权限，此前使用SqlAzureDacpacDeployment@1任务操作数据库完全正常，但调用exe时出现认证失败错误：

Microsoft.Data.SqlClient.SqlException (0x80131904): DefaultAzureCredential failed to retrieve a token from the included credentials. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/defaultazurecredential/troubleshoot
- EnvironmentCredential authentication unavailable. Environment variables are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/environmentcredential/troubleshoot
- WorkloadIdentityCredential authentication unavailable. The workload options are not fully configured. See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/workloadidentitycredential/troubleshoot
- ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.
Status: 400 (Bad Request)

核心原因 #

SqlAzureDacpacDeployment@1任务内部已集成Azure AD身份认证逻辑，会自动使用Pipeline的服务主体/托管身份获取数据库访问令牌；而你的.exe通过DefaultAzureCredential获取身份时，需要明确提供可识别的凭证源，无法直接"继承"Pipeline权限。

解决方案 #

方案1：通过AzureCLI任务传递身份 #

利用AzureCLI@2任务自动完成服务主体登录并设置环境变量，让DefaultAzureCredential能识别并获取令牌：

- task: AzureCLI@2
  inputs:
    azureSubscription: '你的服务主体订阅连接名称'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      cd $(Pipeline.Workspace)/ef-migrator-exe
      echo "Connection string: ${{ parameters.masterConnectionString }}"
      EfMigrator.exe -c "${{ parameters.masterConnectionString }}"

方案2：手动注入服务主体环境变量 #

直接将服务主体的身份信息设置为环境变量，供DefaultAzureCredential的EnvironmentCredential读取：

- script: |
    cd $(Pipeline.Workspace)/ef-migrator-exe
    echo "Connection string: ${{ parameters.masterConnectionString }}"
    export AZURE_CLIENT_ID=$(servicePrincipalId)
    export AZURE_TENANT_ID=$(tenantId)
    export AZURE_CLIENT_SECRET=$(servicePrincipalKey)
    EfMigrator.exe -c "${{ parameters.masterConnectionString }}"
  displayName: 'Execute EF Migrations'
  env:
    servicePrincipalId: $(AzureServicePrincipalId)
    tenantId: $(AzureTenantId)
    servicePrincipalKey: $(AzureServicePrincipalKey)

方案3：针对托管身份的配置 #

如果Pipeline使用Azure DevOps托管身份而非服务主体，需先给托管身份授予数据库的AD访问权限（如SQL DB Contributor），再通过环境变量指定托管身份ID：

- script: |
    cd $(Pipeline.Workspace)/ef-migrator-exe
    echo "Connection string: ${{ parameters.masterConnectionString }}"
    EfMigrator.exe -c "${{ parameters.masterConnectionString }}"
  displayName: 'Execute EF Migrations'
  env:
    AZURE_CLIENT_ID: $(System.ManagedIdentityClientId)

内容的提问来源于stack exchange，提问作者David Masters