search

microsoft / azure-pipelines-tasks

Tasks for Azure Pipelines
https://aka.ms/tfbuild
MIT License
3.47k stars 2.6k forks source link

Visual Studio Versions of DAC take precedence over SQL Versions #8194

Closed ld0614 closed 3 years ago

ld0614 commented 6 years ago

Troubleshooting

Checkout how to troubleshoot failures and collect debug logs: https://docs.microsoft.com/en-us/vsts/build-release/actions/troubleshooting

Environment

Issue Description

Publish of DACPAC file works on the first attempt Publish of DACPAC file works on the second attempt Select columns are then encrypted via a PowerShell scipt to enable Always on Encryption. Data is added to the tables Publish of DACPAC files fails with the below error.

This can be replicated with Visual Studio 15.8 locally, this returns the error: Cannot proceed as Key Vault support is not present in the current application. For Key Vault support during deployment, install DacFramework.msi and run SqlPackage.exe from its install location.

Error logs

2018-08-29T11:07:41.5950873Z *** Failed to decrypt column 'Password'. 2018-08-29T11:07:41.5959353Z Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.

I believe that this is happing as the newly updated hosted agent has a newer version of the DAC Framework which is taking precedence over the full version which has been installed separately. Looking back in my release log history it appears that a week or so ago I was using the agent hosted at C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe rather than at C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe

As there are two different versions with different functionality it would be useful if the tasks had the option to select which version of the tool to use (like how the Azure Powershell task works). Alternatively is there a good way to programmatically locate the correct version of the sqlpackage.exe file so that I can write a script which does this for me?

Ajay-MS commented 6 years ago

@ld0614

In order to select SQLPackage.exe for the deployment,

Goal

To achieve deployment with the latest version of SQLPackage.exe. So that user can take advantage of features that have been added to latest version of SQLPackage.exe

Search in DAC under the following software

Algorithm

  1. If any of above-mentioned software have SQLPackage.exe with latest major version, it will be selected.
  2. If there is a conflict between the latest major version, preference is given as follow
    1. Data-Tier application framework
    2. Visual Studio
    3. SQL server

Higher preference is given to Data-Tier application framework because it's expected to get the latest version sooner.

In order to add the feature to task for an input to select across different version of SQLPackage.exe, I would like to understand your scenario furthermore.

Can you please share your email id so that we can arrange a call to discuss on this.

ld0614 commented 6 years ago

Hi @Ajay-MS thanks for reaching out, my email address is leo.darcy@outlook.com

bojingo commented 5 years ago

Please provide a way to select which version of the DAC tools to use. I have a custom DeploymentPlanModifier that requires the "140" version of the tools. Unlike the original poster, we use private agents. Something on half of my private agent VMs got updated that put the "150" DAC tools on them but not the other half (no one on the team knows what install/update of what caused this, as all VMs are maintained consistently as far as we can tell - annoying but beside the point) and those VMs were no longer able to deploy the database. What's worse, it took several days to figure this out as the root cause for the deployment failures because of the terribly unspecific and unhelpful error message emitted by the VSTS task (SqlPackage.exe may be to blame, but I'm complaining here, anyway!)

*** An error occurred during deployment plan generation. Deployment cannot continue.
Error SQL0: Required contributor with id 'XXX' could not be loaded.
Contributor initialization 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-

The VSTS task should have a "DAC tools compatibility level" field, or similar:

That way, I can select "140" and regardless of what additional versions end up on the agents, I have predictability and reliability in my deployments.

(FYI, there doesn't seem to be an updated version of the Microsoft.SqlServer.DacFx.x86 package for version 150, so there really is an urgent need for this)

Lacking this has wasted a ton of productivity time for our team.

Ajay-MS commented 5 years ago

@bojingo

Apologize for the issue that you have encountered.

We are planning to address your issue by introducing two combos in the task for selecting

It could take a little time from our end.

If you would like to contribute to this task sooner, please feel free to raise PR. External contributions are highly appreciated.

nishanperera commented 3 years ago

@Ajay-MS Any updates on this issue?

kmkumaran commented 3 years ago

@nadesu / @chshrikh to help you further on this.

dsusca commented 3 years ago

Any Update ? 12/28/2020 exactly the same problem.

github-actions[bot] commented 3 years 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