Configuring the firewall for Client Access


YouTube Video (Better Quality)

Information good as of 4/29/2015 and is subject to change!

We get questions regarding client access to someone’s Azure SQL Database.  I wanted to go through and show what options you have to configure the firewall to allow access to known clients.  To start with, you may see an error, like the following, when you try and connect.

SNAGHTML38d001

Cannot open server ‘<servername>’ requested by the login. Client with IP Address ‘X.X.X.X’ 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. (Microsoft SQL Server, Error: 40615)

Existing Portal

The existing portal is located at https://manage.windowsazure.com.  From here we can go to SQL Databases.  Then select the database we want to give access to, or the server directly.  The firewall rules are actually on the server itself, but you can get there from either spot.

SNAGHTML838fa0

In the database, under quick glance, select Manage allowed IP Addresses.

SNAGHTML844072

This will take us to the Server – Configure Tab.  This will list out any existing rules you have configured.  This will also show you the IP Address you are coming from, and you can quickly add that by clicking Add to the Allowed IP Addresses.  You can also add a range of IP addresses to allow.

SNAGHTML8587e6

Preview Portal

We also have the preview portal that we can use as well.  This is located at https://portal.azure.com.  You want to use the Preview Portal if you have a v12 database!  From that portal, you can click on Browse, and then SQL Databases.

NOTE:  Within the Preview Portal, everything expands to the right.

SNAGHTML887549

Then you can select the server you want to modify.

SNAGHTML8959cd

On the Server screen, you can either click on the Settings icon, or click on Show firewall settings.

image

If you clicked on Settings, just click on Firewall.  This will bring you to the screen that Show firewall settings would bring you directly to.

The Firewall settings screen looks similar to the existing portal.  However, it doesn’t show you the IP you are coming from.  But you can add new rules here the same way you could in the other.  Just provide a name and a Start Address along with an End Address.  If it is just a single IP Address you will use the IP for both start and end.  Otherwise you can add a range.

SNAGHTML8d5b51

Then Save when you are done.

Stored Procedure

We can also used stored procedures to alter the firewall settings.  We have documentation on how these work.  These procedures will go against the Master database, so your account will need access to that, otherwise they won’t work.

sp_set_firewall_rule
https://msdn.microsoft.com/en-us/library/dn270017.aspx

Here are some examples of what this would look like:

–create a firewall rule for a single IP address
exec sp_set_firewall_rule N’DevComputer’,’206.63.251.3′,’206.63.251.3′

–create a firewall rule for Microsoft services and Windows Azure services
exec sp_set_firewall_rule N’MicrosoftServices’,’0.0.0.0′,’0.0.0.0′

–delete a firewall rule
exec sp_delete_firewall_rule N’DevComputer’

–get a list of the firewall rules
select * from sys.firewall_rules

PowerShell

You can install the Windows Azure PowerShell components which will include the Azure SQL Database cmdlets.  The following documentation says how to install this.  This comes from the Web Platform Installer.

How to install and configure Azure PowerShell
http://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

There are two PowerShell commands we can use to work with the Database Firewall rules.

Get-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546731.aspx

New-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546724.aspx

Remove-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546727.aspx

Set-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546739.aspx

You can also run the help command to get the list of these assuming everything was installed properly.  Also, be sure you are using the Microsoft Azure PowerShell prompt which will load the cmdlets automatically for you.

get-help *-AzureSqlDatabaseServerFirewall*

SNAGHTML980c22

We can then get a listing of the rules that are currently in place for our server.

Get-AzureSqlDatabaseServerFirewallRule -servername guyinacube | select -Property RuleName, StartIpAddress, EndIpAddress

SNAGHTML9a0e3b

You may only see the AllowAllWindowsAzureIps.  It will have the IP Address of all zeros.  This is to allow anything within the Windows Azure Datacenter access to the server.  You can remove this if you want.  This is just a Yes/No option within the portal.

SNAGHTML9b28a4

We can then add a Firewall rule using New-AzureSqlDatabaseServerFirewallRule.

New-AzureSqlDatabaseServerFirewallRule -ServerName guyinacube -RuleName "MyClient" -StartIpAddress 206.63.251.3 -EndIpAddress 206.63.251.3

SNAGHTMLa5be1d

Find your IP Address

When adding a firewall rule, you will need to know the IP Address that is external facing.  This is what will hit the Azure Data Centers.  It probably won’t be the IP Address you see from an ipconfig output.  To get this address, you can see it from the Existing portal.  The preview portal doesn’t show it.  There are websites you can go to that will tell you as well.

What is my IP

You can go to http://whatismyip.com and that will show you your IP address.

SNAGHTMLa1b595

Speedtest

Another site you can use is http://speedtest.net.  This is meant more to show you your upload and download speeds, but it will also show you your public IP address as well.

SNAGHTMLa42c42

 

Adam W. Saxton | Microsoft SQL Support – Escalation Services
@GuyInACube | Mixes | YouTube | Facebook.com\guyinacube

Comments (0)

Skip to main content