Agile Ramblings

Home » Azure » Using PowerShell to Set Your Azure SQL firewall rule

Using PowerShell to Set Your Azure SQL firewall rule

If you’ve read a couple of my recent blog posts, you’ll see that I’ve been working in PowerShell a lot lately. I’ve also been working with Azure a lot lately as well and I’m getting opportunities to put those two things together.

Since my laptop is moving around a lot and occasionally my home IP address changes, I do need to update my Azure SQL Firewall rule to allow my computer at my current IP address to talk to my Azure SQL database server.

Azure SQL Database Firewall

I’ve added 4 simple functions to my .\profile.ps1 script that makes this job really easy.

function Set-MyAzureFirewallRule {
    $response = Invoke-WebRequest ifconfig.me/ip
    $ip = $response.Content.Trim()
    New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $ip -EndIPAddress $ip -RuleName <Name of Rule> -ServerName <your database server name here>
}
function Update-MyAzureFirewallRule{
    $response = Invoke-WebRequest ifconfig.me/ip
    $ip = $response.Content.Trim()
    Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $ip -EndIPAddress $ip -RuleName <Name of Rule> -ServerName <your database server name here>
}
function Remove-MyAzureFirewallRule{
    Remove-AzureSqlDatabaseServerFirewallRule -RuleName <Name of Rule> -ServerName <your database server name here>
}
function Get-MyAzureFirewallRule{
    Get-AzureSqlDatabaseServerFirewallRule -RuleName <Name of Rule> -ServerName <your database server name here>
}

Get the Azure PowerShell Module

The first thing you’ll need to do if you want to do any work with Azure via PowerShell is download and install the Azure PowerShell modules.

Install And Configure Azure PowerShell

Once you’ve done this, you’ll be able to run Azure CommandLets in your PowerShell session.

How to get your IP address

Since many times I’m behind a router that is doing NAT translations, knowing my IP address isn’t as simple as typing Get-NetIPAddress | Format-Table or ipconfig in a console. That will tell me what my computer thinks the IP address is in my local network, but that isn’t what Azure will see. Azure will see the IP address of my cable modem.

In order to find out what my IP address is from an external perspective, I need the help of a little service called ifconfig.me tell me what my IP address is externally. If you make the whole Url ifconfig.me/ip you will get a simple text response from them with your IP address. Just give that Url a click and try it out. If you view the page source, you’ll see that only text was returned.

Putting it all together

So now we have the Azure PowerShell modules and we know about ifconfig.me. All we need now is the put the two together into one of our functions. I’ll use my first function as the example. You’ll be able to follow the rest after I describe this one.

function Set-MyAzureFirewallRule {
    $response = Invoke-WebRequest ifconfig.me/ip
    $ip = $response.Content.Trim()
    New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $ip -EndIPAddress $ip -RuleName <Name of Rule> -ServerName <your database server name here>
}

The first line is the PowerShell (non-Azure) CmdLet Invoke-WebRequest ifconfig.me/ip. This will call ifconfig.me/ip and get a response, trapped in the $response variable.

In the next line, I clean up the response a little bit using some .Net string functions to move my IP address into the $ip variable.

Finally, I call the Azure PowerShell CmdLet to create a new Firewall rule in my Azure account.

You will have to have followed the instructions in Azure PowerShell Install and Configure to set up the authentication to allow this PowerShell session to access your Azure subscription.

The other three variations of this function are for completeness. You will actually probably use the Update-MyAzureFirewallRule most since you’ll set-up the Firewall rule once the first time and then you’ll just need to update it whenever your IP address changes.

Final Thoughts

I hope this post makes it easier for you to access your SQL Azure database server from your laptop, where ever it may have moved. Once you’ve set up the rule, you’ll be able to access your database server from the tools in Visual Studio, SQL Server Management Studio, or any other tool you prefer to use to work with your Azure SQL Server.

Enjoy!!


5 Comments

  1. […] This post provided the inspiration, which I then tweaked it to suit my needs. Like I said; it’s quick-and-dirty, so use at your own risk. 😉  I’m no PowerShell guru either, so feel free to let me know if you improve on it. […]

  2. Padma Peddigari says:

    am getting below error whie running Invoke-WebRequest ifconfig.me/ip

    Invoke-WebRequest : Unable to connect to the remote server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: