Share via

Azure SQL Database: Firewall security

  • Article

Microsoft Azure SQL Database provides a relational database service for Azure and other Internet-based applications. To help protect your data, the Azure SQL Database firewall prevents access to the Azure SQL Database server until you specify which computers have permission.

To connect to your Azure SQL Database server for the first time, you must enable connectivity through the firewall using the management portal. The firewall restricts incoming traffic to TCP port 1433 only. In addition, by default, all external (from the Azure point of view) connections are blocked, so you need to explicitly enable them by specifying the public IP address (or IP address range) assigned to your Internet entry point.

To configure the firewall, we have to create firewall rules that specify ranges of acceptable IP addresses.  We can create firewall rules at the server and database levels.

Server-level firewall rules: These rules enable clients to access your entire Azure SQL Database server, that is, all the databases within the same logical server. These rules are stored in the master database. You create the server-level firewall rules using the Azure Platform Management Portal or programmatically using the master Database.

Return to Top

Configure Server-Level Firewall Settings:

 Using the Management Portal

  1. Log on to the Azure Management Portal (https://windows.azure.com).
  2. Expand subscriptions, choose your subscription, expand it and select your server.
  3. In the center pane, click on the "Firewall Rules" box.
  4. To configure the server-level firewall settings:
    1. Enable connection attempts from Azure by selecting the Allow other Azure services to access to this server check box. This will add a firewall rule with the start and end IP range values set to 0.0.0.0.
    2. Add a new server-level firewall setting for Internet-based connections by clicking Add. In the Add Firewall Rule dialog box, specify a unique name in the Rule Name box with the corresponding IP address range in the IP range start and IP range end boxes. Click OK.
  5. We are done with the Firewall settings.

Return to Top 

Using  Master or User Database:
  1. Follow the above 3 steps and expand the server to see the databases.
  2. Select MASTER and click on MANAGE on the ribbon above.
  3. Connect to the master database of the Azure SQL Database server using your server-level principal login.
  4. View the server-level firewall settings corresponding to your Azure SQL Database server by executing the query:

select * from sys.firewall_rules

  1. Configure the server-level firewall settings by using the sp_set_firewall_rule stored procedure. Follow below steps

a.  Enable connection attempts from Azure by using the sp_set_firewall_rule stored procedure with the parameters start_ip_address and end_ip_address equal to 0.0.0.0.

 

              Ex: exec sp_set_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0'

 

 

b.  Add a new firewall setting for Internet-based connections by specifying a unique name in the name parameter of the sp_set_firewall_rule stored procedure.

 

                Ex: exec sp_set_firewall_rule N'Example setting 1', 'YOUR IP ADDRESS','YOUR IP ADDRESS'

 

  1. Specify the lowest desired IP address in that range with the start_ip_address parameter and the highest desired IP address in that range with the end_ip_address parameter. The name parameter is of the nvarchar data type and the start_ip_address and the end_ip_address parameters are of the varchar data type.

 

Removing the Firewall Setting: Use below store procedure

 

exec sp_delete_firewall_rule N'Example setting 1'

Configure Database-Level Firewall Settings:

This is quite similar with the Server Level Firewall setting using Master DB

  1. Connect to the database for which you want to create a database-level firewall rule.
  2. View the database-level firewall settings for the database by executing the following query:

select * from sys.database_firewall_rules

  1. Create a database-level firewall rule by using the sp_set_database_firewall_rule stored procedure. Add a new firewall setting for Internet-based connections by using the stored procedure sp_set_database_firewall_rule stored procedure. Refer above to pass the parameters of the procedure.

 Ex: exec sp_set_database_firewall_rule N'Example DB Setting 1','0.0.0.0','0.0.0.0'

 

Removing the Firewall Setting: Use below store procedure

 

   exec sp_delete_database_firewall_rule N'Example DB Setting 1'

 

Return to Top