Azure SQL Database and SQL Data Warehouse IP firewall rules

Microsoft Azure SQL Database and SQL Data Warehouse provide a relational database service for Azure and other Internet-based applications. To help protect your data, firewalls prevent all access to your database server until you specify which computers have permission. The firewall grants access to databases based on the originating IP address of each request.

 Note

This article applies to Azure SQL server, and to both SQL Database and SQL Data Warehouse databases that are created on the Azure SQL server. For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.

 Important

This article does not apply to Azure SQL Database Managed Instance. Please see the following article on connecting to a Managed Instance for more information about the networking configuration needed.

Virtual network rules as alternatives to IP rules

In addition to IP rules, the firewall also manages virtual network rules. Virtual network rules are based on Virtual Network service endpoints. Virtual network rules might be preferable to IP rules in some cases. 

Overview

Initially, all access to your Azure SQL server is blocked by the SQL Database firewall. To access a database server, you must specify one or more server-level IP firewall rules that enable access to your Azure SQL server. Use the IP firewall rules to specify which IP address ranges from the Internet are allowed, and whether Azure applications can attempt to connect to your Azure SQL server.

To selectively grant access to just one of the databases in your Azure SQL server, you must create a database-level rule for the required database. Specify an IP address range for the database IP firewall rule that is beyond the IP address range specified in the server-level IP firewall rule, and ensure that the IP address of the client falls in the range specified in the database-level rule.

 Important

SQL Data Warehouse only supports server-level IP firewall rules and does not support database-level IP firewall rules.

Connection attempts from the Internet and Azure must first pass through the firewall before they can reach your Azure SQL server or SQL Database, as shown in the following diagram:

• Server-level IP firewall rules:
  These rules enable clients to access your entire Azure SQL server, that is, all the databases within the same SQL Database server. These rules are stored in the master database. Server-level IP firewall rules can be configured by using the portal or by using Transact-SQL statements. To create server-level IP firewall rules using the Azure portal or PowerShell, you must be the subscription owner or a subscription contributor. To create a server-level IP firewall rule using Transact-SQL, you must connect to the SQL Database instance as the server-level principal login or the Azure Active Directory administrator (which means that a server-level IP firewall rule must first be created by a user with Azure-level permissions).
• Database-level IP firewall rules:
  These rules enable clients to access certain (secure) databases within the same SQL Database server. You can create these rules for each database (including the master database) and they are stored in the individual databases. Database-level IP firewall rules for master and user databases can only be created and managed by using Transact-SQL statements and only after you have configured the first server-level firewall. If you specify an IP address range in the database-level IP firewall rule that is outside the range specified in the server-level IP firewall rule, only those clients that have IP addresses in the database-level range can access the database. You can have a maximum of 128 database-level IP firewall rules for a database.

Recommendation

Microsoft recommends using database-level IP firewall rules whenever possible to enhance security and to make your database more portable. Use server-level IP firewall rules for administrators and when you have many databases that have the same access requirements and you don't want to spend time configuring each database individually.

 Important

Windows Azure SQL Database supports a maximum of 128 IP firewall rules.

Connecting from the Internet

When a computer attempts to connect to your database server from the Internet, the firewall first checks the originating IP address of the request against the database-level IP firewall rules, for the database that the connection is requesting:

• If the IP address of the request is within one of the ranges specified in the database-level IP firewall rules, the connection is granted to the SQL Database that contains the rule.
• If the IP address of the request is not within one of the ranges specified in the database-level IP firewall rule, the server-level IP firewall rules are checked. If the IP address of the request is within one of the ranges specified in the server-level IP firewall rules, the connection is granted. Server-level IP firewall rules apply to all SQL databases on the Azure SQL server.
• If the IP address of the request is not within the ranges specified in any of the database-level or server-level IP firewall rules, the connection request fails.

 Note

To access Azure SQL Database from your local computer, ensure the firewall on your network and local computer allows outgoing communication on TCP port 1433.

Connecting from Azure

