Configure Azure SQL Database firewall rules - overview
Microsoft Azure SQL Database provides 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.
To configure your firewall, you create firewall rules that specify ranges of acceptable IP addresses. You can create firewall rules at the server and database levels.
- Server-level firewall rules: These rules enable clients to access your entire Azure SQL server, that is, all the databases within the same logical server. These rules are stored in the master database. Server-level firewall rules can be configured by using the portal or by using Transact-SQL statements.
- Database-level firewall rules: These rules enable clients to access individual databases within your Azure SQL Database server. You can create these rules for each database and they are stored in the individual databases. (You can create database-level firewall rules for the master database.) These rules can be helpful in restricting access to certain (secure) databases within the same logical server. Database-level firewall rules can only be configured by using Transact-SQL statements.
Recommendation: Microsoft recommends using database-level firewall rules whenever possible to enhance security and to make your database more portable. Use server-level 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.
Initially, all Transact-SQL access to your Azure SQL server is blocked by the firewall. To begin using your Azure SQL server, you must go to the Azure portal and specify one or more server-level firewall rules that enable access to your Azure SQL server. Use the 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 firewall rule that is beyond the IP address range specified in the server-level firewall rule, and ensure that the IP address of the client falls in the range specified in the database-level rule.
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.
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 full set of firewall rules:
If the IP address of the request is within one of the ranges specified in the server-level firewall rules, the connection is granted to your Azure SQL Database server.
If the IP address of the request is not within one of the ranges specified in the server-level firewall rule, the database-level firewall rules are checked. If the IP address of the request is within one of the ranges specified in the database-level firewall rules, the connection is granted only to the database that has a matching database-level rule.
If the IP address of the request is not within the ranges specified in any of the server-level or database-level firewall rules, the connection request fails.
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.
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 these connections are allowed. If the connection attempt is not allowed, the request does not reach the Azure SQL Database server.
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.
You can enable connections from Azure in two ways:
In the Microsoft Azure portal, select the checkbox Allow azure services to access server when creating a new server.
In the Classic portal, from the Configure tab on a server, under the Allowed Services section, click Yes for Microsoft Azure Services.
The first server-level firewall setting can be created using the Azure portal or programmatically using the REST API or Azure PowerShell. Subsequent server-level firewall rules can be created and managed using these methods, as well as through Transact-SQL. To improve performance, server-level firewall rules are temporarily cached at the database level. To refresh the cache, see DBCC FLUSHAUTHCACHE. For more information on server-level firewall rules, see How to: Configure an Azure SQL server firewall using the Azure portal.
After you have configured the first server-level firewall, you may want to restrict access to certain databases. If you specify an IP address range in the database-level firewall rule that is outside the range specified in the server-level 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 firewall rules for a database. Database-level firewall rules for master and user databases can be created and managed through Transact-SQL. For more information on configuring database-level firewall rules, see sp_set_database_firewall_rule (Azure SQL Databases).
In addition to the Azure portal, firewall rules can be managed programmatically using Transact-SQL, REST API, and Azure PowerShell. The following tables describe the set of commands available for each method.
|Catalog View or Stored Procedure||Level||Description|
|sys.firewall_rules||Server||Displays the current server-level firewall rules|
|sp_set_firewall_rule||Server||Creates or updates server-level firewall rules|
|sp_delete_firewall_rule||Server||Removes server-level firewall rules|
|sys.database_firewall_rules||Database||Displays the current database-level firewall rules|
|sp_set_database_firewall_rule||Database||Creates or updates the database-level firewall rules|
|sp_delete_database_firewall_rule||Databases||Removes database-level firewall rules|
|List Firewall Rules||Server||Displays the current server-level firewall rules|
|Create Firewall Rule||Server||Creates or updates server-level firewall rules|
|Set Firewall Rule||Server||Updates the properties of an existing server-level firewall rule|
|Delete Firewall Rule||Server||Removes server-level firewall rules|
|Get-AzureSqlDatabaseServerFirewallRule||Server||Returns the current server-level firewall rules|
|New-AzureSqlDatabaseServerFirewallRule||Server||Creates a new server-level firewall rule|
|Set-AzureSqlDatabaseServerFirewallRule||Server||Updates the properties of an existing server-level firewall rule|
|Remove-AzureSqlDatabaseServerFirewallRule||Server||Removes server-level firewall rules|
There can be up as much as a five-minute delay for changes to the firewall settings to take effect.
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 V12 of SQL Database: Outside vs inside section of Ports beyond 1433 for ADO.NET 4.5 and SQL Database V12.
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. For more information about preparing logins, see Managing Databases, Logins, and Users in Azure SQL Database.
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 a firewall rule.
- Get static IP addressing instead for your client computers, and then add the IP addresses as firewall rules.
For how to articles on creating server-level and database-level firewall rules, see:
- Configure Azure SQL Database server-level firewall rules using the Azure portal
- Configure Azure SQL Database server-level and database-level firewall rules using T-SQL
- Configure Azure SQL Database server-level firewall rules using PowerShell
- Configure Azure SQL Database server-level firewall rules using the REST API
For a tutorial on creating a database, see Create a SQL database in minutes using the Azure portal. For help in connecting to an Azure SQL database from open source or third-party applications, see Client quick-start code samples to SQL Database. To understand how to navigate to databases, see Manage database access and login security.