Configure server and database-level firewall rules
In this we will learn about server and database-level firewall rules.
Create a server-level IP firewall rule
For single and pooled databases, SQL Database builds a firewall at the server level. Unless you write an IP firewall rule to open the firewall, client apps will be unable to connect to the server or any of its databases. Create a firewall rule for a certain IP address or range of addresses that you want to be allowed to connect from outside Azure for a connection from an IP address outside Azure.
Create a server-level IP firewall rule for your client’s IP address by following these instructions. Enable external connection for your IP address solely through the Azure SQL Database firewall.
- After the database deployment complete. First, select SQL databases from the left-hand menu and then choose mySampleDatabase on the SQL databases page.
- Secondly, copy this fully qualified server name to use when connecting to your server and its databases in other quickstarts.
- Thirdly, select Set server firewall on the toolbar. The Firewall settings page for the server opens.
- Then, choose Add client IP on the toolbar to add your current IP address to a new server-level IP firewall rule. A server-level IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.
- Now, select Save. A server-level IP firewall rule is created for your current IP address opening port 1433 on the server.
- Lastly, close the Firewall settings page.
Database-level IP firewall rules
Clients can access specific (secure) databases thanks to database-level IP firewall restrictions. The rules for each database (including the master database) are created here and saved in the respective database.
- Firstly, you can only create and manage database-level IP firewall rules for master and user databases by using Transact-SQL statements and only after you configure the first server-level firewall.
- Secondly, if you specify an IP address range in the database-level IP firewall rule that’s outside the range in the server-level IP firewall rule. And, only those clients that have IP addresses in the database-level range can access the database.
- Lastly, you can have a maximum of 128 database-level IP firewall rules for a database.
Server-level versus database-level IP firewall rules
- Firstly, Should users of one database be fully isolate from another database?
- If so, authorize access using database-level IP firewall rules. This solution eliminates the use of server-level IP firewall rules, which allow access to all databases over the firewall. Your defenses’ depth would be reduced as a result.
- Secondly, do users at the IP addresses need access to all databases?
- If yes, use server-level IP firewall rules to reduce the number of times that you have to configure IP firewall rules.
- Next, does the person or team who configures the IP firewall rules only have access through the Azure portal, PowerShell, or the REST API?
- If so, you must use server-level IP firewall rules. Database-level IP firewall rules can only be configured through Transact-SQL.
- Is the person or team who configures the IP firewall rules prohibited from having high-level permission at the database level?
- If so, use server-level IP firewall rules. You need at least CONTROL DATABASE permission at the database level to configure database-level IP firewall rules through Transact-SQL.
- Lastly, Does the person or team who configures or audits the IP firewall rules centrally manage IP firewall rules for many (perhaps hundreds) of databases?
- Best practises are decided by your needs and surroundings in this case. While server-level IP firewall rules may be simpler to set up, scripting can set up rules at the database level. Even if server-level IP firewall rules are used, database-level IP firewall rules may need to be audited to verify if users with CONTROL access on the database set database-level IP firewall rules.
Connections from the internet
When a computer attempts to connect to your server over the internet, the firewall first compares the request’s originating IP address against the database-level IP firewall rules for the database in question.
- Firstly, if the address is within a range that’s specified in the database-level IP firewall rules, the connection is granted to the database that contains the rule.
- Secondly, if the address isn’t within a range in the database-level IP firewall rules, the firewall checks the server-level IP firewall rules. However, if the address is within a range that’s in the server-level IP firewall rules.
- Lastly, if the address isn’t within a range that’s in any of the database-level or server-level IP firewall rules, the connection request fails.
Permissions
You must be one of the following to build and maintain IP firewall rules for Azure SQL Server:
- Firstly, in the SQL Server Contributor role
- Secondly, in the SQL Security Manager role
- Lastly, the owner of the resource that contains the Azure SQL Server
Create and manage IP firewall rules
The first server-level firewall setting is created using the Azure portal, Azure PowerShell, Azure CLI, or an Azure REST API, or programmatically using Azure PowerShell, Azure CLI, or an Azure REST API. Using these technologies or Transact-SQL, you may build and manage extra server-level IP firewall rules.
Use the Azure portal to manage server-level IP firewall rules
In the Azure portal, create a server-level IP firewall rule. To begin, navigate to your database’s or server’s overview page.
From the database overview page
- Firstly, to set a server-level IP firewall rule from the database overview page, select Set server firewall on the toolbar, as the following image shows. Here, the Firewall settings page for the server opens.
- Secondly, select Add client IP on the toolbar to add the IP address of the computer that you’re using. And then select Save.
Troubleshoot the database firewall
Consider the following points when access to Azure SQL Database doesn’t behave as you expect.
- Firstly, 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. To make connections inside the Azure cloud boundary, you may have to open additional ports.
- Secondly, Network address translation. Because of network address translation (NAT), the IP address that’s used by your computer to connect to Azure SQL Database may be different than the IP address in your computer’s IP configuration settings.
- Thirdly, changes to the allow list haven’t taken effect yet. There may be up to a five-minute delay for changes to the Azure SQL Database firewall configuration to take effect.
- Then, the login isn’t authorized, or an incorrect password was used. If a login doesn’t have permissions on the server or the password is incorrect, the connection to the server is denied. Creating a firewall setting only gives clients an opportunity to try to connect to your server.
- Lastly, Dynamic IP address. If you have an internet connection that uses dynamic IP addressing and you have trouble getting through the firewall, try one of the following solutions:
- Firstly, ask your internet service provider for the IP address range that’s assigned to your client computers that access the server. Add that IP address range as an IP firewall rule.
- Then, get static IP addressing instead for your client computers. Add the IP addresses as IP firewall rules.
Reference: Microsoft Documentation, Documentation 2