Azure Sql Firewall Configuration

In this post I will show you how to configure firewall settings for Azure SQL server in order to connect from your local development machine or from some remote machine where your application is hosted. More or less, the process is same.

What is firewall, in azure's case IP firewall

Technically speaking, firewall is a software program which prevents unauthorized access to your system. We can think of firewall as a bouncer waiting outside of the midnight party club. The bouncer only allows the spectators to pass through the club's door whoever has the pass/invitation to join the party.  Pretty cool, huh. As network traffic passes through the firewall, the firewall decides which traffic to forward and which traffic not to forward, based on rules that we have defined.

In azure SQL we have to add some firewall rules for allowing trusted users to pass through the wall of winterfell. In azure world , the users who can have access to SQL server are called clients. By allowing specific clients we are revoking anonymous access to SQL server as well as SQL DB.
There are 2 kinds of firewall rules for azure SQL

1. IP Rules 
2. Virtual Network Rules

In this article I will discuss only about the IP rules and show you how to configure IP rules in azure SQL firewall. 

Types of IP firewall

There are two types of IP firewall present in azure. 

• Server level IP firewall rules: Enables clients or range of IP addresses who can have access to the entire server and all databases inside that server. 
• Database level IP firewall rules : Enables clients or range of IP addresses who can have access to a specific database for which the client is granted access to. 

How the procedure works

Before diving into all the nitty-gritty details, we need to understand how the firewall works.

                                      
                                                                        Fig : Azure SQL Firewall rules

Initially , all access to Azure SQL server is blocked SQL Database Firewall.To access a database server, we must specify one or more server level IP firewall rules that enable access to Azure SQL server. Use the IP firewall rules to specify which IP address ranges from the internet are allowed and whether Azure applications can attempt to connect to your Azure SQL server.

Here is how the firewall works. When a computer tries to connect to your database server from the internet, the firewall first checks the originating IP address of the request against the database level IP firewall for the database that the connection requests. 

• If the address is within a range that's specified in the database-level IP firewall rules, the connection is granted to the SQL database that contains the rule. 
• If the address isn't within a range in the database level IP firewall rules, the firewall checks the server level IP address rules.If the address is within a range that's in the server-level IP firewall rules, the connection is granted. Server level IP firewall rules apply to all SQL databases on the Azure SQL server.
• If the address isn't within a range that's in the database-level or server-level IP firewall rules, the connection request fails.

Configuration

Firstly, login to your azure account. Type "SQL server" in top search bar. Select SQL server from the result

After the selection , the list of all SQL servers will appear if you have any. The list will be empty if you didn't create any SQL server at all from your account. For the sake of demonstration and to keep the content concise, I am skipping the creation part of SQL server in azure.

Select the SQL server from the list for which you want to configure IP firewall rule.

Go to the firewall settings as depicted in the following screenshot. You can go to firewall settings either from overview panel or by selecting "Firewall & Virtual Networks" in the left menu panel of SQL Server.

There are three columns in IP firewall rule screen, i.e Rule Name, Start IP, End Ip. See following screenshot. For "Rule Name" , you can give any name you find meaningful. Start IP & End IP depicts the starting and ending IP address range of user/client for whom you want to give access to this SQL server. If there is only one user/ only one IP address to be allowed, then Start & End IP will be same.

Click "Add client IP " button. 

Notice that a new row appears after clicking the "Add client IP" button. Give the new rule a name, enter Starting IP address and ending IP address if you want to add a range of IP addresses. Enter same IP address in both field if you wanna allow only one client IP address.

Notice the section of the screen where it says, "Allow access to Azure services". By selecting ON , basically you are telling that any service of azure can have access to this SQL server. This option configures the firewall to allow all connections from Azure including connections from the subscriptions of other customers. If we select ON, it will generate a name rule name and for Start IP & End IP column, azure will enter 0.0.0.0 as the IP address. This rule will not be visible in the azure portal. We can verify this using Transact-SQL, which we will see shortly.

Once you are done, click the "Save" button. A pop up will appear saying azure is updating the firewall rules. Another pop up will appear once the update is complete.

Now it's time to test the newly firewall rule. Open your SQL Server Management Studio. Enter the server name & credentials. Hopefully you will get connected to the azure SQL server if you follow previous steps without any exception.

There is one last thing to show. Open a new query window and run following query

               SELECT * FROM sys.firewall_rules ORDER BY name;

This query will return all the server level firewall rules that you created for your azure SQL server. Following screenshot all of the firewall for my azure SQL server. Notice the first row. First row defines the firewall rule for all azure connection.  Start IP & End IP is same and it is 0.0.0.0 for all azure connections just I told you before.

And now we are done configuring our IP firewall rules. You can add as many firewall rules as you want(I don't know the current number of firewall rules that can be created).

Configuration of firewall rules is not that much hard, all you need to do is just be patient. That's it for today. If you like my article or did find anything that is not mentioned or wrong , do comment below and share the article.