Controlling database access through Windows Azure SQL database firewall rules

Today's article comes from the technical writer Kumar Vivek of our user Experience team. This article briefly outlines the newly introduced database-level firewall rules in Windows http://www.aliyun.com/zixun/aggregation/13357.html ">azure SQL database."

Windows Azure SQL Database firewall can prevent others from accessing your SQL database to help protect your data. You can specify firewall rules, such as limiting the range of IP addresses that are allowed to access, to control access to the database. However, these rules are defined at the server level, allowing authorized terminals to access your entire SQL database server, which is all databases on that logical server. What if you want to control access to a particular database on the server that contains security information? You're probably going to need some brains.

So now, you can do it! Describes the database-level firewall rules in the Windows Azure SQL database! In addition to the existing server-level firewall rules, you can now define firewall rules for each database in the SQL database server to restrict access to specific terminals. To do this, you must create a database-level firewall rule to make sure that the IP address of the requesting client is outside the server-level firewall rules, the database-level firewall rules.

The following describes how a connection request from a client is made through Windows Azure SQL database firewall rules:

• Grant SQL database server connection permissions if the requested IP address is within the scope specified by the server-level firewall rule.
• Check the database-level firewall rules if the requested IP address is not within the scope specified by the server-level firewall rule. If the requested IP address is within the range specified by the latter, only the appropriate database is granted connection permissions.
• The connection request fails if the requested IP address is not within the scope specified by any server-level or database-level firewall rule.

Managing database-level firewall rules
Unlike firewall rules at the server level, database-level firewall rules are built for each database and stored in separate databases (including master). The Sys.database_firewall_rules view in each database is displayed in the current database-level firewall rules. Also, you can use the Sp_set_database_firewall_rule and sp_delete_database_firewall_rule stored procedures in each database to create or delete database-level firewall rules.

Microsoft Windows Azure SQL database blocks access to SQL database servers by using SQL database firewall. You can define database-level firewall settings for master or user databases in the SQL database server to selectively allow access to the database. You must connect to the database to create or edit database-level firewall rules for the database. For more information about database-level firewall rules, see Windows Azure SQL Database firewall.

Note: When you first connect to a SQL Database server, you must use the administrative portal to enable the connection through the firewall.

Configure database-level firewall settings for master and user databases

1. Use the Admin portal to confirm the presence of server-level firewall settings that allow your computer to attempt to connect to the SQL Database server. Your computer's IP address must be within the range of IP addresses set by one of the server-level firewalls. For more information, see Windows Azure SQL Database firewall.

2, connect to the database to create the database-level firewall rules.

3. Create database-level firewall rules using sp_set_database_firewall_rule stored procedures. Adds a new firewall setting for an internet-based connection by specifying a unique name in the name parameter of the sp_set_database_firewall_rule stored procedure. Use the start_ip_address parameter to specify the minimum IP address in the range that you want, and use the end_ip_address parameter to specify the highest IP address in the range that you want. The data type of the name parameter is varchar for the data type of the nvarchar,start_ip_address and end_ip_address parameters.

Similarly, you can enable Windows Azure by using the Sp_set_database_firewall_rule stored procedure (where the parameters start_ip_address and end_ip_address equals 0.0.0.0) The connection attempt made.

4. View database-level firewall settings for databases by executing the following query: SELECT * from Sys.database_firewall_rules. This view exists in every database on the SQL database server, and it returns a list of all database-level rules for the database.

5. Update existing database-level firewall settings for internet-based connections by specifying an existing name in the name parameter of the sp_set_database_firewall_rule stored procedure. Use the start_ip_address and end_ip_address parameters to specify a new range of IP addresses.

6, delete the database-level firewall settings by specifying the name of the unwanted firewall setting in the name parameter of the sp_delete_database_firewall_rule stored procedure.

Example

This sample demonstrates how to configure database-level firewall settings using Transact-SQL. To view the database-level firewall settings for a database in the SQL database server, connect to the database, and then 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 named Example DB Setting 1 for IP address 0.0.0.4 only. The sp_set_database_firewall_rule stored procedure is then called again, allowing another IP address in the firewall setting to 0.0.0.5 access.

--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 firewall settings, 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 '