SSMS

Managing firewall rules for Azure databases with SSMS

When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.

Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.

Attempt to connect to your Azure database.

ssms-azure-01

If the firewall is not already open for this IP, you’ll receive the following screen:

ssms-azure-02

The first step is to identify you with your Azure subscription.

ssms-azure-03If you’ve created your account a long time ago, you could receive this screen … If your account is linked to professional resources (granted by your comany) the good choice is probably the first. If you’re developing from home with resources that you’ll pay by yourself, the correct choice is probably the second.

ssms-azure-05

When you’re successfully identified, you can create a new rule. Open the firewall for your own IP or for a subnet.

ssms-azure-06

Now, you can connect and query your database or the virtual master database (open the folder System Databases).

ssms-azure-07

Firewall rules applicable at the whole server are effectively stored in the master database, you can list them by querying the view sys.firewall_rules.

ssms-azure-08

At this moment, you see that I was a bit lazy and didn’t clean all these rules since a long time. If you want to remove some rules, you can use the stored procedure sp_delete_firewall_rule.

ssms-azure-10

It’ll clean-up your list of firewal rules.ssms-azure-11

 

If you want you can create firewall rules at the database level by connecting to the database and using the sys.database_firewall_rules. The firewall rules at the database level are evaluated before these at the server level. To improve performance, server-level firewall rules are temporarily cached at the database level. To refresh the cache, see DBCC FLUSHAUTHCACHE