How to generate EF Core migrations script when ConnectionString is only known after ARM template deployment?

Question

I want to release an app to Azure and deploy migrations to a database before deploying the Web App. That sounds relatively simple, you can create a migrations.sql script with dotnet-ef in your Build pipeline and apply this script in your Release pipeline.

However, I cannot create a a migrations.sql script in the Build pipeline as I am using four different databases for a DTAP environment. Thus, I would need to generate a migrations.sql script per environment and perform these separately against each of the databases. (as I understand it)

In my Release pipeline I use an incremental ARM template to deploy resources and set the ConnectionString (which comes from an Azure Key Vault) in the Azure Web App application settings configuration.

How/where do I generate the migrations.sql script? Do I do this in a Release pipeline? Am I making a major mistake in my reasoning?

---

EDIT:

Thanks for Madej's answer that shows the environment doesn't matter. I tried implementing creating the migrations.sql script in my pipelines.

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://learn.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  projects: '**/*.csproj'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: DotNetCoreCLI@2
  displayName: "Install dotnet-ef"
  inputs:
    command: 'custom'
    custom: 'tool'
    arguments: 'install --global dotnet-ef'

- task: DotNetCoreCLI@2
  displayName: "Restore tools"
  inputs:
    command: 'custom'
    custom: 'tool'
    arguments: 'restore'

- task: DotNetCoreCLI@2
  displayName: "Restore"
  inputs:
    command: 'restore'
    projects: '$(projects)'
    feedsToUse: 'select'

- task: DotNetCoreCLI@2
  displayName: "Build"
  inputs:
    command: 'build'
    projects: '$(projects)'
    arguments: '--configuration $(BuildConfiguration)'

- task: DotNetCoreCLI@2
  displayName: "Create migrations.sql"
  inputs:
    command: 'custom'
    custom: 'ef'
    arguments: 'migrations script --configuration $(BuildConfiguration) --no-build --idempotent --output $(Build.ArtifactStagingDirectory)\migrations.sql'
    workingDirectory: 'WebApi.api'

- task: DotNetCoreCLI@2
  displayName: "Publish"
  inputs:
    command: 'publish'
    publishWebProjects: true
    arguments: '--configuration $(BuildConfiguration) --output $(Build.ArtifactStagingDirectory)'
    zipAfterPublish: false

- task: PublishBuildArtifacts@1
  displayName: "Publish to Azure Pipelines"
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)'
    ArtifactName: 'drop'
    publishLocation: 'Container'

My pipeline doesn't work, in the task "Create migrations.sql" I run into the following error:

An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: DefaultAzureCredential failed to retrieve a token from the included credentials.
- EnvironmentCredential authentication unavailable. Environment variables are not fully configured.
- ManagedIdentityCredential authentication unavailable. No Managed Identity endpoint found.
- Visual Studio Token provider can't be accessed at C:\Users\VssAdministrator\AppData\Local\.IdentityService\AzureServiceAuth\tokenprovider.json
- Stored credentials not found. Need to authenticate user in VSCode Azure Account.
- Please run 'az login' to set up account

This is because in my Program.cs I add a keyvault and authenticate with the Azure.Identity DefaultAzureCredential as follows:

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureWebHostDefaults(webBuilder =>
        {
            webBuilder.ConfigureAppConfiguration((hostingContext, config) =>
            {
                var settings = config.Build();

                var credentials = new DefaultAzureCredential(
                    new DefaultAzureCredentialOptions() {
                        ExcludeSharedTokenCacheCredential = true,
                        VisualStudioTenantId = settings["VisualStudioTenantId"],
                    }
                );

                config.AddAzureKeyVault(new Uri(settings["KeyVault:Endpoint"]), credentials).Build();
            })
            .UseStartup<Startup>();
        });

The Azure Pipelines cannot get a token from DefaultAzureCredential. How do I authenticate the Azure Pipelines?

Answer 1

I have figured out the solution to the problem in my edit. The primary way that the DefaultAzureCredential class gets credentials is via environment variables.

Thus, I had to define the environment variables somewhere. I didn't want to do this in the pipeline variables to avoid having to manage them as they should be available from the project in the form of a service connection to Azure.

I did the following:

1. In my pipelines added an AzureCLI task to read out the service principal id, key and tenant id and set them to job variables as follows:

- task: AzureCLI@2
  inputs:
    azureSubscription: '<subscription>'
    scriptType: 'ps'
    scriptLocation: 'inlineScript'
    inlineScript: |
      Write-Host '##vso[task.setvariable variable=AZURE_CLIENT_ID]'$env:servicePrincipalId
      Write-Host '##vso[task.setvariable variable=AZURE_CLIENT_SECRET]'$env:servicePrincipalKey
      Write-Host '##vso[task.setvariable variable=AZURE_TENANT_ID]'$env:tenantId
    addSpnToEnvironment: true

2. In my "Create migrations.sql" task pass these variables as environment variables as follows:

- task: DotNetCoreCLI@2
  displayName: "Create migrations.sql"
  inputs:
    command: 'custom'
    custom: 'ef'
    arguments: 'migrations script --configuration $(BuildConfiguration) --no-build --idempotent --output $(Build.ArtifactStagingDirectory)\migrations.sql'
    workingDirectory: 'WebApi.api'
  env:
    AZURE_CLIENT_ID: $(AZURE_CLIENT_ID)
    AZURE_CLIENT_SECRET: $(AZURE_CLIENT_SECRET)
    AZURE_TENANT_ID: $(AZURE_TENANT_ID)

3. Added the service principal to the Azure Key Vault RBAC as a Key Vault Secrets User. I could only do this with az:

az role assignment create --role 'Key Vault Secrets User (preview)' --scope '/subscriptions/<subscription ID>/resourcegroups/<resource group name>/providers/Microsoft.KeyVault/vaults/<vault name>' --assignee '<service principal object id>'

This absolutely solved my problems without having to manage any more secrets/variables as they are all contained in the pipeline itself and don't pose any security threats.