If you decide to use IP addresses to control what services have access to your Azure SQL Database, then understanding firewall rules are important. Within this important area you have the ability to create firewall rules that are scoped to the database or server level as shown below.

For this post I want to actually show you the TSQL code to do this, hopefully it will become a good reference point for the future. Before we step into the code lets understand the differences between database level and server level rules.

For server level rules they enable access your entire Azure SQL server, that is, all the databases within the same logical server. These rules are stored in the master database. Database level rules enable access to certain databases (yes you could also run this within master) within the same logical server, think of this as you being more granular with the access where they are created within the user database in question.

Personally, I try and always use database level rules, this is especially true when I work with failover groups.

Server Level

Let’s look at server level rules first. Your main Catalog views / stored procedures will be:

• sys.firewall_rules –  Displays the current server-level firewall rules
• sp_set_firewall_rule  – Creates or updates server-level firewall rules
• sp_delete_firewall_rule – Removes server-level firewall rules

Let’s look at my testing environment. Remember we must connect to the master database for server level rules.

SELECT * FROM sys.firewall_rules
 

As you can see I have 2 rules here. So now let’s delete the rule called “arun”.

EXEC sp_delete_firewall_rule  N'arun'

Now, you want to add a new server level rule? Below I am mapping in only 1 IP address rather than a range.

-- Create server-level firewall setting for only IP 1.2.3.4
EXEC sp_set_firewall_rule N'WorldIP', '1.2.3.4', '1.2.3.4';

You can double check this.

 SELECT * FROM sys.firewall_rules where name ='WorldIP'

Let’s move onto database level rule code.

Database Level

Remember that these are scoped to the user database and you can only create these via TSQL. The server level rules you could do via the Azure portal but I like using code.

• sys.database_firewall_rules – Displays the current database-level firewall rules
• sp_set_database_firewall_rule – Creates or updates the database-level firewall rules
• sp_delete_database_firewall_rule – Removes database-level firewall rules

I Connect to my dummy database and run the following (returns nothing).

SELECT * FROM sys.database_firewall_rules
 

So let’s add a new database level rule.

-- Create database-level firewall setting for only IP 0.0.0.9
EXECUTE sp_set_database_firewall_rule N'MyDummyServer', '0.0.0.9', '0.0.0.9';
SELECT * FROM sys.database_firewall_rules
 

To complete this blog post, the command to delete the rule.

EXECUTE sp_delete_database_firewall_rule N'MyDummyServer';