How to: Configure the Database-Level Firewall Settings (Windows Azure SQL Database)

1. Use the Management Portal to confirm that there is a server-level firewall setting allowing your computer to attempt connection to the SQL Database server. The IP address of your computer must be within the IP address range of one of the server-level firewall settings. For more information, see Windows Azure SQL Database Firewall.

2. Connect to the database for which you want to create a database-level firewall rule.

3. Create a database-level firewall rule by using the sp_set_database_firewall_rule stored procedure. Add a new firewall setting for Internet-based connections by specifying a unique name in the name parameter of the sp_set_database_firewall_rule stored procedure. Specify the lowest desired IP address in that range with the start_ip_address parameter and the highest desired IP address in that range with the end_ip_address parameter. The name parameter is of the nvarchar data type and the start_ip_address and the end_ip_address parameters are of the varchar data type.

   Similarly, you can enable connection attempts from Windows Azure by using the sp_set_database_firewall_rule stored procedure with the parameters start_ip_address and end_ip_address equal to 0.0.0.0.

4. View the database-level firewall settings for the database by executing the following query: select * from sys.database_firewall_rules. This view is present in each database in your SQL Database server, and returns a list of all the database-level rules for a database.

5. Update an existing database-level firewall setting for Internet-based connections by specifying an existing name in the name parameter of the sp_set_database_firewall_rule stored procedure. Specify the new IP address range with the start_ip_address and end_ip_address parameters.

6. Remove a database-level firewall setting by specifying name of the undesired firewall setting in the name parameter of the sp_delete_database_firewall_rule stored procedure.

This example demonstrates how to configure your database-level firewall settings using Transact-SQL. To view a database-level firewall setting for a database in your SQL Database server, connect to the database, and run the following query:

-- view database-level firewall settings
select * from sys.database_firewall_rules

You can use the sp_set_database_firewall_rule stored procedure to add or change firewall settings. The following code creates a database-level firewall setting called Example DB Setting 1 for only the IP address 0.0.0.4. Then, the sp_set_database_firewall_rule stored procedure is called again to allow an additional IP address, 0.0.0.5, in that firewall setting.

-- create new database-level firewall setting for only IP 0.0.0.4
exec sp_set_database_firewall_rule N'Example DB Setting 1','0.0.0.4','0.0.0.4'

-- update database-level firewall setting to also allow IP 0.0.0.5
exec sp_set_database_firewall_rule N'Example DB Setting 1','0.0.0.4','0.0.0.5'

To remove a firewall setting, use the sp_delete_database_firewall_rule stored procedure, as shown in the following code.

-- Remove database-level firewall setting
exec sp_delete_database_firewall_rule N'Example DB Setting 1'

Tasks

Concepts