Securing Secrets in Azure DevOps


By:   |   Updated: 2020-10-22   |   Comments   |   Related: More > Azure


Problem

In my previous article, Azure DevOps CI/CD using GitHub Repo and Visual Studio Azure SQL Database Project, I demonstrated how to deploy an Azure SQL Database from Visual Studio using Azure DevOps using the Azure SQL Database deployment release task. However, with this process, the SQL Database Username and Password was exposed in the DevOps Azure Pipeline. This can be a concern for a number of reasons including security, compliance, audits and more. How can we secure secrets in Azure DevOps pipelines?

Solution

Azure DevOps tasks are the building blocks for defining automation in pipelines. Tasks are packaged scripts or procedures that have been abstracted with a set of inputs. Often times, these inputs contain sensitive credentials and secrets that need to be appropriately secured. There are a few options for securing secrets in AzureDevOps pipelines including pipeline variables, variable groups, and Azure Key Vault tasks. In this article, I will demonstrate how to implement these options. As a pre-requisite, ensure that you have completed the steps outlined in my previous article, Azure DevOps CI/CD using GitHub Repo and Visual Studio Azure SQL Database Project.

Option 1: Using Pipeline Variables

Pipeline Variables provide a convenient way to get key data elements into various parts of the pipeline. These variables can be used to store key pipeline secret credentials. By defining pipeline variables within the AzureDevOps pipeline, we are exclusively authorizing the associated Azure pipeline to access these secret variables within the tasks of the pipeline. Note that this method of storing and retrieving secrets by using pipeline variables does not utilize or link to Azure Key Vault secrets since the secrets are stored within the AzureDevOps as a pipeline variable.

Let's get started within implementing pipeline variables to store our Azure SQL Database login secret credentials.

To begin, open the Azure SQL Database deployment release pipeline task containing the Login and Password secrets.

ReleaseStage1 CICD Release pipeline stages

Next, click variables to access pipeline variables.

ASqlDacpactask SQL Dacpac task details.

Within the pipeline variables tab, add the administratorLoginUser and administratorLoginPassword and values. Notice the lock icon to the right of the values. Click the lock icon to change the variable type to a secret.

PipelineVariables Admin User login and PW secret value

The secrets are now secured and associated with the release pipeline scope.

ADOSecrets Image of ADO Secrets

Next, within the pipeline task, replace the actual secret values with $(administratorLoginUser) and $(administratorLoginPassword). This parameterizes the values and links it to the secret values defined in the pipeline variables tab.

Click save and then create a new release.

DacpacDetailsOption1 Dacpac deployment details with pipeline variable secrets

As expected, we can see that the database deployment task succeeded which means it was able to successfully utilize the secrets provided in the pipeline variables.

Option1DeploymentSuccess Image of success of the deployment

Upon drilling into the Agent job details, we can see all the detailed steps that have been successfully completed.

Option1AgentJobDetails Image of deployment details for option 1

Now that we have successfully tested this method of securing secrets in the Azure DevOps pipeline let's delete the pipeline variables and save to prepare for the next option.

RemoveSecretVariables remove the secret variables before option2

Option 2: Using Variable Groups

Variable groups are slightly different from pipeline variables in that their scope can be expanded to be shared between pipelines. Additionally, variable groups can be linked to specific stages in the pipeline, build or release along with the capability of linking to secrets withing Azure Key Vault. In short, this option of variable groups is more robust that pipeline variables.

Since we will be linking Key Vault secrets to the variable group we will create, let's start by adding our SQL Database administratorLoginUser and administratorLoginPassword secrets within Azure Key Vault in the portal.

AKVSecrets Image of AKV secrets needed for sql db

Now that we have created the key vault secrets in the portal, lets navigate to ‘Library' within the Azure DevOps project and select the Variable groups tab.

ADOVariableGroups ADO Variable Groups in library

Configure the following Variable group details and ensure to toggle Link secrets from an Azure Key vault as variables. You'll then be prompted to select your Azure subscription and associated key vault name and click Authorize to grant the variable group access to your Azure Key Vault.

VariableGroupsConfig Image of the variable group details for SQL DB.

Next, click Add to add the key vault secrets as variables.

AddVariables Add variables to the variable group details.

When the ‘Choose secrets' UI appears, select the secrets that we created in the key vault and click Ok.

ChooseSecretsOption2 Choose secrets from the variable group AKV connection

Finally, click Save.

SaveVariableGroup Click Save variable group

Next, navigate to the release pipeline and select Variables, variable groups and then select ‘Link variable group'.

LinkVariableGroup1 Link variable group to pipeline

Select the Variable group that we created in the previous steps, associate it with the release scope, and click Link.

LinkVariableGroup2

Confirm that the release pipeline variable group contains the linked SQL DB Credentials and click Save and then create the release.

SaveVariableGroup2 Save the linked variable group

Once the release pipeline successfully completes running, we can confirm that the Variable groups containing the linked key vault secrets authenticated the SQL Server as expected.

ReleaseOption2 Image of pipeline success for Option 2 variable group AKV linked secrets.

From the deployment process Agent job details, we can see that the key vault secrets we downloaded and utilized within the pipeline.

Option2AgentJob Image of Agent job details for option 2

Now that we have successfully tested this method of securing secrets in Azure DevOps, we can unlink the variable group from the release pipeline to prepare for Option 3.

UnlinkVariableGroup 
Unlink Variable group to prep for next option3

Additionally, delete the Variable group from the Azure DevOps Library.

DeleteVariableGroup Delete the variable group to prep for option3

Option 3: Using Azure Key Vault Task

So far, we have tested two methods of securing secrets in Azure DevOps: pipeline variables and variable groups. There is one last option that is left to test and that is the Azure Key Vault Task within the pipeline task.

Let's begin by searching for Azure Key Vault in the task search section.

Next, add the Azure Key Vault task to the release pipeline.

AddAKVTask add akv to the pipeline task.

Ensure that the task is placed before the task requiring the key vault secrets. In this case, we will place the Azure Key Vault task before the Azure SQL Database deployment task containing the SQL secret credentials.

When configuring the Azure Key Vault task there is the option to either enter the desired secrets in the Secrets filter section or leave a * to download all secrets from the Azure Key Vault.

To only retrieve the desired secrets, I have only entered the administratorLoginUser and administratorLoginPassword secrets in the filter section.

Finally, notice the option to ‘Make secrets available to the whole job'. By enable this option, the key vault secrets will be exposed to all subsequent tasks in the job and not just the single task that follows this one. Since we only need this secret for one task in the pipeline, let's leave this unchecked for now.

ConfigureAKVTask Configure the AKV Task

As expected, when the release completes running successfully, we can confirm that the Azure Key Vault Task containing the secret values were successfully authenticated by the Azure SQL Database deployment task.

Option3Release Image of success of Option 3 AKV Task in pipeline

Lastly, let's take a look at the Agent job details to once again confirm that the Azure Key Vault task successfully complete and passed the secret values to the Azure SQL deployment task.

Option3AgentJob Image of details of Agent job for Option 3
Next Steps


Last Updated: 2020-10-22


get scripts

next tip button



About the author
MSSQLTips author Ron L'Esteve Ron L'Esteve is a seasoned Data Architect who holds an MBA and MSF. Ron has over 15 years of consulting experience with Microsoft Business Intelligence, data engineering, emerging cloud and big data technologies.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Continuous database deployments with Azure DevOps

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks














get free sql tips
agree to terms