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

Ive seen answers here focusing on the environment variables to enable the execution of your app when script building,

This is unnecessary, instead implement IDesignTimeDbContextFactory

This avoids dotnet ef migration requiring to build the host.

See https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=dotnet-core-cli

Answer 2

You can do this in a build pipeline because migration.sql script makes some checks if specific migration was already applied or not.

To create migration script when you use Azure Key Vault in you confiugration the easiest way is to run command from Azure Clit task:

  - task: AzureCLI@2
    inputs:
      azureSubscription: 'rg-tcm-si'
      scriptType: 'pscore'
      scriptLocation: 'inlineScript'
      inlineScript: 'dotnet ef migrations script --configuration $(BuildConfiguration) --no-build --idempotent --output $(Build.ArtifactStagingDirectory)\migrations.sql'
      workingDirectory: 'Itan.Database'

Before that you need to add get and list permissions to your serivde principal which is behind your connection service:

And then even if you need to deploy the same script to different environments/databases it is all fine until they haven't been drifted. So if you do all changes through ef core you are good to go with migration.sql done once and applied many times.

In database you should have:

which contains already applied migrations. ANd then in script you will find:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200101111512_InitialCreate')
BEGIN
    CREATE TABLE [SomeTable] (
        [Id] uniqueidentifier NOT NULL,
        [StorageDate] datetime2 NOT NULL,
       .....
    );
END;

GO

Thus you are safe to run it against multiple databases.

And then to deploy you can use

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL SqlTask'
  inputs:
    azureSubscription: 'YourSubscription'
    ServerName: 'YourServerName'
    DatabaseName: 'YourDatabaseName'
    SqlUsername: UserName
    SqlPassword: '$(SqlServerPassword)'
    deployType: SqlTask
    SqlFile: '$(System.DefaultWorkingDirectory)/staging/drop/migrations.sql'

Answer 3

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.