Securely configuring Azure DevOps pipeline for SQL unit testing

Introduction

If you are aiming to securely configure your database unit tests in Azure DevOps pipeline, first challenge you might need to address is about how to configure your connection strings. Unfortunately no clear documentation explains about the best practices around this area. The aim of this article is to cover the possibilities to configure your connection strings securely. The aim is to get rid of the connection strings stored in the source code but configuring those securely in Azure DevOps pipeline.

Pre-requisites

You should have basic idea of creating SQL Unit tests using Visual Studio, configuring build and releases in Azure DevOps and configuring Azure Key Vaults in azure portal.

Possible alternatives

We will explore few possible alternatives to configure your connection string in Azure DevOps pipeline:

  1. Using user defined secret variable
  2. Using Azure Key Vault task
  3. Using variable group

Alternate 1: Using user defined secret variable

You can use user defined secret variable for variety of purposes in build and release pipeline to store sensitive information. You can store your database connection string in a secret variable while configuring your DevOps pipeline. The steps needed are listed below.

i. Configure connection string in app.config

Connection strings are used for ExecutionContext and PrivilegedContext in a database test project. To avoid storing sensitive information in a connection string, use tokens in app.config in place of real connection strings. These tokens then replaced with real connection string in the Azure DevOps pipeline. Your connection string in the SQL test project would be something like this:

app.config of Database unit test project

Note: For simple explanation, added the same connection string for both Privileged and Execution contexts. But in reality those would be different with different user privileges.

ii. Configure connection string value in build pipeline secret variable

We are going to use custom build variables to stored the real connection string.

Go to your Azure DevOps portal and edit your database release definition:

Add a release pipeline variable in your release definition. The name of the variable as same as what we defined in app.config without prefix (#{) and suffix (}#). In our case, the variable name is SQLDBTestConnectionString. Ensure to lock the values to keep the value secret. Also select the scope to your release.

iii. Add ‘Replace Token’ task in your release pipeline

At the time of writing this article, there are no out of the box pipeline tasks in Azure DevOps pipeline to replace such tokens. Instead ‘Replace Tokens’ is a free tool available in Azure market place to satisfy the need. Add this task and specify your release directory and application configuration file of the db project.

This step will make the token specified for connection string in the application configuration file replaced with the value specified in the pipeline variable.

Now you should be ready to configure your DB tests. Final release pipeline will looks like:

Release pipeline

Alternate 2: Using Azure Key Vault task

i. Add your connection string in Azure Key Vault

Go to your Azure portal and open the Key Vault

Select ‘Secrets’ and ‘Generate/Import’

Add a secret in the name how we configured in the app.config.

ii. Add ‘Azure Key Vault’ task in the release pipeline

Add ‘Azure Key Vault’ task next to ‘Azure SQL Database Deployment’ task. ‘Authorize’ button will pops up when you first time configuring this task which will add necessary permissions for the release principal on Azure Key Vault you want to access. This task will automatically produces a build variable dynamically based on the secret filters you applied and the same is available for subsequent tasks in the release pipeline. In our case ‘Replace Tokens’ will replace the connection strings in the application configuration files of DB unit test project.

iii. Add ‘Replace Token’ task in your release pipeline

You can just follow the step ‘Add ‘Replace Token’ task in your release pipeline’ in previous section. Final release pipeline will looks like:

Release pipeline

Alternate 3: Using variable group

i. Define a build variable group

Go to your release pipeline, select ‘Variables’ and ‘Variable groups’ and then ‘Manage variable groups’

Select ‘+Variable group’ to add a variable group

In the ‘Variable group’ page, select the ‘Link secrets from an Azure key vault as variables’ option. Choose the azure subscription and authorize, you need to choose ‘Authorize’ when you first time configuring this step. Select the key vault which has the connection string value. In the variables section, click ‘Add’

Choose the secret you configured in azure key vault for the connection string and add.

ii. Add release pipeline variable

Add a release pipeline variable in your release definition. The name of the variable as same as what we defined in app.config without prefix (#{) and suffix (}#). In our case, the variable name is SQLDBTestConnectionString. We are not going to set the actual connection string in this case. Instead, add a space in the value. Select the scope to your release.

iii. Add ‘Replace Token’ task in your release pipeline

You can just follow the step ‘Add ‘Replace Token’ task in your release pipeline’ in previous section. Final release pipeline will looks like:

Debug Note: If you found to ‘Replace Tokens’ task failed with message ‘##[warning]variable not found’, then recheck that you have the build variable added with a empty value.

Conclusion

We have three alternative options to securely configure database unit tests in Azure DevOps release pipeline. You can apply the same configurations for other automatic tests in the build or release pipeline where you want to manage sensitive information. Choose the suitable alternative based on your need.