Azure DevOps - The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider is not valid

Question

I have an Azure VMSS scale set running VMs with the below image...

source_image_reference {
    publisher = "MicrosoftVisualStudio"      
    offer     = "visualstudio2022"           
    sku       = "vs-2022-comm-latest-ws2022" 
    version   = "latest"
  }

There is a custom Power-Shell script that runs each time a VM is started.

The PowerShell script downloads chocolatey and installs the below software and sets the PATH environment variable adding a reference to where 'sqlpackage.exe' is installed on the VM... "C:\Program Files\Microsoft SQL Server\150\DAC\bin" (I have logged on to the VM and checked this is correct)

choco feature enable -n allowGlobalConfirmation

choco install dotnet -y     
choco install sqlpackage -y 
choco install dacfx-18 -y   

#choco install sql2017-dacframework --version 14.0.3757.2 -y

$p = $env:PATH
if (!($p.Contains("C:\Program Files\Microsoft SQL Server\150\DAC\bin"))) { 
  
  Write-Host "`n****************** Debug: Appending to Start (C:\Program Files\Microsoft SQL Server\150\DAC\bin) to Env:PATH"  
  #$env:PATH = "C:\Program Files\Microsoft SQL Server\150\DAC\bin;$p"

  $Path = "C:\Program Files\Microsoft SQL Server\150\DAC\bin"
 
  $Path = $Path + [IO.Path]::PathSeparator + [Environment]::GetEnvironmentVariable("PATH", "Machine") 
  
  [Environment]::SetEnvironmentVariable( "Path", $Path, "Machine" )


}
else
{
    Write-Host "`n****************** Debug: NOT Appending (C:\Program Files\Microsoft SQL Server\150\DAC\bin) to Env:PATH"  
}

$p = "'/Directory:c:\agentdevops1 /Token:$pat_token /Pool:selfhosted-vmss-windows-lds-$env /Url:$url'"
Write-Host "Param $p"

Write-Host "`n****************** Debug Install: azure-pipelines-agent"
choco install azure-pipelines-agent --force --params "$p"

The VMs in the VMSS act as self-hosted VMS each with their own 'azure devOps agent' linking to Azure Build and Release pipelines.

The Azure Build and Release pipelines are carrying out DAC-PAC tasks.The Az Service-Principal that is linked to the 'pipeline' has database 'owner' permisisons.

Where the DAC-PAC task is run against an 'Azure SQL Database' it completes successfully.

Where the DAC-PAC Publish task is run against an 'Azure Synapse Database' it FAILS with the below error.

2023-11-26T09:39:21.4095525Z "C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"C:\agentdevops1\_work\r1\a\_SQL\DACPAC-Registrar\Registrar.dacpac" /TargetServerName:""isl-XXX-XXX-sqlsrv01.database.windows.net"" /TargetDatabaseName:"Registrar" /AccessToken:"********" /v:AzureDataLakeStorage="islXXXXXXXdatalakesa"
2023-11-26T09:39:21.6055840Z Publishing to database 'Registrar' on server 'isl-XXX-XXX-sqlsrv01.database.windows.net'.
2023-11-26T09:39:22.0702915Z Initializing deployment (Start)
2023-11-26T09:39:22.3009340Z Initializing deployment (Failed)
2023-11-26T09:39:22.4995063Z ##[error]*** Could not deploy package.
2023-11-26T09:39:22.5079532Z ##[error]Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.
2023-11-26T09:39:22.5478215Z ##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2023-11-26T09:39:22.5776187Z ##[section]Finishing: Azure SQL DacpacTask: Registrar

The below choco commands install the 'sqlpackage' and Dac-Framework on the VM before any operations are executed by the devOps pipeline. Note: 'sqlpackage.exe' is the main software required.

choco install dotnet -y     
 choco install sqlpackage -y 
 choco install dacfx-18 -y

The error maybe that the wrong version of one of the above is being installed.

"Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider is not valid."

Any thoughts or suggestions on how to fix? (NB: I can't use Windows Hosted VMs in the Azure devOps pipelines)

Answer 1

What worked for me. In my custom Powershell script that runs when a VM from the VMSS is started...

use evergreen or specific dacfx msi link below

    wget -O DacFramework.msi "https://aka.ms/dacfx-msi"
    msiexec.exe /i "DacFramework.msi" /qn

Also added the below location to the PATH env. variable via the Powershell script...

"C:\Program Files\Microsoft SQL Server\160\DAC\bin"