0

I have a powershell task that connects to an Azure database and executes SQL Scripts. The script works when executed on any machine for which the corresponding IP address or range has been set as allowable within the firewall rules in Azure.

The problem is when the Script is executed within a DevOps pipeline I always get the error,

"Invoke-Sqlcmd : Cannot open server 'SERVERNAME' requested by the login. Client with IP address 'XX.XXX.XXX.XXX' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect."

I understand what's happening but after setting a specified range of IP's based on the IP referenced during the error; on a subsequent execution the agent runs on yet another IP which is outside of the range I allow.

After a couple of times doing this it was obvious that I was simply widening the IP range beyond acceptable for reasons of security.

So the question is there a way to restrict/control the range of the DevOps run agents for tasks where they're required to access Azure based Resources that are under access control via firewalls?

Mark
  • 55
  • 1
  • 7

1 Answers1

0

The reason you are hitting multiple IPs is that you are using Microsoft-hosted agents which will be updated weekly depends upon Microsoft Azure Datacenters

There are 2 solutions i can think of to solve your problem

Solution 1: (a little bit of a workaround and discuss this with your security team)

Adding a couple of task(s) in your pipeline to add and remove the IP of azure DevOps in order to invoke your sqlcmd

Step 1:

steps:
- task: AzurePowerShell@5
  displayName: 'Add buildserver public ip'
  inputs:
    azureSubscription: test
    ScriptType: InlineScript
    Inline: |
     $ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
     New-AzSqlServerFirewallRule -ResourceGroupName "group"  -ServerName "database-server-name" -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip
    azurePowerShellVersion: LatestVersion

Step 2: Your Invoke-sqlcmd

Step 3: Remove the added IP from the step1

Here is some good reference

Solution 2:

Using Self-Hosted Agent

An agent that you set up and manage on your own to run jobs is a self-hosted agent. You can use self-hosted agents in Azure Pipelines or Azure DevOps Server, formerly named Team Foundation Server (TFS). Self-hosted agents give you more control to install dependent software needed for your builds and deployments. Also, machine-level caches and configuration persist from run to run, which can boost speed.

With the self-hosted agent, you will get your dedicated public IP which will not gona to be changed and you can control the IP

Jayendran
  • 9,638
  • 8
  • 60
  • 103
  • Thank you. I will need to run these options past our DevOps admin and hopefully 1 of them will be a viable solution. Otherwise, I suppose we'll have to find another method for solution of the current task. – Mark Jun 05 '22 at 14:09