In my previous post, I have shown how to configure the SQL Azure firewall settings from Azure management portal. In this post, I am going to discuss how to configure the SQL Azure firewall settings via SQL sever management studio 2008 R2. To do so, you need to login to the master database of the SQL Azure server with the server level principal login. Also make sure the Local computer’s IP falls in the “allowed” IP range. Now, we are ready to configure the SQL Azure firewall rules via SSMS

1. Run “select * from sys.firewall_rules” to get a list of all existing firewall rules:

“name” specifies is the user friendly name given to the firewall rule

The starting and the ending IP range is specified in the “start_ip_address” and the “end_ip_address” columns respectively.

“create_date” specifies the date on which the rule was created.

“modify_date” specifies the date on which the rule was last modified. Both “create_date” and “modify_date” display UTC time.

Also, note that if you wish to give a Azure services (in other words an app deployed on Azure platform) the permission to access the SQL Azure, you should add specifies 0.0.0.0 as the start_ip_address as well as the end_ip_address.

2. Now let’s add a firewall rule

schema: exec sp_set_firewall_rule N'<rule_name>’,'<start_ip>’,'<end_ip>’

To create a firewall rule for a Single IP:

exec sp_set_firewall_rule N‘justanexample1’,‘29.9.19.89’,‘29.9.19.89’

To create a firewall rule for a IP range:

exec sp_set_firewall_rule N‘justanexample2’,‘0.0.0.1’,‘0.0.0.100’;

3. To update the firewall rule

schema: exec sp_set_firewall_rule N'<rule_name>’,'<new_start_ip>’,'<new_end_ip>’

Let’s update the rule named “justanexample2”:

exec sp_set_firewall_rule N'justanexample2','0.0.0.1','0.0.0.200';

Note that the end_ip_address got updated!

4. Now, let’s delete the rule.

schema exec sp_delete_firewall_rule N'<rule_name>’

Let’s delete the rule ‘justanexample1” and “justanexample2”

exec sp_delete_firewall_rule N‘justanexample1’;

go

exec sp_delete_firewall_rule N‘justanexample2’;

go

select * from sys.firewall_rules;

go

Thus, we have been successful at configuring the SQL Azure firewall rules through SSMS!

Now, These queries can also ran from a Third party tool that let’s you manage SQL Azure. Just make sure you are login to the master database with the server level principal login and your IP is in the allowed range.