Azure SQL database configure firewall

In this Azure SQL tutorial, we will discuss the Azure SQL database firewall and how to configure it. We will discuss various methods to configure an Azure SQL database firewall.

The Azure SQL firewall is a layer of security added to prevent any types of malicious requests. You can allow or disallow any IP address or any range of IP addresses to connect to your Azure SQL database.

For this, you have to create some firewall rules at different levels. In the Azure SQL database, you have two types of firewalls:

1. Server-level firewall
2. Database-level firewall

• Azure SQL server-level firewall
• Deploy and configure Azure firewall using the Azure portal
• Azure SQL database sp_set_firewall_rule
• Configure Azure SQL firewall using CLI
• Configure Azure SQL firewall using Powershell
• Configure Azure SQL firewall using REST APIs
• Azure SQL database-level firewall
• Configure database-level firewall in Azure SQL database
• Azure SQL server-level firewall vs database-level firewall

Azure SQL server-level firewall

The server-level firewall in the Azure SQL database is implemented on all the databases created under a server. You can create a server-level firewall rule when you want to create a rule for all the databases.

You can create a maximum of 128 server-level firewall rules for a single server.

These firewall rules are stored in the master database. You can create, view, or manage these firewall rules in multiple ways. In the upcoming sections, you will see various ways to manage the server firewall.

Deploy and configure Azure firewall using the Azure portal

When you create an Azure SQL database, a firewall is created at that time. You can create firewall rules at that time also. However, if you did not specify the desired firewall rules, you can specify them later.

To connect to your Azure SQL database, you need to create a firewall rule i.e. you have to specify your IP address in the firewall. If you want to configure the server-level firewall using the Azure portal, you can follow the below steps:

• Open the Azure portal and navigate to your database.
• In the database Overview, click on Set server firewall.

• After that, you can manage your firewall settings.
• Here you can view the existing firewall settings and a list of firewall rules created.
• You can choose the TLS version to use.
• The connection policy specifies how you want to connect to yur database. Either you want to connect directly or use any proxy server between the endpoints.
• To create a new firewall rule, enter the Rule name, Start IP, End IP in the input fields.
• If you want to allow only a single IP address to connect, you can specify the same IP address in both the Start IP and the End IP input fields.
• Otherwise, you can also specify the range of IP addresses between these two fields.
• The current Client IP address is also shown in the firewall settings. You can click on Add client IP to create a new firewall rule for the current client IP address.

• You can also create a virtual network in the firewall settings.
• Once you have specified the client IP addresses or range, click on Save to save the rules.
• Now you will be able to connect to your Azure SQL database.
• To delete a firewall rule, right click on the firewall rule and click on Delete.

Thus, you might have learned how you can configure the server-level firewall of your Azure SQL database server using the Azure portal.

Read Pause and resume Azure SQL database

Azure SQL database sp_set_firewall_rule

You can also manage the Azure SQL Database server-level firewall rules using some system stored procedures. One of them is sp_set_firewall_rule.

Using this system stored procedure, you can create a new server-level firewall rule or update an existing one.

Permissions: You need to have enough permissions to execute this stored procedure. Either you must be a server-level admin or an active directory administrator.

You also need to connect to your Azure SQL database using any IDE like the Azure data studio or SQL Server management studio.

Because this query will be run on the master database and we cannot run queries on the master database from the Query editor in the Azure portal.

EXECUTE sp_set_firewall_rule
   @name = <name of the rule>,
   @start_ip_address = ,start IP address of the range>,
   @end_ip_address = <end IP address of the range>

For example:

EXECUTE sp_set_firewall_rule @name = N'MyIPRule',
   @start_ip_address = '192.167.1.1', @end_ip_address = '192.167.1.50'

You can verify in the Azure portal firewall settings that the new firewall rule is created.

To delete a firewall rule you can use the sp_delete_firewall_rule.

For example, if we want to delete the firewall rule that we created above with the name MyIPRule, we will write the query as:

EXEC sp_delete_firewall_rule @name = N'MyIPRule'

To view the existing firewall rules, you can query the sys.firewall_rules system catalog view as:

SELECT * FROM sys.firewall_rules ORDER BY name

Thus, you might have learned how you can configure the Azure SQL database server-level firewall rules using T-SQL queries.

Read How to rename a database in Azure SQL

