Azure SQL Database firewall rule Deepdive–Part1

The first thing you must do after creating your Azure SQL Database is to configure Firewall rules. To help protect your data Azure prevents all connections to your Azure SQL Database until and unless a Server\Database firewall rule exists. It is Azure’s way to reduce the surface area & secure your Databases. Your connection gets validated against the firewall rule based on originating IP Address of request. It works just like your windows firewall.

So in-order to grant access to your databases you must create firewall rules with a range of acceptable IP Addresses. Your firewall rule not only validates the client connections from the Internet but it also checks whether Azure applications are allowed to connect to your Azure SQL database or not. Every connection must be validated by the firewall before reaching Azure SQL Database.

You can create two types (levels) of firewall rules:

1. Database level firewall rule: As the name suggests this represents the firewall rule created for individual Azure SQL Database and any IP range specified within this rule will be able to connect\access the database. The best part is these rules are stored within databases (Including master database) which helps to keep the database portable means no matter where you move them the application\desired users will be able to access it because the rules are always within the database. You can have a maximum of 128 Database-level firewall rules for a database.

2. Server level firewall rule: The name defines the exact purpose, this represents the firewall rule created for complete Azure SQL Database server, which means the IP range specified within this rule will be allowed to access any database on this logical server. Since this is a server wide rule it gets stored in master database only and hence applicable \inherited by all the databases (but never gets stored in individual databases)

 

What if I want to provide selective access to one database? And how to avoid most common mistake?

Yes, you guessed it correctly database level firewall rule is the solution if you want to grant access to only selected database. But you need to be aware about the most common mistake people do while configuring database-level firewall rules: You need to make sure the IP Address range specified in database level firewall rule is exclusive in nature, which means it must be beyond the range specified in your server level firewall rule else your end users will automatically get access to your all other databases on that specific logical server.

 

How to allow Azure applications to access Azure SQL databases?

As discussed earlier Azure application connections are also restricted initially on Azure SQL Databases and you must configure firewall rules in order to grant access to them. In order to grant access to azure applications the starting and ending address must be equal to 0.0.0.0 which indicates that all connections from within Azure are allowed.

 

Why I am still unable to access my SQL Azure database even after creating firewall rules? Troubleshoot Azure SQL Database\server firewall rule.

Well, there could be multiple reasons for this, but the most common issues you need to check are:

1. Check if you are using the correct FQDN Server name for your Azure SQL Database.

2. Check your IP Address if it falls under your server\database level firewall rule.

3. If you just created a firewall rule, then please be patient! You may need to wait a couple of minutes for the rule to take effect.

4. If you are trying to access Azure SQL Database from your local computer, make sure the firewall in your network and local computer allows outgoing connection on TCP port 1433. If it is not there create an exception in your firewall.

5. Check if you are using correct credentials and you have access to the database.

6. NAT (Network address translation) setting may be contributing – check with a network pro.

 

Best Practices:

1. Try to use Database-level firewall rule whenever possible to keep your SQL database more portable.

2. Use Server-level firewall rule only when you have multiple databases with same access requirements and most of them will remain together on the same server, this will also save time since you won’t have to configure firewall rules individually for these Databases.

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp