Rajendra Gupta
Azure firewall rule

Configure IP firewall rules for Azure SQL Databases

March 18, 2022 by

The Azure SQL Database provides high performance, secure and reliable managed relational database service. Security is the prime focus area in both on-premises and cloud infrastructure while designing the database infrastructure.

You can safeguard your database using various ways:

  • Firewalls
  • authentication
  • authorization
  • Permissions
  • Encryption

This article explores the IP firewall rules that restrict connections to the Azure SQL Database.

IP firewall overview

Azure has two kinds of firewalls for Azure SQL Databases.

  • Server level firewall
  • Database level firewall

Let’s explore these firewalls one by one.

Server level firewall

The server-level firewall rules are applicable for Azure SQL Server. Therefore, it is applicable for all underlying Azure SQL Databases. Each connection must pass through the firewall before it can reach out to the Azure server or database.

By default, once we create a new Azure logical SQL Server, the server-level firewall blocks all connections to the public endpoint. The public endpoint is the FQDN of the Azure server in the form of [AzureServer].database.windows.net.

Azure stores the firewall rules in the master database. It allows a maximum of 128 server-level firewall rules for an Azure server. If you enable the option – Allow Azure Services and resources to access this server, it is considered a single server firewall rule.

To configure the server-level firewall rule, you can use Azure Portal, Azure CLI, Azure PowerShell or T-SQL statements.

Set up server-level firewall rules using Azure Portal

To configure the server-level firewall rules, connect to Azure portal and navigate to Azure SQL Database dashboard. Here, you get an option to set the server firewall, as shown below.

server firewall

Click on the Set Server Firewall to get the following options.

  • Add client IP: You can use this option to add the current client IP in the allowed IP that connects to Azure SQL Server. You can check the public IP address in the section – Client IP address.
  • You can also specify the Rule name and IP address range in the Start IP and End IP section.

Add Client IP

Once you click on Add client IP, it creates a rule with the same start and End IP address.

Start and End IP address

Azure SQL Server allows DB to connect to default port 1433 for a single IP address or a range of IP addresses.

Set up server-level firewall rules using T-SQL

You can execute the stored procedure sp_set_firewall_rule in the Azure SQL Server master database. For example, the following T-SQL configures server-level rule named – Allow DB Connections for the IP address 10.0.0.2

Note: To run this stored procedure, you must connect with the master database using client tools such as SSMS, Azure Data Studio or SQLCMD.

Database-level IP firewall rules

The database firewall rules are specific to a specific Azure SQL Database. For example, if you have three Azure DB’s, you need to configure them individually.

  • The database firewall rules can be configured for the master or user database.
  • To configure these firewall rules, you need to configure the first server-level firewall rule.
  • The T-SQL stored procedure sp_set_database_firewall_rule can create the database firewall rule.
  • The database-level firewall overrides the server-level firewall for the specific database. For example, suppose you have an IP address in the database-level firewall but not in the server-level firewall. In this case, the client has an IP address in the database firewall that can query the database.

The following T-SQL statement sets a database level firewall rule for the IP address 10.0.0.4. You need to run this under the database security content where you require the database firewall rule.

How are the Server level or database level rules applied?

The following diagram shows that any connection request to the Azure database must be met with the firewall.

  • Initially, Azure checks the database firewall rule. If the client IP is within the database firewall rule, it gets direct access to the database
  • If there is no database-level firewall or client IP is not allowed in DB firewall, Azure checks the Server level firewall rule
    • If the client IP address falls in the server-level firewall IP range, the connection is allowed
    • The connection is rejected if the client IP address is not specified in the server-level firewall IP address range

Server level or database level rules

Image reference: Microsoft

Note: The Server or database-level firewall restricts the client connections to Azure SQL Databases. However, you still require authentication using SQL or Azure Active Directory Authentication.

Create Azure firewall rule during SSMS connection

Suppose you connect from a client whose IP address is not allowed in server or database level firewalls. In this case, the SSMS gives a prompt, as shown below.

Azure firewall rule

Click on Sign In to authenticate yourself with the Azure account and add a new firewall rule. You can add either the client IP address or the subnet IP address range.

Add IP or subnet range

Click OK, and you can connect to Azure SQL Database as shown below.

SSMS connection to Azure SQL Database

You might have a question – The firewall created during SSMS connection is a server or database-level firewall rule. To identify the view the existing server firewall rule in the Azure portal. As shown below, it is a server rule. Therefore, you should configure a database firewall rule separately using the SP specified previously.

View existing rule

Viewing existing firewall rules using T-SQL

You can view the existing firewall rules using the T-SQL statement as well. Let’s explore these queries.

  • sys.firewall_rules: It displays the server-level IP firewall rules. You can query it in the master database.

server-level IP firewall rules

  • sys.database_firewall_rules: It displays the database IP firewall rule. You need to execute the script on the database in which you want to view the DB firewall rule.

database IP firewall

Currently, it gives zero rows because I have not configured a database level firewall. Suppose your client having IP address 10.0.0.4 requires DB firewall access. To allow him connection, you create a database firewall rule using the following SP.

Note: You need to use the client allowed in the server firewall to connect and create a database firewall.

You can re-execute the select statement to view the configured database firewall rules.

Configuring database firewall rules

Delete a database firewall rule

You can delete the database firewall rule only from the T-SQL script stored procedure sp_delete_database_firewall_rule. The following script deletes the DB firewall rule named “Example DB Rule.”

Delete a database firewall rule

Delete a server firewall rule

You can remove the server firewall rule using the Azure portal or the T-SQL statement. For the demo, I will remove the server-level firewall rule – ClientIPAddress_2021-10-04_10:08:35. To remove this rule, you can run the stored procedure sp_delete_firewall_rule on the master database.

Few important points regarding Azure SQL Database firewalls

  • It is recommended that you implement a database firewall rule instead of a server firewall rule for client connections. It enhances security by restricting clients from having access to all databases in an Azure SQL Server.
  • If the clients use dynamic IP addresses, they may face issues in DB connections. In this case, you may do one of the following
    • Configure IP subnet range that allows connections for all IP addresses in that range
    • Use static IP address and add that static IP in the firewalls
  • Most of the time, changes to Azure firewalls are instantaneous. However, it may take up to five minutes for changes to be effective
  • Due to network address translation (NAT), your computer might use a different IP address for Azure DB connection than the IP address returned by ipconfig. In this case, you can either use the Azure portal to view the current public IP address or use the portal https://www.whatismyip.com/
  • The Azure server or database level firewall allows the client IP address to connect Azure SQL Databases. However, your request might not reach the Azure boundary for connection if your client has a local firewall. In this case, you must allow port 1433 in the local firewall as well.

Conclusion

This article explores the configuration of server and database level firewalls for Azure SQL Database. The firewalls are essentials for allowing only authorized clients to connect to databases. You can explore both server and database firewall rules and configure them as per your requirement. Please do not allow a broad range of IP addresses (such as 0.0.0.0) as it might not serve as the firewall to safeguard database access.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure
  2. Configuring the Azure SQL Database Firewall
  3. How to prepare for the Exam AZ-900: Microsoft Azure Fundamentals
  4. How to prepare for the Exam AZ-104: Microsoft Azure Administrator
  5. How to prepare for the Exam DP-200: Implementing an Azure Data Solution
168 Views