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?

Tiamo Idzenga · Accepted Answer · 2020-10-21T15:17:54.303

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:

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

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)

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.

This really worked well for me. The only issue I had was that the job variables would prefix the Env variables with a space, so in the dotnet app, I retrieved the variables manually and trimmed them. — DigiBanks99, Jun 17 '21 at 08:58
Thanks so much for this! I originally had my `dotnet ef migrations script` command working within an Azure CLI task, but it suddenly started failing last week. This fixed it - I also ran into the spacing issue, but adding `.Trim()` to the PowerShell lines fixed that. — CaptainStealthy, Jul 19 '21 at 23:12
I also marked the job vars as secret, so they would be suppressed in any console/log output. — CaptainStealthy, Jul 19 '21 at 23:13

Krzysztof Madej · Answer 2 · 2020-11-03T02:34:09.050

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'

This brings me to a follow-up question. Since the ConnectionString comes from an Azure Key Vault the application adds this Key Vault in Program.cs and uses the DefaultAzureCredential class from Azure.Identity to authenticate itself and build Configuration from the Key Vault. Locally and after deployment to Azure this goes right. In the Azure Pipelines however it cannot get a token. How do I authenticate Azure Pipelines in this way? Do I az login in a pipeline? That seems unintended. — Tiamo Idzenga, Oct 21 '20 at 12:19
Maybe this will help for this issue https://stackoverflow.com/questions/62817337/azure-keyvault-azure-identity-credentialunavailableexception-defaultazurecrede — Krzysztof Madej, Oct 21 '20 at 12:33
I don't believe so. Locally and after deployment this works fine. There is no EnvironmentCredential configured. Locally I use the VisualStudioTokenProvider and after deployment I use the ManagedIdentityCredential.. The key vault uses RBAC. — Tiamo Idzenga, Oct 21 '20 at 12:36
I have finally figured out the solution to the problem in my edit. :-) I will answer my own question now. — Tiamo Idzenga, Oct 21 '20 at 15:04
So it works when you set these env variables mentioed in the above topic. Super cool! — Krzysztof Madej, Oct 21 '20 at 15:58

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

2 Answers2