Azure SQL: As-needed and temporary access from Microsoft-hosted pipeline agents

Photo by Ralph (Ravi) Kayden on Unsplash

The Problem

Silvr was recently working with a client who needed enforce stricter firewall rules on their Azure SQL instances. The client made their configuration changes on Azure SQL’s firewall only to discover that their pipeline-based database migrations were suddenly failing. This was because the Microsoft-hosted agents in their pipelines were not accounted for in the firewall changes.

We discussed self-hosted agents as a solution to this problem and while they intend to deploy those, the time to implement them was too far out to be a reasonable here. We needed something that would allow their SQL commands to work now.

After some research, we determined that,

• Allowing all agent IP ranges was far too open a configuration for the use case,
• Trying to manage firewall rules manually seemed impossible

Solving it for Microsoft-hosted agents

We do have a measure of control over the pipelines themselves. After all, we already authorize the agents to create resources with Terraform and even run some PowerShell within the subscription, so why not let it modify the Azure SQL firewall as needed too?

The az.sql PowerShell module allows the creation and removal of Azure SQL firewall rules through New-AzSqlServerFirewallRule and Remove-AzSqlServerFirewallRule, so we set about using those to open and close the server’s firewall around the other SQL work that needed doing. But how do we get the agent’s IP address?

Though Microsoft Azure’s DevOps pipelines offer a host of predefined variables, a variable that provides the agent’s IP address isn’t one of them. Thankfully, services like ipinfo.io exist and can help us here. This got me the IP address we needed from whatever Microsoft-hosted agent we landed on.

This got me the IP address we needed from whatever Microsoft-hosted agent we happened to land on,

Invoke-RestMethod http://ipinfo.io/json | Select-Object -ExpandProperty ip

Putting that together with the firewall commands from az.sql we get something like this to make our firewall addition, send SQL commands to the server, and then remove the rule:

