Securely configuring Azure DevOps pipeline for SQL unit testing
Introduction
If you are aiming to securely configure your database unit tests in the Azure DevOps pipeline, the first challenge you might need to address is how to configure your connection strings. Unfortunately, no clear documentation explains 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 the Azure DevOps pipeline.
Pre-requisites
You should have a basic idea of creating SQL Unit tests using Visual Studio, configuring build and releases in Azure DevOps, and configuring Azure Key Vaults in the Azure portal.
Possible alternatives
We will explore few possible alternatives to configure your connection string in the Azure DevOps pipeline:
- Using user-defined secret variable
- Using Azure Key Vault task
- Using variable group
Alternate 1: Using user-defined secret variable
You can use a user-defined secret variables for a variety of purposes in the 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 strings in the Azure DevOps pipeline. Your connection string in the SQL test project would be something like this:
Note: For a 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 store 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 is the 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 of 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 the Azure DevOps pipeline to replace such tokens. Instead ‘Replace Tokens’ is a free tool available in the Azure marketplace 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 the 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. The final release pipeline will look like:
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. The ‘Authorize’ button will pop up when you first time configuring this task which will add the necessary permissions for the release principal on Azure Key Vault you want to access. This task will automatically produce 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 the 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 the previous section. The final release pipeline will look like:
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 the Azure Keyvault 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 is the 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 for your release.
iii. Add the ‘Replace Token’ task in your release pipeline
You can just follow the step ‘Add ‘Replace Token’ task in your release pipeline’ in the previous section. The final release pipeline will look like:
Debug Note: If you found to ‘Replace Tokens’ task failed with the message ‘##[warning]variable not found’, then recheck that you have the build variable added with an empty value.
There is an open issue with this approach where the configuration file is copied into the pipeline agent where it contains the connection string. I will blog about this when the problem is better handled.
Conclusion
We have three alternative options to securely configure database unit tests in the 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 a suitable alternative based on your need.