Configure Azure SQL database firewall using CLI

In Microsoft Azure, there is an option to manage the resources through a command-line interface in order to automate the workflow. The Azure CLI is a great option for executing scripts to manage your resources.

One of the tasks that you can do with the Azure CLI is managing the Azure SQL database firewall. In this section, you will learn how you can manage the Azure SQL database firewall.

Navigate to the link https://shell.azure.com in your browser and log in with your Microsoft account to open the Azure CLI. Make sure the terminal mode is set to Bash.

Now you are ready to execute commands on CLI. Let us see some common examples of managing the firewall.

Create a server-level firewall using Azure CLI

To create a server-level firewall rule, we will use the az sql server firewall-rule create command. The general syntax of this command is:

az sql server firewall-rule create 
	--name <firewall rule name>
	--resource-group <resource group name>
	--server <server name>
 	--start-ip-address <first IP address of the allowed IP range>
	--end-ip-address <last IP address of the allowed IP range>
	--subscription <name of your Microsoft Subscription>

Let us see an example of this command. We will create a new server-level firewall rule.

az sql server firewall-rule create --name MyFirewall --resource-group GroupAzure --server mysql1000 --start-ip-address 192.168.0.0 --end-ip-address 192.168.0.255

The above command will create a new server-level firewall rule named MyFirewall on a server named mysql1000 belonging to the GroupAzure resource group that will allow incoming connections from the IP address range 192.168.0.0 – 192.168.0.255.

Once you have successfully executed this command, you will see an output like in the below image:

You can also enable the firewall rule that allows all the Azure resources to access your Azure SQL database server. For this, you have to execute the below command in the Azure CLI:

az sql server firewall-rule create --resource-group GroupAzure --server mysql1000 --name AllowAllAzureIPs --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Hence, in this way you can create server-level firewall rules using Azure CLI.

Read How to create table in azure sql database

View server-level firewall rules using CLI

In this section, you will learn how you can view the server-level firewall rules using the Azure CLI.

To view a list of server-level firewall rules of a server, you can use the az sql server firewall-rule list command. The general syntax of this command is:

az sql server firewall-rule list 
	--ids <complete resource ID of the firewall rule>
	--resource-group <resource group name>
	--server <server name>	
	--subscription <subscription name or id>

For example, to get a list of all server-level firewall rules, you can execute the below command in Azure CLI:

az sql server firewall-rule list --resource-group GroupAzure --server mysql1000

To view a particular server-level firewall rule, you can use the az sql server firewall-rule show command. The general syntax for using this command is:

az sql server firewall-rule show
	--ids <resource id of firewall rules>
        --name < name of the firewall rule>
        --resource-group <resource group name>
        --server <server name>
        --subscription <subscription name or id>

For example:

az sql server firewall-rule show --name MyFirewall --server mysql1000 --resource-group GroupAzure

Hence, you might have learned how you can view the existing server-level firewall rules using Azure CLI.

Read Azure SQL database column encryption

Update server-level firewall rule using CLI

In case you want to modify or update a server-level firewall rule you can use the az sql server firewall-rule update command in Azure CLI. The general syntax for using this command is:

az sql server firewall-rule update 
	--ids <resource ID>
        --name <name of the firewall rule>
        --resource-group <name of the resource group>
        --server <server name>
        --start-ip-address <first IP address of the new IP address      range>
	--end-ip-address <last IP address of the new IP address range>
        --subscription <subscription name or ID>

For example, we have created a server-level firewall rule named MyFirewall. Suppose we want to extend the IP address range specified that can access the database. We will execute the command as:

az sql server firewall-rule update --name MyFirewall --resource-group GroupAzure --server mysql1000 --start-ip-address 192.168.0.0 --end-ip-address 192.168.10.255

You can see in the above image that the IP address range is changed.

Delete a server-level firewall using CLI

To delete a server-level firewall rule using CLI, you can use the az sql server firewall-rule delete command. The general syntax of this command is:

az sql server firewall-rule delete
	--ids <resource id>
        --name < name of the firewall rule>
        --resource-group <resource group name>
        --server <server name>
        --subscription <subscription name or id>

For example, if we want to delete the firewall rule that we created above with the name MyFirewall, we will execute the below command in Azure CLI:

az sql server firewall-rule delete --name MyFirewall --resource-group GroupAzure --server mysql1000

