Latest version of Azure Build Task broken in Azure DevOps

• 
  More info:
  
  SCA version 4.3.20259.22134 works fine.
  
  So the latest version breaks the connection string connecting using Azure DevOps.

  chris_ford2020 September 25, 2020 18:20

  - 0 +
• 
  Also consequently, the Release Task is also broke for same reason.   Had to set specific version there too.

  chris_ford2020 September 25, 2020 18:30

  - 0 +
• 
  This solved my problem with the Build step but the Release step is still giving me trouble.

  Bill_E September 25, 2020 21:34

  - 0 +
• 
  Does it need to be in both places?
  
  
  name: CREATE_TXSURESTAGINGDATABASE_RELEASE_RESOURCES

  displayName: 'Create TxSureStagingDatabase Release Resources'

  enabled: 'true'

  failOnStderr: 'true'

  env:

  SERVER_INSTANCE: '$(serverInstanceNameReleaseTxSureStagingDatabase)'

  DATABASE: '$(databaseNameReleaseTxSureStagingDatabase)'

  USERNAME: '$(DevOps--Release--DatabaseAdminUserName)'

  PASSWORD: '$(DevOps--Release--DatabaseAdminUserPassword)'

  DATABASE_PACKAGE: '$(artifactDatabasePath)\TxSureStagingDatabase.1.0.nupkg'

  EXPORT_PATH: '$(releaseResourcePathTxSureStagingDatabase)'

  requiredVersionOfDlma: specific

  requiredVersion: 4.3.20259.22134

  
  

  - task: RedgateSqlChangeAutomationRelease@4

  # 'SQL Change Automation: Release' task: https://marketplace.visualstudio.com/items?itemName=redgatesoftware.redgateDlmAutomationRelease

  name: DEPLOY_TXSURESTAGINGDATABASE_RELEASE_RESOURCES

  displayName: 'Deploy TxSureStagingDatabase Release Resources'

  enabled: 'true'

  inputs:

  Operation: DeployFromResources

  ImportPath: '$(releaseResourcePathTxSureStagingDatabase)'

  AppendEnvironment: false

  TargetDatabaseServer: '$(serverInstanceNameReleaseTxSureStagingDatabase)'

  TargetDatabaseName: '$(databaseNameReleaseTxSureStagingDatabase)'

  TargetDatabaseUsername: '$(DevOps--Release--DatabaseAdminUserName)'

  TargetDatabasePassword: '$(DevOps--Release--DatabaseAdminUserPassword)'

  requiredVersionOfDlma: specific

  requiredVersion: 4.3.20259.22134
  
  

  Bill_E September 25, 2020 21:36

  - 0 +
• 
  @Bill_E - Yes, has to be in both tasks - Build and Release. That's how I got my pipelines working again.
  
  I left off the last set of digits... I only used this in the required version: 4.3.20259

  chris_ford2020 September 25, 2020 21:40

  - 0 +
• 
  @chris_ford2020 - Mine is failing here with basically the same error.  What you posted earlier fixed my Build step
  
  

  name: CREATE_TXSURESTAGINGDATABASE_RELEASE_RESOURCES

  displayName: 'Create TxSureStagingDatabase Release Resources'

  enabled: 'true'

  failOnStderr: 'true'

  env:

  SERVER_INSTANCE: '$(serverInstanceNameReleaseTxSureStagingDatabase)'

  DATABASE: '$(databaseNameReleaseTxSureStagingDatabase)'

  USERNAME: '$(DevOps--Release--DatabaseAdminUserName)'

  PASSWORD: '$(DevOps--Release--DatabaseAdminUserPassword)'

  DATABASE_PACKAGE: '$(artifactDatabasePath)\TxSureStagingDatabase.1.0.nupkg'

  EXPORT_PATH: '$(releaseResourcePathTxSureStagingDatabase)'

  requiredVersionOfDlma: specific

  requiredVersion: 4.3.20259.22134
  
  

  New-DatabaseReleaseArtifact : An unhandled error occurred:

  RedGate.Versioning.Engine.Shared.DatabaseUtils.Exceptions.DatabaseConnectionException: Error connecting to database: A

  connection was successfully established with the server, but then an error occurred during the login process.

  (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) --->

  Microsoft.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error

  occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an

  authority that is not trusted.) ---> System.ComponentModel.Win32Exception: The certificate chain was issued by an

  authority that is not trusted

  Bill_E September 25, 2020 21:47

  - 0 +