To allow applications from Azure to connect to your Azure SQL server, Azure connections must be enabled. When an application from Azure attempts to connect to your database server, the firewall verifies that Azure connections are allowed. A firewall setting with starting and ending address equal to 0.0.0.0 indicates Azure connections are allowed. If the connection attempt is not allowed, the request does not reach the Azure SQL Database server.

 Important

This option configures the firewall to allow all connections from Azure including connections from the subscriptions of other customers. When selecting this option, make sure your login and user permissions limit access to only authorized users.

Creating and managing IP firewall rules

The first server-level firewall setting can be created using the Azure portal or programmatically using Azure PowerShell, Azure CLI, or the REST API. Subsequent server-level IP firewall rules can be created and managed using these methods, and through Transact-SQL.

 Important

Database-level IP firewall rules can only be created and managed using Transact-SQL.

Manage server-level IP firewall rules using the Azure portal

To set a server-level IP firewall rule in the Azure portal, you can either go to the Overview page for your Azure SQL database or the Overview page for your SQL Database server.

From database overview page:-

1. To set a server-level IP firewall rule from the database overview page, click Set server firewall on the toolbar as shown in the following image: The Firewall settings page for the SQL Database server opens.
   
2. Click Add client IP on the toolbar to add the IP address of the computer you are currently using and then click Save. A server-level IP firewall rule is created for your current IP address.
   

From server overview page:-

The overview page for your server opens, showing you the fully qualified server name (such as mynewserver20170403.database.windows.net) and provides options for further configuration.

1. To set a server-level rule from server overview page, click Firewall in the left-hand menu under Settings:
2. Click Add client IP on the toolbar to add the IP address of the computer you are currently using and then click Save. A server-level IP firewall rule is created for your current IP address.

Manage IP firewall rules using Transact-SQL

Catalog View or Stored Procedure	Level	Description
sys.firewall_rules	Server	Displays the current server-level IP firewall rules
sp_set_firewall_rule	Server	Creates or updates server-level IP firewall rules
sp_delete_firewall_rule	Server	Removes server-level IP firewall rules
sys.database_firewall_rules	Database	Displays the current database-level IP firewall rules
sp_set_database_firewall_rule	Database	Creates or updates the database-level IP firewall rules
sp_delete_database_firewall_rule	Databases	Removes database-level IP firewall rules

The following examples review the existing rules, enable a range of IP addresses on the server Contoso, and deletes an IP firewall rule:

SQL

SELECT * FROM sys.firewall_rules ORDER BY name;

Next, add a server-level IP firewall rule.

SQL

EXECUTE sp_set_firewall_rule @name = N'ContosoFirewallRule',
   @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

To delete a server-level IP firewall rule, execute the sp_delete_firewall_rule stored procedure. The following example deletes the rule named ContosoFirewallRule:

SQL

EXECUTE sp_delete_firewall_rule @name = N'ContosoFirewallRule'

Manage server-level IP firewall rules using Azure PowerShell

 Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 

 Important:-

The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

Cmdlet	Level	Description
Get-AzSqlServerFirewallRule	Server	Returns the current server-level firewall rules
New-AzSqlServerFirewallRule	Server	Creates a new server-level firewall rule
Set-AzSqlServerFirewallRule	Server	Updates the properties of an existing server-level firewall rule
Remove-AzSqlServerFirewallRule	Server	Removes server-level firewall rules

The following example sets a server-level IP firewall rule using PowerShell:

PowerShell

New-AzSqlServerFirewallRule -ResourceGroupName "myResourceGroup" `
    -ServerName $servername `
    -FirewallRuleName "AllowSome" -StartIpAddress "0.0.0.0" -EndIpAddress "0.0.0.0"

Manage server-level IP firewall rules using Azure CLI

Cmdlet	Level	Description
az sql server firewall-rule create	Server	Creates a server IP firewall rule
az sql server firewall-rule list	Server	Lists the IP firewall rules on a server
az sql server firewall-rule show	Server	Shows the detail of an IP firewall rule
az sql server firewall-rule update	Server	Updates an IP firewall rule
az sql server firewall-rule delete	Server	Deletes an IP firewall rule

