sp-set-database-firewall-rule-azure-sql-database.md

title	sp_set_database_firewall_rule (Azure SQL Database) | Microsoft Docs
ms.custom
ms.date	08/04/2017
ms.prod
ms.prod_service	sql-database
ms.reviewer
ms.service	sql-database
ms.component	system-stored-procedures
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_set_database_firewall_rule sp_set_database_firewall_rule_TSQL sys.sp_set_database_firewall_rule sys.sp_set_database_firewall_rule_TSQL
dev_langs	TSQL
helpviewer_keywords	sp_set_database_firewall_rule firewall_rules, setting database rules
ms.assetid	8f0506b6-a4ac-4e4d-91db-8077c40cb17a
caps.latest.revision	15
author	edmacauley
ms.author	edmaca
manager	cguyer
ms.workload	On Demand

sp_set_database_firewall_rule (Azure SQL Database)

[!INCLUDEtsql-appliesto-xxxxxx-asdb-xxxx-xxx-md]

Creates or updates the database-level firewall rules for your [!INCLUDEssSDSfull]. Database firewall rules can be configured for the master database, and for user databases on [!INCLUDEssSDS]. Database firewall rules are particularly useful when using contained database users. For more information, see Contained Database Users - Making Your Database Portable.

Syntax

  
sp_set_database_firewall_rule [@name = ] [N]'name'  
, [@start_ip_address =] 'start_ip_address'  
, [@end_ip_address =] 'end_ip_address'
[ ; ]  

Arguments

[@name = ] [N]'name'
The name used to describe and distinguish the database-level firewall setting. name is nvarchar(128) with no default value. The Unicode identifier N is optional for [!INCLUDEssSDS_md].

[@start_ip_address =] 'start_ip_address'
The lowest IP address in the range of the database-level firewall setting. IP addresses equal to or greater than this can attempt to connect to the [!INCLUDEssSDS] instance. The lowest possible IP address is 0.0.0.0. start_ip_address is varchar(50) with no default value.

[@end_ip_address =] 'end_ip_address'
The highest IP address in the range of the database-level firewall setting. IP addresses equal to or less than this can attempt to connect to the [!INCLUDEssSDS] instance. The highest possible IP address is 255.255.255.255. end_ip_address is varchar(50) with no default value.

The following table demonstrates the supported arguments and options in [!INCLUDEssSDS].

Note

Azure connection attempts are allowed when both this field and the start_ip_address field equals 0.0.0.0.

Remarks

The names of database-level firewall settings for a database must be unique. If the name of the database-level firewall setting provided for the stored procedure already exists in the database-level firewall settings table, the starting and ending IP addresses will be updated. Otherwise, a new database-level firewall setting will be created.

When you add a database-level firewall setting where the beginning and ending IP addresses are equal to 0.0.0.0, you enable access to your database in the [!INCLUDEssSDS] server from any Azure resource. Provide a value to the name parameter that will help you remember what the firewall setting is for.

Permissions

Requires CONTROL permission on the database.

Examples

The following code creates a database-level firewall setting called Allow Azure that enables access to your database from Azure.

-- Enable Azure connections.  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0';  
  

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 update the end IP address to 0.0.0.6, in that firewall setting. This creates a range which allows IP addresses 0.0.0.4, 0.0.0.5, and 0.0.0.6 to access the database.

-- Create database-level firewall setting for only IP 0.0.0.4  
EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.4';  
  
-- Update database-level firewall setting to create a range of allowed IP addresses
EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.6';  
  

See Also

Azure SQL Database Firewall
How to: Configure Firewall Settings (Azure SQL Database)
sp_set_firewall_rule (Azure SQL Database)
sp_delete_database_firewall_rule (Azure SQL Database)
sys.database_firewall_rules (Azure SQL Database)