• 
  @chris_ford2020 - Actually, it appears to be erroring on this line.
  
  

  $databaseConnection = New-DatabaseConnection -ServerInstance $env:SERVER_INSTANCE -Database $env:DATABASE -Username $env:USERNAME -Password $env:PASSWORD

  Bill_E September 25, 2020 22:25

  - 0 +
• 
  Hi All,
  
  So this issue has come about due to a recent change to the Microsoft Data Client.
  
  As you're using Azure DevOps, the following is recommended:
  
  1 - Fix the certificate trust issue on the Azure DevOps agent
  2 - Use a Powershell task instead and use the SCA PS cmdlets directly: you will be able to add Trust Server Certificate=True to the connection strings this way)

  DanC September 28, 2020 08:50

  - 0 +
• 
  Hi DanC, unfortunately it is not the Microsoft Data Client that caused the issue.  The issue arose once Red Gate released their September 23rd 2020 update: https://documentation.red-gate.com/sca/release-notes-and-other-versions/powershell-module-release-notes/sql-change-automation-powershell-4-3-release-notes
  
  

  4.3.20267 - September 23, 2020

  Features:

  • Added ability to set Encrypt and TrustServerCertificate connection properties on New-DatabaseConnection cmdlet

  Unfortunately the default Red Gate has chosen isn't working as before when no parameters are passed regarding this new "Feature". I would highly suggest that you make sure the default settings work when no parameters are passed so as to not break everyone else who isn't passing in parameters.  Unfortunately this broke out Azure DevOps deployment and now has our company not wanting to update the component in future releases. In the meantime we are passing as input params for the Azure Devops YAML Tasks:
  
  

  requiredVersionOfDlma: specific

  requiredVersion: 4.3.20259.22134
  
  As far as calling PowerShell directly, we have one step that does that already because we are passing in parameters to the:
  
  

  # New-DatabaseReleaseArtifact

  # More Info: https://documentation.red-gate.com/display/SCA3/New-DatabaseReleaseArtifact

  $databaseReleaseArtifact = New-DatabaseReleaseArtifact -Source $buildDatabaseBuildArtifact -Target $databaseConnection -SqlCmdVariables $sqlcmdVariablesNewDatabaseReleaseArtifact
  
  Before this happens we call:
  
  

  $databaseConnection = New-DatabaseConnection -ServerInstance $env:SERVER_INSTANCE -Database $env:DATABASE -Username $env:USERNAME -Password $env:PASSWORD -TrustServerCertificate $true -Encrypt $false
  
  We had to add -TrustServerCertificate $true -Encrypt $false to make this work.  We didn't have to add those parameters before...hence the bad default settings on the SCA PowerShell modules you guys released.
  
  Please fix your default settings.  This is in a development environment in which we haven't had to worry about this before and now with your change it's something we have to deal with.  I consider it a breaking change that you released and not some minor feature that when releases doesn't break our process.
  
  Thank you for listening to our customer feedback. Also keep in mind that now we have to have a specific version because your Azure DevOps tasks defaults, won't work for us.
  
  As far as calling the PowerShell modules directly, we shouldn't have to do that because you released a "new" feature, and strip us away from using the Azure DevOps SCA tasks.  You can see how this causes us to not want to use or rely on the SCA Azure DevOps tasks.  The real solution here is to fix your default settings. It allows the feature to still be there, and not break people already consuming the SCA Azure DevOps tasks.......
  
  Thank you.

  rbigbie September 28, 2020 16:28

  - 0 +
• 
  Hi @rbigbie
  
  Thank you for your feedback!
  
  So the changes in the release notes were made because of the update Microsoft made to the Data Client, I can tell you a feature request is being looked at to specify the parameters directly in the tasks.
  
  I do apologize for all the inconvenience this has caused!

  DanC October 05, 2020 10:16

  - 0 +
• 
  Hi @rbigbie
  
  Hope you've been well!
  
  The feature for adding more options to the add-ons including Azure DevOps has now been released:
  
  

  • SCA-2664: Added option to encrypt connection
  • SCA-2745: Added option to Trust Server Certificate

  
  https://documentation.red-gate.com/sca/release-notes-and-other-versions/add-on-release-notes/azure-devops-build-add-on-release-notes/azure-devops-build-add-on-4-0-release-notes
  
  Hope this helps!

  DanC October 09, 2020 11:12

  - 0 +

Add comment

Please sign in to leave a comment.