Data Series: Control Database Access Using Windows Azure SQL Database Firewall Rules

Editor’s Note: Today’s post comes from Kumar Vivek, Technical Writer in our Customer Experience team. This post provides an overview of the newly-introduced database-level firewall rules in Windows Azure SQL Database.

Windows Azure SQL Database firewall prevents access to your SQL Database server to help protect your data. You could specify firewall rules to control access to your SQL Database server by specifying ranges of acceptable IP addresses. However, these firewall rules were defined at the server level, and enabled clients to access your entire SQL Database server, that is, all the databases within the same logical server. What if you wanted to control access to particular databases (containing secure information) within your SQL Database server; you could not do so earlier.

Well, now you can! Introducing database-level firewall rules in Windows Azure SQL Database! In addition to the server-level firewall rules, you can now define firewall rules for each database in your SQL Database server to restrict access to selective clients. To do so, you must create a database-level firewall rule for the required database with an IP address range that is beyond the IP address range specified in the server-level firewall rule, and ensure that the IP address of the client falls in the range specified in the database-level firewall rule.

This is how the connection attempt from a client passes through the firewall rules in Windows Azure SQL Database:

  • If the IP address of the request is within one of the ranges specified in the server-level firewall rules, the connection is granted to your SQL Database server.
  • If the IP address of the request is not within one of the ranges specified in the server-level firewall rule, the database-level firewall rules are checked. If the IP address of the request is within one of the ranges specified in the database-level firewall rules, the connection is granted only to the database that has a matching database-level rule.
  • If the IP address of the request is not within the ranges specified in any of the server-level or database-level firewall rules, the connection request fails.

 For detailed information, see the full article Windows Azure SQL Database Firewall.

Managing Database-Level Firewall Rules

Unlike server-level firewall rules, the database-level firewall rules are created per database and are stored in the individual databases (including master). The sys.database_firewall_rules view in each database displays the current database-level firewall rules. Further, you can use the sp_set_database_firewall_rule and sp_delete_database_firewall_rule stored procedures in each database to create and delete the database-level firewall rules for the database.

For detailed information about managing database-level firewall rules, see the complete article How to: Configure the Database-Level Firewall Settings.