Adding / Deleting / Updating the SQL Azure firewall rules via SSMS

Standard

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:

1

“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’

2

 

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

 

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';

4

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

 

5

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.

Advertisements

One thought on “Adding / Deleting / Updating the SQL Azure firewall rules via SSMS

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s