What are the challenges you face when working across database platforms? Take the survey
Options

Latest version of Azure Build Task broken in Azure DevOps

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.
Tagged:

Answers

  • Options
    More info:

    SCA version 4.3.20259.22134 works fine.

    So the latest version breaks the connection string connecting using Azure DevOps.
  • Options
    Also consequently, the Release Task is also broke for same reason. =)  Had to set specific version there too.
  • Options
    Bill_EBill_E Posts: 4 New member
    This solved my problem with the Build step but the Release step is still giving me trouble.
  • Options
    Bill_EBill_E Posts: 4 New member
    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
    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

  • Options
    chris_ford2020chris_ford2020 Posts: 9 Bronze 1
    edited September 25, 2020 9:41PM
    @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
  • Options
    Bill_EBill_E Posts: 4 New member
    @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
  • Options
    Bill_EBill_E Posts: 4 New member
    @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
  • Options
    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)

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    rbigbierbigbie Posts: 16 Bronze 2
    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
    $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.
  • Options
    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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

Sign In or Register to comment.