sql-server azure-devops pipeline sql-server-express sqlpackage

SQLPackage failing to login on azure DevOps pipeline

Lately (since around 15/02/2024) in around half of the pipeline runs the SQLPackage executable fails to fully connect to the mssqllocaldb server that is started in a previous step. The main error is "No process is on the other end of the pipe".

I am not sure why this started happening or how to prevent it from happening, I also tried connecting with Trusted_Connection=True;TrustServerCertificate=True; without any success;

My solution consists of an SQL Server Database Project and an integration test project (among other things).

The database project has the following after build event:

<PropertyGroup>
  <SQLDeployWorkingDirectory Condition="Exists('C:\Program Files\Microsoft SQL Server\160\DAC\bin\')">C:\Program Files\Microsoft SQL Server\160\DAC\bin\</SQLDeployWorkingDirectory>
</PropertyGroup>
<Exec
  Condition="'$(Configuration)' == 'Release'"
  WorkingDirectory="$(SQLDeployWorkingDirectory)"
  Command="SqlPackage.exe /Action:Publish
    /SourceFile:&quot;$(SolutionDir)$(MSBuildProjectName)\$(OutputPath)$(MSBuildProjectName).dacpac&quot;
    /TargetConnectionString:&quot;Data Source=(localdb)\mssqllocaldb;Initial Catalog=$(MSBuildProjectName).Test; Integrated Security=true;&quot;" />

This is the start of the pipeline configuration file:

trigger:
- main

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'
# Start of versioning variables
  botUserName: 'Azure DevOps pipeline'
  botEmail: 'Azure.Devops@pipeline.com'
  rootBranchName: 'main'
  sourceBranch: $[replace(variables['build.sourceBranch'], 'refs/heads/', '')]
  prBranchName: $[replace(variables['system.pullRequest.sourceBranch'], 'refs/heads/', '')]
  latestVersion: 'v0.1.1'
  needsVersioning: False
  disable.coverage.autogenerate: 'true'
# End of versioning variables

steps:

# Needed for versioning
- checkout: self
  persistCredentials: true

- task: NuGetToolInstaller@1

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: PowerShell@2
  displayName: 'Install .NET Coverage Tool'
  inputs:
    targetType: 'inline'
    script: 'dotnet tool install --global dotnet-coverage'

- task: PowerShell@2
  displayName: 'Start SQL server'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

- task: PowerShell@2
  displayName: 'Info SQL server'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb info mssqllocaldb'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

These are the errors for the VSBuild@1 step:

##[error]*** A connection was successfully established with the server, but then an error occurred during the login process. (provider(0,0): Error : 0 - No process is on the other end of the pipe.)

##[error]Project\Project.sqlproj(190,5): Error MSB3073: The command "SqlPackage.exe /Action:Publish /SourceFile:"D:\a\1\s\Project\bin\Release\Project" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=Project.Test; Integrated Security=true;"" exited with code -1.

##[error]Process 'msbuild.exe' exited with code '1'.

There are seemingly no differences between the "Info SQL Server" steps of a failed and a successful run, example log:

Name:               mssqllocaldb
Version:            15.0.4153.1
Shared name:        
Owner:              fv-az902-225\VssAdministrator
Auto-create:        Yes
State:              Running
Last start time:    2/20/2024 9:30:53 AM
Instance pipe name: np:\\.\pipe\LOCALDB#E9389DB3\tsql\query

Pipeline partial log for VSBuild@1

AfterBuild:
  SqlPackage.exe /Action:Publish /SourceFile:"D:\a\1\s\Project\bin\Release\Project" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=Project.Test; Integrated Security=true;"
  Publishing to database 'Project.Test' on server '(localdb)\mssqllocaldb'.
  Initializing deployment (Start)
##[error]*** A connection was successfully established with the server, but then an error occurred during the login process. (provider(0,0): Error : 0 - No process is on the other end of the pipe.)
*** A connection was successfully established with the server, but then an error occurred during the login process. (provider : Named Pipes Provider, error : 0 - No process is on the other end of the pipe.) [D:\a\1\s\Project\Project.sqlproj]
  Initializing deployment (Complete)
  Analyzing deployment plan (Start)
  Analyzing deployment plan (Complete)
  Updating database (Start)
  Creating database Project.Test...
  Creating Table [dbo].[Table]...
...
 Creating Procedure [dbo].[Procedure]...
  Update complete.
  Updating database (Complete)
  Successfully published database.
  Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection
  Time elapsed 0:01:08.07
##[error]Project\Project.sqlproj(190,5): Error MSB3073: The command "SqlPackage.exe /Action:Publish /SourceFile:"D:\a\1\s\Project\bin\Release\Project.dacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=Project.Test; Integrated Security=true;"" exited with code -1.

Solution

From the YAML you posted, you are running the pipeline job on the Microsoft-hosted agents and try to connect to the automatic instance of LocalDB.

The first time a user on a computer tries to connect to LocalDB, the automatic instance must be both created and started. You might need a few moments to wait for the instance to be created and started.

Each time you run a pipeline on Microsoft-hosted agents, you get a fresh virtual machine for each job in the pipeline. So, each time when you try to connect to the automatic instance of LocalDB on Microsoft-hosted agents, it is your first time on the VM to try the connection.

For your case, you can try the following methods to see whether the issue can be fixed:

After the PowerShell@2 task to run 'sqllocaldb start mssqllocaldb' command and before the VSBuild@1 task, add a step to run the sleep command for a few moments.

- task: PowerShell@2
  displayName: 'Info SQL server'
  inputs:
    targetType: 'inline'
    script: |
      sqllocaldb info mssqllocaldb
      Start-Sleep -Seconds 15

Set the "Number of retries if task failed" on the VSBuild@1 task.

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: 'xxxx'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
  retryCountOnTaskFailure: 5