CODEWITHSUNDEEP
azure-devopsazure-sql-database
Yogesh Kulkarni
Yogesh Kulkarni

Reputation: 359

How to query Azure SQL DB from Azure DevOps Pipeline?

I want to query Azure SQL DB from Azure DevOps release pipeline. How to do this?

Upvotes: 1

Views: 4759

Answers (1)

Repcak
Repcak

Reputation: 1006

One way would be using powershell Invoke-sqlcmd In a powershell task in Azure Devops.

Example:

$return = Invoke-Sqlcmd 
   -ConnectionString $DBCONNECTIONSTRING ` 
   -InputFile $PSScriptRoot/Create-DbApiUser.sql ` 
   -Variable @("password=$Password","username=$Username")

This example uses a full connection string and an input file as the SQL query. You don't need that however and you can write the SQL query in PowerShell itself. You can also pass variables to the SQL query as in the example. Also, the $return also to get the table data into a PowerShell variable and work further with that.

It also depends what you want to achieve. Invoke-sqlcmd has its limitations.

You have also a dedicated AzureDevops task:

    - task: SqlAzureDacpacDeployment@1
      inputs:
        AuthenticationType: 'server'
        ServerName: '$Sqlservername'
        DatabaseName: '$Databasename'
        SqlUsername: '$Login'
        SqlPassword: '$Password'
        deployType: 'InlineSqlTask'
        SqlInline: 'SELECT * FROM foo'
        IpDetectionMethod: 'AutoDetect'

Note, to access the database you have to set the firewall settings on the sql server to either:

  1. Allow Azure services and resources to access this server.
  2. If 1 is not an option, add a powershell task as showed in the documentation to add and remove firewall rules so the pipeline can connect to the database.

Upvotes: 2

Related Questions