The following example sets a server-level IP firewall rule using the Azure CLI:

Azure CLI

az sql server firewall-rule create --resource-group myResourceGroup --server $servername \
-n AllowYourIp --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

 Tip

For an Azure CLI example in the context of a quick start, see Create DB - Azure CLI and Create a single database and configure a SQL Database IP firewall rule using the Azure CLI

Manage server-level IP firewall rules using REST API

API	Level	Description
List Firewall Rules	Server	Displays the current server-level IP firewall rules
Create or Update Firewall Rule	Server	Creates or updates server-level IP firewall rules
Delete Firewall Rule	Server	Removes server-level IP firewall rules
Get Firewall Rules	Server	Gets server-level IP firewall rules

Server-level versus database-level IP firewall rules

Q. Should users of one database be fully isolated from another database? If yes, grant access using database-level IP firewall rules. This avoids using server-level IP firewall rules, which permit access through the firewall to all databases, reducing the depth of your defenses.

Q. Do users at the IP address’s need access to all databases? Use server-level IP firewall rules to reduce the number of times you must configure IP firewall rules.

Q. Does the person or team configuring the IP firewall rules only have access through the Azure portal, PowerShell, or the REST API? You must use server-level IP firewall rules. Database-level IP firewall rules can only be configured using Transact-SQL.

Q. Is the person or team configuring the IP firewall rules prohibited from having high-level permission at the database level? Use server-level IP firewall rules. Configuring database-level IP firewall rules using Transact-SQL, requires at least CONTROL DATABASE permission at the database level.

Q. Is the person or team configuring or auditing the IP firewall rules, centrally managing IP firewall rules for many (perhaps 100s) of databases? This selection depends upon your needs and environment. Server-level IP firewall rules might be easier to configure, but scripting can configure rules at the database-level. And even if you use server-level IP firewall rules, you might need to audit the database-level IP firewall rules, to see if users with CONTROL permission on the database have created database-level IP firewall rules.

Q. Can I use a mix of both server-level and database-level IP firewall rules? Yes. Some users, such as administrators might need server-level IP firewall rules. Other users, such as users of a database application, might need database-level IP firewall rules.

Troubleshooting the database firewall

Consider the following points when access to the Microsoft Azure SQL Database service does not behave as you expect:

• Local firewall configuration:
  Before your computer can access Azure SQL Database, you may need to create a firewall exception on your computer for TCP port 1433. If you are making connections inside the Azure cloud boundary, you may have to open additional ports. For more information, see the SQL Database: Outside vs inside section of Ports beyond 1433 for ADO.NET 4.5 and SQL Database.
• Network address translation (NAT):
  Due to NAT, the IP address used by your computer to connect to Azure SQL Database may be different than the IP address shown in your computer IP configuration settings. To view the IP address your computer is using to connect to Azure, log in to the portal and navigate to the Configure tab on the server that hosts your database. Under the Allowed IP Addresses section, the Current Client IP Address is displayed. Click Add to the Allowed IP Addresses to allow this computer to access the server.
• Changes to the allow list have not taken effect yet:
  There may be as much as a five-minute delay for changes to the Azure SQL Database firewall configuration to take effect.
• The login is not authorized or an incorrect password was used:
  If a login does not have permissions on the Azure SQL Database server or the password used is incorrect, the connection to the Azure SQL Database server is denied. Creating a firewall setting only provides clients with an opportunity to attempt connecting to your server; each client must provide the necessary security credentials. 
• Dynamic IP address:
  If you have an Internet connection with dynamic IP addressing and you are having trouble getting through the firewall, you could try one of the following solutions:
  • Ask your Internet Service Provider (ISP) for the IP address range assigned to your client computers that access the Azure SQL Database server, and then add the IP address range as an IP firewall rule.
  • Get static IP addressing instead for your client computers, and then add the IP addresses as IP firewall rules.