Server-level firewall rule versus a database-level firewall rule and how to troubleshoot the database firewall in Azure
Q. Should users of one database be fully isolated from another database?
If yes, grant access using database-level firewall rules. This avoids using server-level 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 firewall rules to reduce the number of times you must configure firewall rules.
Q. Does the person or team configuring the firewall rules only have access through the Azure portal, PowerShell, or the REST API?
You must use server-level firewall rules. Database-level firewall rules can only be configured using Transact-SQL.
Q. Is the person or team configuring the firewall rules prohibited from having high-level permission at the database level?
Use server-level firewall rules. Configuring database-level 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 firewall rules, centrally managing firewall rules for many (perhaps 100s) of databases?
This selection depends upon your needs and environment. Server-level firewall rules might be easier to configure, but scripting can configure rules at the database-level. And even if you use server-level firewall rules, you might need to audit the database-firewall rules, to see if users with CONTROL permission on the database have created database-level firewall rules.
Q. Can I use a mix of both server-level and database-level firewall rules?
Yes. Some users, such as administrators might need server-level firewall rules. Other users, such as users of a database application, might need database-level 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. 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.
Q. Should users of one database be fully isolated from another database?
If yes, grant access using database-level firewall rules. This avoids using server-level 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 firewall rules to reduce the number of times you must configure firewall rules.
Q. Does the person or team configuring the firewall rules only have access through the Azure portal, PowerShell, or the REST API?
You must use server-level firewall rules. Database-level firewall rules can only be configured using Transact-SQL.
Q. Is the person or team configuring the firewall rules prohibited from having high-level permission at the database level?
Use server-level firewall rules. Configuring database-level 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 firewall rules, centrally managing firewall rules for many (perhaps 100s) of databases?
This selection depends upon your needs and environment. Server-level firewall rules might be easier to configure, but scripting can configure rules at the database-level. And even if you use server-level firewall rules, you might need to audit the database-firewall rules, to see if users with CONTROL permission on the database have created database-level firewall rules.
Q. Can I use a mix of both server-level and database-level firewall rules?
Yes. Some users, such as administrators might need server-level firewall rules. Other users, such as users of a database application, might need database-level 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. 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.
