Azure SQL Database Level Firewall Rules

I have written about Azure SQL Database LEVEL firewall rules before during my blog series, more specifically the security blog post. If you can’t remember the section on firewalls then I will bring the following diagram to your attention.

DB_FIREWALL

The main purpose of this post today is to discuss this point – If you have an Azure SQL Database involved in Active Geo Replication and opt to use database level firewall rules do you need to create the rules in both the primary and secondary database?

I thought so, but I was wrong. I connect to my primary database and run the following (obfuscated) .

EXECUTE sp_set_database_firewall_rule N'rule1', '0.0.0.4', '0.0.0.4';

EXECUTE sp_set_database_firewall_rule N'rule2', '0.0.0.4', '0.0.0.4';

I then check the following table to confirm that I did successfully add the rules.

SELECT * FROM sys.database_firewall_rules

Nod1
Naturally I thought I would need to do the same over at the secondary (maybe a part of me was getting confused with server level rules ………. ). So I connected to the other server within my failover group and issue the same code.

But this time I received: Msg 3906, Level 16, State 2, Procedure sp_set_database_firewall_rule, Line 1 [Batch Start Line 0]
Failed to update database “xxxx” because the database is read-only.

However querying the rules table you will see they have also been created on the secondary.

SELECT @@SERVERNAME
SELECT * FROM sys.database_firewall_rules

2ndNode
This behavior is confirmed from official Microsoft documentation too – “We recommend using database firewall rules for Geo-replicated databases so these rules can be replicated with the database to ensure all secondary databases have the same firewall rules as the primary. This approach eliminates the need for customers to manually configure and maintain firewall rules on servers hosting both the primary and secondary databases” Source : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview

The rules had been replicated over for me.

 

One thought on “Azure SQL Database Level Firewall Rules

  1. Pingback: Azure Database-Level Firewall Rules And Geo-Replication – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s