Thus, you might have learned how you can manage the server-level firewall rules using the Azure CLI.

Read Configuring email notifications in Azure SQL database

Configure Azure SQL database firewall using Powershell

You can also manage the server-level firewall rules using Powershell. This helps to automate your tasks using scripts.

If you want to use Windows PowerShell on your local machine, you need to install a module named Az on your PowerShell. This module contains libraries that help to manage your Azure resources like servers, databases, and VMs.

Also, you will need to connect to your Azure account. You can execute the below command to connect to your Azure account. You will be asked to log in to your Azure account.

Connect-AzAccount

However, you can also use the Azure PowerShell by visiting the URL http://shell.azure.com in your browser.

In Azure PowerShell, all the required modules are already installed. You just need to log in and execute the commands.

Create a server-level firewall rule using PowerShell

To create a server-level firewall rule you can use the New-AzSqlServerFirewallRule cmdlet. Below is the syntax for creating a new server-level firewall rule:

New-AzSqlServerFirewallRule '
    -ResourceGroupName <your resource group name> `
    -ServerName <server name> `
    -FirewallRuleName <name of the new firewall rule> `
    -StartIpAddress <first IP address of the IP range> `
    -EndIpAddress <last IP address of the IP range> 
    

There are other optional parameters also. But these are some commonly used parameters.

Note:

Both the starting and the ending IP addresses of the IP range specified are both included in the IP range.

For example, to create a new server-level firewall rule, you can execute a command similar to the command below.

The below PowerShell command will create a new server-level firewall rule named Allowed IP range that allows the incoming connection requests to the server mysql1000 from machines having the IP address between the inclusive range 192.168.1.1 – 192.168.10.255.

New-AzSqlServerFirewallRule 
    -ResourceGroupName "GroupAzure" `
    -ServerName "mysql1000" 
    -FirewallRuleName "Allowed IP range" 
    -StartIpAddress "192.168.1.1" 
    -EndIpAddress "192.168.10.255" 

Once the command is executed successfully, you will see an output like the below screen.

You can also verify in the Azure portal if the firewall rule is created or not.

Alow all Azure IPs using PowerShell

You can also create a firewall rule that allows all the Azure IPs to access the Azure SQL database server with the same New-AzSqlServerFirewallRule command. For example:

New-AzSqlServerFirewallRule -ResourceGroupName "GroupAzure" -ServerName "mysql1000" -AllowAllAzureIPs

In this case, you do not need to specify the range of IP addresses. The Azure IP addresses will be able to connect to the Azure SQL database server.

Read Create stored procedures in Azure SQL database

Azure SQL database view firewall rules using PowerShell

To view an existing server-level firewall rule or the list of all server-level firewall rules, you can use the Get-AzSqlServerFirewallRule cmdlet. The general syntax of this cmdlet is:

Get-AzSqlServerFirewallRule
   -FirewallRuleName <name of the firewall rule>
   -ServerName <name of the server>
   -ResourceGroupName <Resource group name>

For example, if you want to get a list of all the server-level firewall rules of a server named mysql1000, belonging to the resource group GroupAzure, you will execute the following command in PowerShell:

Get-AzSqlServerFirewallRule -ResourceGroupName "GroupAzure" -ServerName "mysql1000"

You can also use this command in some other ways. For example, if you want to view a particular firewall rule, you can define its name in the command as follows:

Get-AzSqlServerFirewallRule -ResourceGroupName "GroupAzure" -ServerName "mysql1000" -FirewallRuleName "Allowed IP range"

In this way, you can use view the existing server-level firewall rules of an Azure SQL database.

Read Azure sql password validation failed

Update a server-level firewall rule using PowerShell

You may face some scenarios when you want to modify the IP address range that can access your Azure SQL database server. In such a case, you can use the Set-AzSqlServerFirewallRule cmdlet.

The general syntax for this cmdlet is:

Set-AzSqlServerFirewallRule 
   -FirewallRuleName <name of the firewall rule>
   -StartIpAddress <starting IP address of the new range>
   -EndIpAddress <ending IP address of the new range>
   -ServerName <name of the server>
   -ResourceGroupName <name of the resource group>

For example, we created a server-level firewall rule named Allowed IP range with the IP range 192.168.1.1 – 192.168.10.255.

Now, we will modify this IP address range with the following PowerShell command:

Set-AzSqlServerFirewallRule ` 
   -FirewallRuleName "Allowed IP range" `
   -StartIpAddress "192.168.1.1" `
   -EndIpAddress "192.168.255.255" `
   -ServerName "mysql1000" `
   -ResourceGroupName "GroupAzure"

