Keep your Azure Synapse secrets secret in Azure DevOps

Reading Time: 5 minutes

In this post I want to cover how you can keep your Azure Synapse secrets secret in Azure DevOps. Because you need to do this if you are working with production deployments.

With this in mind, I want to raise more awareness about it and make sure others avoid putting secrets directly in their pipelines like in the below example.

To clarify, when I say Azure Synapse I mean Azure Synapse Analytics.

In reality, you can apply the contents of this post to other services that you deploy to using the Azure Pipelines service within Azure DevOps. Such as Azure SQL Database and other cloud-based services.

In fact, the ‘Azure SQL Database deployment’ task I use in this example is the same one you can use to deploy state-based updates to Azure SQL Database.

By the end of this post, you will know various ways to keep your Azure Synapse secrets secret within Azure DevOps. To summarize, this post covers the three methods below:

1. Creating variables within a pipeline.
2. Creating a variable group.
3. Referencing an Azure Key Vault directly within a pipeline.

Why keep secrets secret

You should keep your secrets secret to avoid exposing sensitive details about your Azure services. If you are fairly new to creating pipelines it can be easy to expose these details and I want to make sure you avoid that.

For example, say you were working with a new YAML pipeline in Azure Pipelines. Similar to the one that I covered in a previous post about creating a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps.

From there you decided to add a new Azure SQL Database deployment task as below.

From there, you type in all the connection details and then click the blue Add button. Once you have done that the task appears in your pipeline with all the information that you added like in the below example.

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'YOUR SELECTED SUBSCRIPTION'
    AuthenticationType: 'server'
    ServerName: '{Your dedicated SQL endpoint}'
    DatabaseName: 'ProductionSQLPool'
    SqlUsername: 'madeupsqladminaccount'
    SqlPassword: 'D0N0t3nteraP@$$w0rdh3r3'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: 'mydacpac.dacpac'
    IpDetectionMethod: 'AutoDetect'

From there you decide to test it works by clicking Save and run. Once you have done that your YAML file is committed (saved) to your repository along with any sensitive information you have entered.

Which means that even if you change this in your pipeline and commit the change those details are still in the history of your Git repository. You will have to resolve this another way.

If it is a repository that you share the situation can be made worse as well. Because your repository can end up being shared either within your organization or publicly. Along with the sensitive details you entered.

How to keep Azure Synapse secrets secret

In reality, there are multiple ways to keep your Azure Synapse secrets secret in Azure DevOps. Most popular methods involve using variables which can be configured in multiple places within Azure Pipelines.

Create variable in a pipeline

For instance, if you are using the GUI-based Releases feature in Azure Pipelines you can create your own variables for use just for that release. Which you can set to be plain text or masked as a secret. You can create these variables at either the pipeline or stage level.

You can also add your own variables for use within a YAML pipeline as well by clicking on the variables button in the top right-hand corner whilst you are editing a pipeline.

Once added, you can then call these variables for use. Like in the below example which shows a task to deploy to a dedicated SQL Pool:

                  - task: SqlAzureDacpacDeployment@1
                    displayName: 'Update dedicated SQL Pool'
                    inputs:
                      azureSubscription: $(azuresubscription)
                      AuthenticationType: 'server'
                      ServerName: $(SQLEndpoint)
                      DatabaseName: '$(database)'
                      SqlUsername: '$(sqluser)'
                      SqlPassword: '$(sqlpw)'
                      deployType: 'DacpacTask'
                      DeploymentAction: 'Publish'
                      DacpacFile: '$(SQLPooldacpacfile)'

However, creating variables just for that deployment is not very practical if you need the same secret for other deployments. Which is where variable groups prove to be very handy.

Keeping secrets in variable groups

Basically, variable groups are where you can create one or more secrets within a logical group. You can then reference these groups in either the GUI-based Releases pipeline or inside a YAML pipeline. Afterwards, you can use the variables inside them just like in the above example.

You can create variable groups within the Library feature in Azure Pipelines.

There are two ways you can enter variables into variable groups.

First way is to create your own variables in a variable group. Doing this allows you to enter variables which can be viewed in the library as plain text or masked as a secret.

Second method is where you link a library to an existing Azure Key Vault. From there, you can cherry pick secrets that are already created in your Azure Key Vault service that you want in your variable group. Like in the below example which references a Key Vault for a serverless SQL Pool.

I must admit I prefer this method. Especially since a lot of companies expect you to use secrets that are stored in Azure Key Vault.

After you have created and saved your variable group you can easily link it to your release pipeline as below.

You can add it to a YAML pipeline using within the variables section like below:

variables:
- group: DedicatedSQLPoolTest

You can read more about this in the official ‘Add & use variable groups‘ page from Microsoft.

Using Azure Key Vault task to get secrets

Final method I want to cover is referencing an Azure Key Vault directly within an Azure Pipeline to get secrets from it. You do this using an Azure Key Vault task. Afterwards, you can call the secrets as variables like in the previous example.

I recommend creating a service connection to your Azure account if you decide to implement this. So that you can connect using your service connection name instead of revealing your Azure subscription.

This method can be useful if you want to use a YAML pipeline as a template for others to copy in different organizations. However, bear in mind that if you use this method your Key Vault name can be exposed in the pipeline.

Microsoft provide further details about how you can use Azure Key Vault secrets in Azure Pipelines.

Final words about keeping your Azure Synapse secrets secret

I really hope that some of you find this post about keeping your Azure Synapse secrets secret in Azure DevOps useful.

Because you really need to do this if you want to deploy Azure Synapse objects to a production environment. Plus, it is good to get into the habit of keeping secrets out of your pipeline code. There can be serious repercussions if you do not.

Of course, if you have any comments or queries about this post feel free to reach out to me.