[BUG]: SqlAzureDacpacDeploymentV1 does not find correct sqlPackge version

AartBluestoke commented 12 months ago

Task name

SqlAzureDacpacDeploymentV1 specifically, this script: https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlAzureDacpacDeploymentV1/FindSqlPackagePath.ps1

Task version

1.225.1

Environment type (Please select at least one enviroment where you face this issue)

[X] Self-Hosted
[ ] Microsoft Hosted
[ ] VMSS Pool
[ ] Container

Azure DevOps Server type

dev.azure.com (formerly visualstudio.com)

Azure DevOps Server Version (if applicable)

No response

Operation system

windows

Task log

- Check available SqlPackage via dotnet tool
Package Id                Version       Commands  
--------------------------------------------------
microsoft.sqlpackage      162.0.52      sqlpackage

- Check version selected
"C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe" /version
15.0.5015.3
Finishing: Check available sqlPackage version

- actual command logs here:
Starting: GENERATE REPORT - Staging
==============================================================================
Task         : Azure SQL Database deployment
Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
Version      : 1.225.1
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
==============================================================================
Temporary inline SQL file: C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\tmp4282.tmp
Invoke-Sqlcmd -ServerInstance "<my server>" -Database "<myDatabase>" -Username "***"  -Password ******  -Inputfile "C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\tmp4282.tmp"  -ConnectionTimeout 120 ...
"D:\DevOpsAgents\<my agent>\Agent_01\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.225.1\vswhere.exe" -version [15.0,18.0) -latest -format json
"D:\DevOpsAgents\<my agent>\Agent_01\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.225.1\vswhere.exe" -version [15.0,18.0) -products Microsoft.VisualStudio.Product.BuildTools -latest -format json
"C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe" /Action:DeployReport /SourceFile:"..."
Generating report for database '<myDatabase>' on server '<myServer>'.
Time elapsed 0:00:26.21

Relevant log output

- Check available SqlPackage via dotnet tool
Package Id                Version       Commands  
--------------------------------------------------
microsoft.sqlpackage      162.0.52      sqlpackage

- actual command logs here, note it selects the old vs2019 version, not the newer version that dotnet tool shows is installed :
Starting: GENERATE REPORT - Staging
==============================================================================
Task         : Azure SQL Database deployment
Version      : 1.225.1
==============================================================================
"C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe" /Action:DeployReport /SourceFile:"..."

Aditional info

SqlPackage installed by `dotnet tool install --global Microsoft.SqlPackage --version 162.0.52`

as per instructions at: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

AartBluestoke commented 12 months ago

Replicating the issue a test machine with direct execution of the script:

PS > dotnet tool list -g
Package Id                Version       Commands
--------------------------------------------------
microsoft.sqlpackage      162.0.52      sqlpackage

PS > Get-SqlPackageOnTargetMachine
Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine DESKTOP-KUFRT7B

==> Manually navigate to my downloads folder and run the DacFramework.msi to install it.

PS > Get-SqlPackageOnTargetMachine
C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe

PS > &"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /version
162.0.52.1

AartBluestoke commented 12 months ago

the following paths should be searched

OS | Path -- | -- Linux/macOS | $HOME/.dotnet/tools Windows | %USERPROFILE%\\.dotnet\tools

as per the documentation

https://learn.microsoft.com/en-us/dotnet/core/tools/dotnet-tool-install#global-tools

v-mohithgc commented 11 months ago

Hi, what fix do you propose here?

AartBluestoke commented 11 months ago

@v-mohithgc - as per my above comment the above path should also be searched, as part of the 'FindSqlPackagePath' script .

this is the location the SqlPackage is installed if you follow the documented global install process

after dotnet tool install --global Microsoft.SqlPackage --version 162.0.52 the FindSqlPackagePath.ps1 fails to find it -

[debug]Sql Versions installed on machine XX as read from registry: 150

[debug]Dac Framework (installed with SQL) not found on machine XX

[debug]Dac Framework (installed with DAC Framework) not found on machine XX

^-- This message should show the path after dotnet install

[debug]Getting latest Visual Studio 15 setup instance.

[debug]Entering Invoke-VstsTool.

it is easy to reproduce this issue.

download the FindSqlPackagePath.ps1 script and run it (it should fail to find)
run dotnet tool install --global Microsoft.SqlPackage --version 162.0.52 in powershell
run FindSqlPackagePath.ps1 (it should find it, but currently fails to find)
install the MSI installer from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download
run FindSqlPackagePath.ps1 (it will now find it)

github-actions[bot] commented 5 months ago

This issue is stale because it has been open for 180 days with no activity. Remove the stale label or comment on the issue otherwise this will be closed in 5 days

AartBluestoke commented 5 months ago

@v-mohithgc this was tagged with Area:RM 6 months ago, is there a typical time to action those tickets? (commenting to prevent the closure)

AlexisTercero55 commented 4 months ago

Sqlpackage.exe is part of DAC Framework, it comes from a .msi installer. You need to install DAc Framework in your agent in order to use SQL package as CLI from a released pipeline.

Ensure this component at your agent before any sql server deployment e.g. dacpac deployment.

MS DAC Framework

Kind regards

AartBluestoke commented 4 months ago

@AlexisTercero55 - thanks for the comment but a) this is an issue that turns up with azure pipelines, not visual studio. b) represents a config that when run by hand works, c) "There's no SSDT standalone installer for Visual Studio 2022." (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16)

so i can't follow that link to get the latest framework anyway. d) as noted above, you have to follow the "msi installer link" at https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 to find a compatible package. All the other installation mechanisms don't work. e) you probably don't' want a full visual studio 2022 installation on your cloud deployment worker anyway.

AlexisTercero55 commented 4 months ago

@AlexisTercero55 - thanks for the comment but a) this is an issue that turns up with azure pipelines, not visual studio. b) represents a config that when run by hand works, c) "There's no SSDT standalone installer for Visual Studio 2022." (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16)

so i can't follow that link to get the latest framework anyway. d) as noted above, you have to follow the "msi installer link" at https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 to find a compatible package. All the other installation mechanisms don't work. e) you probably don't' want a full visual studio 2022 installation on your cloud deployment worker anyway.

sorry, my bad is this link for the msi instaler: https://aka.ms/dacfx-msi

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16#windows-net-framework

In the other hand you can try commands that release pipelines do to ensure that sqlpackge.exe works:

# example publish from Azure SQL Database using SQL authentication and a connection string
SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Persist Security Info=False;User ID=sqladmin;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Im using the same deployment task at my work:

==============================================================================
Task         : Azure SQL Database deployment
Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
Version      : 1.225.1
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
==============================================================================

AartBluestoke commented 4 months ago

the exact installed and selected versions are quite important when you have plugins for sqlPackage - as there are several binary incompatibility breaks where plugins can only be used with specific versions.

and yes, i know the manual command - i have already noted multiple times "represents a config that works when run by hand"

the problem is the binary incompatability between sql package 19.0 which targets Microsoft.Data.SqlClient, vs all prior versions (which target System.Data.SqlClient) and the "adds support for net 6 as the target framework"

except when you go and install via dotnet tool install you get a version that supports net 6, and is not found via the standard azure pipeline.

I know you can install the MSI installer to get the framework version - i pointed that out in the first comment - https://github.com/microsoft/azure-pipelines-tasks/issues/18913#issuecomment-1704478441

microsoft / azure-pipelines-tasks