- task: AzurePowerShell@5
  displayName: do SQL things
  inputs:
    azureSubscription: $(Subscription)
    ScriptType: 'InlineScript'
    FailOnStandardError: True
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      # add a firewall rule
      $agent_ip = (Invoke-RestMethod http://ipinfo.io/json | Select-Object -ExpandProperty ip)
      New-AzSqlServerFirewallRule -StartIpAddress $agent_ip -EndIpAddress $agent_ip -ServerName $(sqlServer) -ResourceGroupName $(resourceGroupName) -FirewallRuleName "temp rule"
      # do the SQL tasks
      Invoke-SqlCmd ...
      # and then remove the rule
      Remove-AzSqlServerFirewallRule -ServerName $(sqlServer) -ResourceGroupName $(resourceGroupName) -FirewallRuleName "temp rule"

But there are a few problems with stuffing everything into a single PowerShell task:

• The code does more than one thing making it easy to introduce problems when updating it.
• Opening the firewall up to perform SQL queries happens in many of the client’s pipelines and we needed to be able to re-use this easily.

Let’s look at how to create and use a template that can open or close the firewall on a SQL server as needed.

Separate the tasks

Let’s say we broke the above code into its constituent tasks. Inside our fully-fledged, SQL-modifying pipeline, we need a task to open our firewall, a task to perform some commands via Invoke-SqlCmd, and then close the firewall again (the name used for FirewallRuleName is updated to make it unique and prevent potential collisions):

- task: AzurePowerShell@5
  displayName: Add a temporary firewall rule
  inputs:
    azureSubscription: $(Subscription)
    ScriptType: 'InlineScript'
    FailOnStandardError: True
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      $agent_ip = (Invoke-RestMethod http://ipinfo.io/json | Select-Object -ExpandProperty ip)
      New-AzSqlServerFirewallRule -StartIpAddress $agent_ip -EndIpAddress $agent_ip -ServerName $(sqlServer) -ResourceGroupName $(resourceGroupName) -FirewallRuleName "pipeline_temp_rule_$(Build.BuildId)_$agent_ip"
- task: AzurePowershell@5
  displayName: SQLCMDs
  inputs:
    azureSubscription: $(Subscription)
    ScriptType: 'Inline Script'
    FailOnStandardError: True
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
        Invoke-SqlCmd ...
- task: AzurePowerShell@5
  displayName: Remove temporary firewall rule
  inputs:
    azureSubscription: $(Subscription)
    ScriptType: 'InlineScript'
    FailOnStandardError: True
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      $agent_ip = (Invoke-RestMethod http://ipinfo.io/json | Select-Object -ExpandProperty ip)
      Remove-AzSqlServerFirewallRule -ServerName $(sqlServer) -ResourceGroupName $(resourceGroupName) -FirewallRuleName "pipeline_temp_rule_$(Build.BuildId)_$agent_ip"

Remember: you’re not guaranteed to get the same agent from stage to stage. As such, the work above is in separate tasks that will run within a single stage.

The two firewall steps are quite similar: they each have many of the same inputs and both using just a PowerShell task to do their work. And that makes them a decent candidate for a template.

What about adding more inputs to the template that control whether we add or remove the firewall rule?

Make it a template

First, let’s get the parameters into our template. We’ll need the basics that allow us to find our SQL server (a resourceGroupName and a sqlServer name as well.) and provides the subscription to use. I decided to use two different boolean parameters for adding or deleting the firewall rule: createRule and removeRule. With those added, the parameters look something like this:

parameters:
- name: buildNumber
  type: string
- name: subscription
  type: string
- name: sqlServer
  type: string
- name: resourceGroupName
  type: string
- name: createRule
  type: boolean
  default: False
- name: removeRule
  type: boolean
  default: False

Now we need merely to create steps to do the work conditionally, one task to add a new rule based on createRule being true and one task to remove an existing rule based on removeRule being false. For more on conditions see, specify conditions.

steps:
- task: AzurePowerShell@5
  displayName: Add a temporary firewall rule
  condition: and(succeeded(),eq(${{ parameters.createRule }}, true))
  inputs:
    azureSubscription: ${{ parameters.Subscription }}
    ScriptType: 'InlineScript'
    FailOnStandardError: true
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      $agent_ip = (Invoke-RestMethod http://ipinfo.io/json | Select -exp ip)
      New-AzSqlServerFirewallRule -StartIpAddress $agent_ip -EndIpAddress $agent_ip -ServerName ${{ parameters.sqlServer }} -ResourceGroupName ${{ parameters.resourceGroupName }} -FirewallRuleName "pipeline_temp_rule_${{ parameters.BuildNumber }}_$agent_ip"
- task: AzurePowerShell@5
  displayName: Remove temporary firewall rule
  condition: and(succeeded(),eq(${{ parameters.removeRule }}, true))
  inputs:
    azureSubscription: ${{ parameters.Subscription }}
    ScriptType: 'InlineScript'
    FailOnStandardError: true
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
      $agent_ip = (Invoke-RestMethod http://ipinfo.io/json | Select -exp ip)
      Remove-AzSqlServerFirewallRule -ServerName ${{ parameters.sqlServer }} -ResourceGroupName ${{ parameters.resourceGroupName }} -FirewallRuleName "pipeline_temp_rule_${{ parameters.BuildNumber }}_$agent_ip"

We’ve dropped the finished template into its own repository to make it easy to consume it from other pipelines. There is plenty more information about templates on template types & usage from Microsoft.

Revised pipeline using the template

With that template built, our revised section for the job looks something like this now,

stages:
  - stage:
    jobs:
      - job:
- checkout: self

- checkout: templates
  path: templates

- template: azsql-host-agent-firewall-rule.yml@templates
  parameters:
    Subscription: $(Subscription)
    BuildNumber: $(Build.BuildNumber)
    sqlServer: $(sqlServer)
    resourceGroupName: $(resourceGroupName)
    createRule: True

- task: AzurePowershell@5
  displayName: SQLCMDs
  inputs:
    azureSubscription: $(Subscription)
    ScriptType: 'Inline Script'
    FailOnStandardError: True
    pwsh: True
    azurePowerShellVersion: 'LatestVersion'
    Inline: |
        Invoke-SqlCmd ...
- template: azsql-host-agent-firewall-rule.yml@templates
  parameters:
    Subscription: $(Subscription)
    BuildNumber: $(Build.BuildNumber)
    sqlServer: $(sqlServer)
    resourceGroupName: $(resourceGroupName)
    removeRule: True

What’s that doing?

1. Within the job, checkout two different repositories: one for the current pipeline and it’s files, and the other containing our templates (note: @templates when each template step is called).
2. The pipeline calls the SQL firewall rule template with createRule: True to open the firewall for the agent.
3. The SQL commands are executed.
4. The pipeline then calls the SQL firewall rule templates again with removeRule: True to remove the previously created rule.

This improves our situation from where we started by:

• Reducing the code within the main pipeline overall.
• Reducing the chance of errors being introduced through simplification and isolation.
• Increasing maintainability through templates.
• Reducing development friction for the next pipeline that needs access to talk to a SQL server.

Wrapping up

This isn’t perfect in its current state. We could make some improvements:

• The template should error if both Rules are true or both are false
• We could add a cleanup pre-step or post-step that removes stale rules.

For example, add a timestamp and automatically remove any rules that are more than 24 hours old.

What if we needed to work across stages? If we wanted to make this easy to implement in whichever pipeline needed it, we’d forego attempts at sending rule names across stages. This approach gets more unwieldy with every additional stage and rule. Instead, refactor the template to add names with the buildId and an incrementor that responds to existing rules. And then, the removal is simply finding and deleting the rules that match the buildId.

What other use cases might an approach like this help?

Share your thoughts…