Recently I had to execute a SQL script inside an Azure DevOps Pipeline. This is not a very common task as part of a build pipeline, however it is something that can be done and a useful technique. This could be useful in a timed job to update a database, or inside a pipeline to store extra details.

In this post, we will look at how to use the AzurePowerShell task to execute the Invoke-Sqlcmd command to run SQL in the context of an Azure DevOps pipeline.

Configuration

Before we can run a script there is some configuration that needs to be done in Azure DevOps, Azure SQL Database and the SQL Server.

DevOps Task

Under the old Classic Build Pipelines there was previously a sql task, under YAML pipelines this is not available however. Instead we can use the AzurePowerShell task and a powershell script to do the same.

- task: AzurePowerShell@5
  inputs:
    azureSubscription: 'SqlDatabaseServiceConnection'
    scriptType: 'FilePath'
    scriptPath: 'WriteMetrics.ps1'
    azurePowerShellVersion: 'LatestVersion'

For this to work you will need to set up a service connection in Azure DevOps to connect to the Azure SQL Database. I have a full video on how to configure a service connection here. The name of the service connection is then used in the azureSubscription parameter of the AzurePowerShell task above.

RBAC Permissions

Once you have created the service connection you will then need to assign the additional Azure AD Role SQL DB Contributor on the database server to the service principal/managed identity used by the service connection.

SQL Permissions

Inside the database you will then need to create a user with the same name as the managed identity attached to the service connection and assign the SQL db_datawriter role.

CREATE USER [ManagedIdentityName] FROM EXTERNAL PROVIDER
ALTER ROLE db_datawriter ADD MEMBER [ManagedIdentityName]

Firewall Permissions

Finally you will need to select “Allow Azure services and resources to access this server” in the firewall settings of the Azure SQL Database server. Alternatively you can add the IP address of your build agent.

PowerShell

If you are using a Microsoft hosted agent you will need to install the SqlServer module at the start of the script. The code is a little longer than you may expect, however this is because without the extra code you will see an issue where we get the error “The term ‘Install-Module’ is not recognized”.

Write-Host "##[group]Install PS modules"

Get-Module -Name PowerShellGet -ListAvailable
Get-Module -Name Microsoft.PowerShell.PSResourceGet -ListAvailable

Install-Module -Name SqlServer -Force -Scope CurrentUser -SkipPublisherCheck
Import-Module SqlServer

Write-Host "##[endgroup]"

Now we are ready to write the code to execute the SQL script.

$tokenResponse = Get-AzAccessToken -ResourceUrl https://database.windows.net
$dbToken = $tokenResponse.Token

$dbServer = 'database-server.database.windows.net'
$databaseName = 'db-metrics'

$metricValue = 100

$insertQuery = "INSERT INTO Metrics (`
CreatedDate,
MetricValue  `
) VALUES ( `
GetDate(),  `
$metricValue `
)"

Invoke-Sqlcmd -ServerInstance $dbServer -AccessToken "$dbToken" -Database $databaseName -Query $insertQuery

Warnings

When you run the script you may see the following warning:

WARNING: Upcoming breaking changes in the cmdlet 'Get-AzAccessToken' :
The Token property of the output type will be changed from String to SecureString. Add the [-AsSecureString] switch to avoid the impact of this upcoming breaking change.
- The change is expected to take effect in Az version : '13.0.0'
- The change is expected to take effect in Az.Accounts version : '4.0.0'

This warning is because the token is not a secure string and we should not be reading the access token as a plain text string. The issue we have however, is that the Invoke-Sqlcmd command does not yet accept a secure string.

If you wish to look at some options to mitigate this there is a full discussion thread on the topic

Summary

In this short post, we have seen how to execute a SQL script against an Azure SQL Database from an Azure DevOps Pipeline. By using a Powershell task and a service connection we can execute a query to either insert or update data in the database.

Title Photo by Caspar Camille Rubin on Unsplash