dotnet ef migrations script fails in CI but works fine locally

Question

Problem Background:

I have a class library project that contains the database migrations (MyProject.MigrationProject.csproj). And in startup.cs of the entry project (Web API), I have explicitly included migration assembly like following.

 services.AddDbContext<ApplicationDbContext>(options =>
   options.UseSqlServer(
      configuration.GetConnectionString("DefaultConnection"),
      x => x.MigrationsAssembly("MyProject.MigrationProject")));

Then I run the dotnet ef migration command locally using powershell. The command I'm using is:

dotnet ef migrations script --no-build -o D:\migrations\script.sql --idempotent --project D:\...\src\MyProject.MigrationProject\MyProject.MigrationProject.csproj --startup-project D:\...\src\MyProject.WebApi\MyProject.WebApi.csproj

The above command executes successfully on my machine and creates the desired script.sql file on output location. The same command is then used in the build pipeline (using command line task) in Azure Devops but for some reason it fails there. The command on Devops looks like this:

dotnet ef migrations script --no-build -o $(Build.ArtifactStagingDirectory)\migrations\script.sql --idempotent --project $(Build.SourcesDirectory)\src\MyProject.MigrationProject\MyProject.MigrationProject.csproj --startup-project $(Build.SourcesDirectory)\src\MyProject.WebApi\MyProject.WebApi.csproj

The Error I get from Devops:

Script contents:
dotnet ef migrations script --no-build -o D:\a\1\a\migrations\script.sql --idempotent --project D:\a\1\s\src\MyProject.MigrationProject\MyProject.MigrationProject.csproj --startup-project D:\a\1\s\src\MyProject.WebApi\MyProject.WebApi.csproj
##[debug]AGENT_VERSION: '2.193.1'
##[debug]AGENT_TEMPDIRECTORY: 'D:\a\_temp'
##[debug]Asserting container path exists: 'D:\a\_temp'
##[debug]Asserting leaf path exists: 'C:\Windows\system32\cmd.exe'
========================== Starting Command Output ===========================
##[debug]Entering Invoke-VstsTool.
##[debug] Arguments: '/D /E:ON /V:OFF /S /C "CALL "D:\a\_temp\37fc4a71-a144-4332-9a84-04e6138a2538.cmd""'
##[debug] FileName: 'C:\Windows\system32\cmd.exe'
##[debug] WorkingDirectory: 'D:\a\1\s'
"C:\Windows\system32\cmd.exe" /D /E:ON /V:OFF /S /C "CALL "D:\a\_temp\37fc4a71-a144-4332-9a84-04e6138a2538.cmd""

An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the applicgation service provider. Error: A certificate with the thumbprint 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' could not be found.
Unable to create an object of type 'ApplicationDbContext'. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

##[debug]Exit code: 1
##[debug]Leaving Invoke-VstsTool.
##[error]Cmd.exe exited with code '1'.
##[debug]Processed: ##vso[task.logissue type=error]Cmd.exe exited with code '1'.
##[debug]Processed: ##vso[task.complete result=Failed]Error detected
##[debug]Leaving D:\a\_tasks\CmdLine_d9bafed4-0b18-4f58-968d-86655b4d2ce9\2.182.0\cmdline.ps1.
Finishing: CmdLine

At times, by tweaking the YAML file, I was able to get rid of the first error but the 2nd one never disappeared on devops. The issue is pretty much because of having separate project for Migrations but I think that's how it should be...

My Build pipline's YAML:

trigger:
- develop

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: DotNetCoreCLI@2
  displayName: Restore
  inputs:
    command: restore
    projects: '**/MyProject.WebApi.csproj'

- task: DotNetCoreCLI@2
  displayName: Build
  inputs:
    projects: '**/MyProject.WebApi.csproj'
    arguments: '--no-restore'

- task: DotNetCoreCLI@2
  displayName: Test
  inputs:
    command: test
    projects: '**/*[Tt]ests/*.csproj'
    arguments: '--no-restore --no-build'

- task: DotNetCoreCLI@2
  displayName: 'Publish WebApi'
  inputs:
    command: publish
    publishWebProjects: false
    projects: '**/MyProject.WebApi.csproj'
    arguments: '--configuration $(BuildConfiguration) --output $(Build.ArtifactStagingDirectory) --runtime -r $(runtime)'

- task: CopyFiles@2
  inputs:
    Contents: '**'
    TargetFolder: '$(Build.ArtifactStagingDirectory)'

- task: UseDotNet@2
  inputs:
    packageType: 'sdk'
    version: '5.x'

- task: DotNetCoreCLI@2
  displayName: Install dotnet-ef
  inputs:
    command: 'custom'
    custom: 'tool'
    arguments: 'install --global dotnet-ef --version 5.0.10 --ignore-failed-sources'

- task: CmdLine@2
  inputs:
    script: dotnet ef migrations script --no-build -o $(Build.ArtifactStagingDirectory)\migrations\script.sql --idempotent --project $(Build.SourcesDirectory)\src\MyProject.MigrationProject\MyProject.MigrationProject.csproj --startup-project $(Build.SourcesDirectory)\src\MyProject.WebApi\MyProject.WebApi.csproj

- task: PublishBuildArtifacts@1
  displayName: 'Publish Artifact: drop'

My Suspicion:

It could be an issue with the directory where the command is executed from (on ADO powershell). I suspect this because, on my local machine, before calling the method x.MigrationsAssembly("MyProject.MigrationProject"), the following command failed when I executed it from a directory other than the entry project's directory but when I navigated the powershell to entry project and executed the same command, it went successful. The command at that time was:

dotnet ef migrations script -o D:\migrations\script.sql --idempotent --project D:\...\src\MyProject.MigrationProject\MyProject.MigrationProject.csproj

I'm already using the same YAML in another project but that contains everything in single Web API project and so, I do not get any issue there.

Question:

What am I doing wrong here? What can I do to fix this issue? Any help would be appreciated.

Project Details

DotNet 5.0

EntityFramewokCore 5.0.10

Visual Studio 2019

If I'm missing anything, please ask.

Updates:

My suspicion about working directory for executing the dotnet ef command appears to be wrong as I tried that by supplying workingDirectory parameter to the command line tasks. It works on local machine though.

Answer 1

Thanks @jane-ma-msft

The Error message shows that it is a Certificate error. Please follow the work around to fix the issue.

1. Try to generate a new certificate or cancel certificate validation.

2. Check your .sln file. If it has PackageCertificateKeyFile & PackageCertificateThumbprint please try to remove the properties and restart a pipeline.

   Or Check that it is configured correctly and that you have uploaded the correct certificate file to the appropriate path.

3. Make sure that the agent windows-latest has all the .NET SDK versions you need and all the software your project needs to reference. If not, use the task or command line to download them. Click this link to view the software installed on the windows-latest agent.

4. If you are using the Microsoft-hosted Windows agents in your pipeline, please try to use the self-hosted Agent in your pipeline. Click this document for detailed steps.

Refer here Link 1 & Link 2