SQL Change Automation 4

Azure DevOps maps all non-secret pipeline variables to environment variables, which causes them to be mapped to SQLCMD variables during deployment and generation of Release Artifact. Thanks to that, they're accessible within SQL scripts of your SQL Change Automation project.

Variables marked as secret are not mapped to environment variables automatically, however. To make them accessible within SQL scripts you'll need to map them to environment variables manually. This is done by adding an "env" section in your YAML file next to the "inputs" section of our Release Task.

When deploying using a Release Artifact, this change needs to be applied to the task creating the Release Artifact (not the one performing deployment).

All environment variables, including manually mapped secret variables, are visible as plain text in the Release Artifact's Targeted Deployment Script (Patch Script).

Example script

Lets assume you already have a valid pipeline generating a Release Artifact, with "MySecretVariable" pipeline variable added as a secret variable. Your Release Artifact creation task would look something like this:

MySecretVariable not available in SQL scripts

- task: RedgateSqlChangeAutomationRelease@4
  inputs:
    Operation: 'Create'
    NuGetFile: '$(SCA.ArtifactsDir)\Database Build Artifact'
    ExportPath: '$(SCA.ArtifactsDir)\Database Release Artifact'
    TargetDatabaseConnectionType: 'explicit'
    TargetDatabaseServer: '$(ProdServer)'
    TargetDatabaseName: '$(ProdDatabase)'
    TargetAuthMethod: 'sqlServerAuth'
    TargetDatabaseUsername: '$(ProdUsername)'
    TargetDatabasePassword: '$(ProdPassword)'

All you need to do is to add an "env" section on the same level as the "input" section of the task. This will map the pipeline variable to environment variable for this task and so SCA will be able to map it to SQLCMD variable for you to use in SQL scripts.

MySecretVariable will be available in SQL scripts

- task: RedgateSqlChangeAutomationRelease@4
  inputs:
    Operation: 'Create'
    NuGetFile: '$(SCA.ArtifactsDir)\Database Build Artifact'
    ExportPath: '$(SCA.ArtifactsDir)\Database Release Artifact'
    TargetDatabaseConnectionType: 'explicit'
    TargetDatabaseServer: '$(ProdServer)'
    TargetDatabaseName: '$(ProdDatabase)'
    TargetAuthMethod: 'sqlServerAuth'
    TargetDatabaseUsername: '$(ProdUsername)'
    TargetDatabasePassword: '$(ProdPassword)'
  env:
    MySecretVariable: $(MySecretVariable)

SQL Change Automation 4

Access Azure DevOps pipeline secret variable from SQL scripts

Example script