If the command is executed successfully, you will see an output like the below image:

In this way, you can update an existing server-level firewall rule in an Azure SQL database server.

Read Migrate SQL Server to Azure SQL database

Azure SQL database delete server-level firewall rule using PowerShell

To delete an existing server-level firewall rule, you can use the Remove-AzSqlServerFirewallRule cmdlet. The general syntax for using this command is:

Remove-AzSqlServerFirewallRule `
   -FirewallRuleName "Allowed IP range" `
   -Force <if specified, deletes the firewall rule withtout any user confirmation> `
   -ServerName "mysql1000" `
   -ResourceGroupName "GroupAzure"

For example, we will delete the above created server-level firewall rule named Allowed IP range by executing the below PowerShell command:

Remove-AzSqlServerFirewallRule `
   -FirewallRuleName "Allowed IP range" `
   -ServerName "mysql1000" `
   -ResourceGroupName "GroupAzure"

You can verify in the Azure portal or by executing the Get-AzSqlServerFirewallRule cmdlet in PowerShell whether the firewall rule is deleted or not.

Thus, you might have learned how you can manage the server-level firewall of an Azure SQL Database using PowerShell.

Read How to create SQL authentication user in Azure SQL database

Configure Azure SQL database firewall using REST APIs

You can also integrate the functionality in your application to manage the Azure SQL Database server-level firewall rules using the REST APIs.

For this, you have to register your application with Microsoft to generate an authorization token called the OAuth token. You will need this token to authenticate yourself while dealing with the Microsoft Azure APIs.

Once you have the Authorization token with yourself, you can configure the Azure SQL database firewall in various ways like creating, viewing, and deleting the firewall rules.

In the upcoming sections, I will create some examples of configuring the Azure SQL database firewall. I will be using Postman for this demonstration purpose.

Create a server-level firewall rule using REST APIs

To create a server-level firewall rule using REST APIs you will need to send a PUT request to the Azure API endpoint. This API endpoint will be:

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/firewallRules/{firewallRuleName}?api-version=2021-02-01-preview

In the above URL, you have to change the parameters according to your configuration. For example, you have to change the subscription ID, resource group name, server name, and firewall rule name.

Secondly, in the request body, you have to pass some more parameters in the JSON format. These parameters are the starting IP addressing and the ending IP address of the allowed IP address range. The body of the request will look like this:

{
  "properties": {
    "startIpAddress": <start IP address,
    "endIpAddress": <end IP address>
  }
}

Now let us see an example. I will send a PUT request to the Azure API endpoint for creating a new firewall rule in the Postman app.

My URL for creating the firewall rule on my server is:

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql1000/firewallRules/FirewallrestAPI?api-version=2021-02-01-preview

Secondly, I will specify two request headers:

1. Content-type: application/json
2. Authorization: <my authorization token>

Finally, I will use specify the request body as :

{
  "properties": {
    "startIpAddress": "192.168.10.1",
    "endIpAddress": "192.168.10.255"
  }
}

The status code 200 shows that the response is OK and our request is successful.

To update an existing server-level firewall rule, you will use this same process. Just replace the starting and the ending IP address in the request body.

Thus, you might have learned how you can create or update a server-level firewall rule using the REST APIs.

View server-level firewall rules using Rest APIs

To view the server-level firewall rules, you will need to send a GET request to the following URL:

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/firewallRules?api-version=2021-02-01-preview

You just have to change a few parameters and add your own according to your server details. Also, specify the two headers i.e. Content-type and Authorization. Keep in mind that a GET request does not have a body.

For example, in my case, the URL will look like this.

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql1000/firewallRules?api-version=2021-02-01-preview

Similarly, to view a particular firewall rule, you can define the name of the firewall rule in the above URL as:

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql1000/firewallRules/MyFirewall?api-version=2021-02-01-preview

Read Reset Password Azure SQL database

Delete a server-level firewall rule using the REST APIs

To delete a server-level firewall rule, the requirements are the same as described in the above sections.

The only difference is that we will send a DELETE request to the same URL and specify the firewall rule name in the URL.

For example, a DELETE request to the following URL will delete the firewall rule named MyRule on the server mysql1000:

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql1000/firewallRules/MyRule?api-version=2021-02-01-preview

In this way, you can delete a server-level firewall rule in Azure SQL.

Thus, you might have learned how you can configure the server-level firewall using the REST APIs.

Read Cannot open backup device operating system error 50 azure

Azure SQL database-level firewall

As the name suggests, the Azure SQl database-level firewall rules are created at the database level i.e. these are stored in the database. You can create a maximum of 128 firewall rules in a database.

The only way to manage database-level firewall rules is using the T-SQL queries. In the next section, you will see how to use T-SQL queries to configure the database-level firewall rules.

Configure database-level firewall in Azure SQL database

As mentioned above, the only way to configure the database-level firewall rules is to use T-SQL queries. There are some views and stored procedures that you can use to view, create, and delete the database-level firewall rules.

If you want to create database-level firewall rules on the master database, you must use an IDE to connect to the database. Otherwise, you can execute the queries in the Azure portal using the Query Editor.

Create a database-level firewall rule

To create a database-level firewall rule, you can use the sp_set_database_firewall_rule system stored procedure. The general syntax of this stored procedure is:

sp_set_database_firewall_rule @name =  <firewall rule name>  
, @start_ip_address = <first IP address of the allowed IP range>
, @end_ip_address =] <last IP address of the allowed IP range>

Let us see an example. I will create a new database-level firewall rule. For this, I will execute the below query in the Query Editor for my database in the Azure portal:

sp_set_database_firewall_rule @name =  N'Database Firewall Rule' 
, @start_ip_address = '192.168.10.1'
, @end_ip_address = '192.168.10.255'

View database-level firewall rules

After creating the firewall rule, you may want to view the firewall rule in your database.

There is a system-defined view named sys.database_firewall_rules that stores the information about the database-level firewall rules. You can query this view to get the list of all database-level firewall rules. For example:

SELECT * FROM sys.database_firewall_rules ORDER BY name;

Delete a database-level firewall rule

To delete a database-level firewall rule, you can use the sp_delete_database_firewall_rule system stored procedure. This system stored procedure takes only on argument i.e. firewall rule name.

For example, if I want to delete the firewall rule that I created above, I will execute the following query on the database:

sp_delete_database_firewall_rule @name = 'Database Firewal Rule'

The number of affected rows is 1. This means that the database-firewall rule is successfully deleted.

Thus, you might have learned how to configure the database-level firewall rules in an Azure SQL database.

• Cannot bulk load because the file could not be opened. operating system error code (null). azure

Azure SQL server-level firewall vs database-level firewall

You may think that what is the need for two types of firewalls, what are the benefits of one over the other when to use which type of firewall rule. In this section, we will compare both types of firewalls.

Precedence: When a client sends the request to an Azure SQL database, first the client IP address is checked in the database-level firewall rules list.

If the client’s IP address lies in the allowed IP range, the client is served, otherwise, the IP address is checked in the server-level firewall. If it is not available even in the server-lever firewall, the request is failed.

Ways to configure: You can configure the server-level firewalls using the Azure portal, PowerShell, T-SQL, CLI, and even REST APIs. But to configure the database-level firewall rules, you can only use the T-SQL queries.

Scope: The database-level firewall rules are stored at the database level and have access only to the database in which they are created. Thus, it makes the database more portable. However, server-level firewall rules have access to all the databases under the server on which the rules are created.

Preference: The preference totally depends on your system configuration and requirements.

Database-level firewall rules should always be preferred if possible. But, if there are a large number of databases, it becomes difficult to manage firewall rules on every database.

Therefore, if you want to give a client’s IP address access to a large number of databases, you should prefer to use server-level firewall rules over database-level firewall rules.

Hope this article helped you to learn about the firewall and its management in an Azure SQL database.

• Azure SQL server-level firewall
• Deploy and configure Azure firewall using the Azure portal
• Azure SQL database sp_set_firewall_rule
• Configure Azure SQL database firewall using CLI
• Configure Azure SQL database firewall using Powershell
• Configure Azure SQL database firewall using REST APIs
• Azure SQL database-level firewall
• Configure database-level firewall in Azure SQL database
• Azure SQL server-level firewall vs database-level firewall