SALES: 1-800-867-1380

sp_set_database_firewall_rule (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see sp_set_database_firewall_rule.

Creates or updates the database-level firewall rules for your Microsoft Azure SQL Database instance. This stored procedure is available in the master database and user databases. The sp_set_database_firewall_rule stored procedure is specific to Azure SQL Database and is not supported in on-premise SQL Server.

Syntax Conventions (Azure SQL Database)

sp_set_database_firewall_rule [@name = ] ‘name’, [@start_ip_address =] ’start_ip_address’, [@end_ip_address =] ‘end_ip_address’

The following table demonstrates the supported arguments and options in Azure SQL Database.

 

Name Datatype Description

[@name = ] ‘name’

NVARCHAR(128)

The name used to describe and distinguish the database-level firewall setting.

[@start_ip_address =] ’start_ip_address’

VARCHAR(50)

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 Azure SQL Database instance. The lowest possible IP address is 0.0.0.0.

[@end_ip_address =] ‘end_ip_address’

VARCHAR(50)

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 Azure SQL Database instance. The highest possible IP address is 255.255.255.255.

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

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 Azure SQL Database server from Azure. Provide a value to the name parameter that will help you remember what the firewall setting is for.

Only the server-level principal login created by the provisioning process can create or modify database level firewall rules.

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

-- Enable Azure connections.
exec 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 allow an additional IP address, 0.0.0.5, in that firewall setting.

-- Create 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'

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft