Executing SQL queries from Azure DevOps using Service Connection credentials

Executing SQL queries on Azure SQL Database using Azure Devops can be a complex and challenging task, particularly when it comes to establishing a secure and reliable connection. This option can be valuable in numerous scenarios. For instance, you can insert pipeline metadata into the database to track deployment information or changes. This technique also allows you to steer other tasks based on values extracted from the database, enabling dynamic decision-making within your pipelines. Additionally, it can be used to perform specific tasks depending on the current configuration of the database, such as adjusting settings or executing configuration-specific scripts. These are just a few examples, and the technique can be adapted for many other use cases, making it a versatile tool in your DevOps toolkit.

In my recent project, I needed to achieve this, and I decided to leverage an existing service connection for connectivity. Despite the initial challenges, I was able to find two effective options that successfully addressed the issue. In this article, I will walk you through these options, detailing the steps and considerations involved, to help you seamlessly execute SQL queries from Azure DevOps to your Azure SQL Database.

The first option I discovered is the simplest and involves using the built-in task called Azure SQL Database Deployment.

This task is specifically designed for deploying to Azure SQL Database directly from Azure DevOps, but it also allows you to write your own SQL statements, which is useful in our scenario. The entire script is quite simple—it is configured to run manually and uses the latest Windows image for execution. It utilizes the aforementioned SQL Azure Dacpac Deployment task to connect to an Azure SQL Database using a predefined DevOps service connection with service principal authentication.

For simplicity, I added an inline SQL query (SELECT @@VERSION) to be executed on a specified SQL Server and database. One of the great features of this task is the ability to automatically detect the IP address used by DevOps and temporarily add it to Azure SQL, simplifying the connectivity process (of course proper permissions must be in place).

trigger:
- none

pool:
  vmImage: windows-latest

steps:
- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'service-connection'
    AuthenticationType: 'servicePrincipal'
    ServerName: 'myserver'
    DatabaseName: 'mydb'
    deployType: 'InlineSqlTask'
    SqlInline: 'SELECT @@VERSION as Version'
    IpDetectionMethod: 'AutoDetect'

In the screenshot below, you can see the correct result  of the query with the SQL Server version displayed on the screen

We can also execute stored procedures, and it still works well. Below, you can see the result of executing sp_help:

The above option is a wrapper that uses Invoke-Sqlcmd behind the scenes. For more advanced scenarios, where additional control is required, you might need to interact directly with Invoke-Sqlcmd. This approach involves a few more steps: first, use az account get-access-token to obtain an access token after logging in with the service connection. Then, you can use this token directly with Invoke-Sqlcmd to execute SQL commands:

trigger:
- none

pool:
  vmImage: windows-latest

steps:
- task: AzureCLI@2
  inputs:
   azureSubscription: 'service-connection'
   scriptType: 'ps'
   scriptLocation: 'inlineScript'
   inlineScript: |
     $accessToken = az account get-access-token --resource https://database.windows.net/ --query accessToken --output tsv
     $result = Invoke-Sqlcmd -ServerInstance "server" -Database "database" -AccessToken $accessToken -Query "SELECT @@VERSION as version"

     $resultValue = $result["Version"]
     Write-Host $resultValue
   addSpnToEnvironment: true
  

This approach is much more flexible. For example, you can save the query result to a DevOps variable using ##vso[task.setvariable] and then use this variable in subsequent tasks within the pipeline script. This allows for dynamic data handling and integration across different stages of your pipeline.

trigger:
- none

pool:
  vmImage: windows-latest

steps:
- task: AzureCLI@2
  inputs:
   azureSubscription: 'service-connection'
   scriptType: 'ps'
   scriptLocation: 'inlineScript'
   inlineScript: |
     $accessToken = az account get-access-token --resource https://database.windows.net/ --query accessToken --output tsv
     $result = Invoke-Sqlcmd -ServerInstance "server" -Database "database" -AccessToken $accessToken -Query "SELECT @@VERSION as version"
     
     $resultValue = $result["Version"]
     
     # Output the value to be captured by Azure DevOps
     Write-Output "##vso[task.setvariable variable=queryResult]$resultValue"
   addSpnToEnvironment: true

- task: AzureCLI@2
  inputs:
   azureSubscription: 'service-connection'
   scriptType: 'ps'
   scriptLocation: 'inlineScript'
   inlineScript: |
     Write-Output "SQL Version is $(queryResult)"
   addSpnToEnvironment: true

In this summary, we explored methods for executing SQL queries from Azure DevOps to an Azure SQL Database. We first discussed the straightforward approach using the built-in Azure SQL Database Deployment task, which simplifies the process by leveraging a pre-configured service connection and automating IP address configuration.

Next, we examined a more advanced approach involving the AzureCLI@2 task. This method provides greater flexibility by allowing direct use of Invoke-Sqlcmd with an access token obtained through az account get-access-token. This approach not only enables the execution of SQL queries but also allows saving query results to DevOps variables, which can be utilized in subsequent pipeline tasks. Both methods offer effective solutions depending on the complexity of the scenario, with the latter providing enhanced control and integration within Azure DevOps pipelines. I hope you find these options useful for your SQL query execution needs. See you!