Use PowerShell to create a SQL Azure firewall rule for your current IP
Tags: powershell, sql, azure, database
Published: 2020-11-22 09:21:39

You can also read this on my (new) [blog](https://seankearon.me/posts/2020/11/using-powershell-to-set-sql-azure-firewall/).

I often connect to my test SQL Azure database instance from my development machine.  To allow connections to the SQL Azure database there must be a rule for your IP address to allow the connection in the SQL Azure database's firewall.  When your IP changes you get an error saying that the connection could not be established.

You can always use the [Azure Portal](https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart) to easily add a client IP rule at any time.

!["Azure portal firewall rules"](https://dev-to-uploads.s3.amazonaws.com/i/opt9kxy2iev1h67q20d7.png)

But I find it faster and less distracting to add the firewall client IP rule by running a PowerShell script on my local machine (which I set up behind some keyboard shortcuts).  Here's how...

Before we can run the script, we need to install the `Az` and `Az.Sql` modules, like so:

```powershell
Install-Module -Name Az -AllowClobber -Scope CurrentUser
Install-Module -Name Az.Sql -AllowClobber -Scope CurrentUser
```

We then need to connect to your Azure account, which is done without any pain by running this and following the prompts to log into your Azure account and authenticate your local PowerShell to connect.

```powershell
Connect-AzAccount
```

After you're set up, the script first creates a rule name based on your local username and computer name.  For me, this will be `sean-on-SEAN-XPS`:

```powershell
$ruleName = "$env:USERNAME-on-$env:COMPUTERNAME"
```

Then we find our current IP address using the wonderful [http://checkip.dyndns.org/](http://checkip.dyndns.org/), which will give us a string something like:  `Current IP Address: 111.222.33.444`.  We just split that to get the IP address:

```powershell
$client = New-Object System.Net.WebClient
[xml]$response = $client.DownloadString("http://checkip.dyndns.org")
$ip = ($response.html.body -split ':')[1].Trim()
```

There is a function called `Set-ServerAccess` that does the real work, calling into the `Az.Sql` module we loaded earlier and we remove any existing rules before adding a new one:

```powershell
function Set-ServerAccess ($subscription, $resourceGroup, $server) {
    $context = Get-AzSubscription -SubscriptionName $subscription
    Set-AzContext $context

    Remove-AzSqlServerFirewallRule -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -ErrorAction SilentlyContinue
    New-AzSqlServerFirewallRule    -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -StartIpAddress $ip -EndIpAddress $ip
}
```

Then, we just call this for the server you want to connect to.  Just add more lines like this to connect to more than one server:

```powershell
Set-ServerAccess 'your subscription name' 'your resource group' 'your sql azure server name'
```

That's it, we're connected and back working again!  The full script is available from [this Gist](https://gist.github.com/seankearon/b49ed2aee7363917a61f99689abccbdb) and looks like this:

```powershell
$ruleName = "$env:USERNAME-on-$env:COMPUTERNAME"

$client = New-Object System.Net.WebClient
[xml]$response = $client.DownloadString("http://checkip.dyndns.org")
$ip = ($response.html.body -split ':')[1].Trim()

function Set-ServerAccess ($subscription, $resourceGroup, $server) {
    "Setting access rule for server $server in subscription $subscription" | Write-Host | Out-Null

    $context = Get-AzSubscription -SubscriptionName $subscription
    Set-AzContext $context

    Remove-AzSqlServerFirewallRule -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -ErrorAction SilentlyContinue
    New-AzSqlServerFirewallRule    -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -StartIpAddress $ip -EndIpAddress $ip
}

Set-ServerAccess 'your subscription name' 'your resource group' 'your sql azure server name'
```

Enjoy!