Latest version of Azure Build Task broken in Azure DevOps

chris_ford2020

Starting on 9/23 - our build pipeline task started failing with this error:

DbCleanTask: Warning (0,0): Unable to connect to [<DBNameOmitted>] in order to perform shadow clean:

##[warning] 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.): 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.)

To work around the issue:
- Edit Pipeline, change Build task from "Use Latest Version of SCA" to "Use specific Version"
- Set the specific version to 4.2.20176

Usually, this happens when the SSL Encrypt is true in the connection string, but I have no control over editing those options on how it connects from Azure. Normally you would also include the Trust Server Certificate option as well. That's how this error normally occurs.

chris_ford2020

More info:

SCA version 4.3.20259.22134 works fine.

So the latest version breaks the connection string connecting using Azure DevOps.

chris_ford2020

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

Bill_E

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

Bill_E

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

chris_ford2020

@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

Bill_E

@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

@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

DanC

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)

rbigbie

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.

DanC

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

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!