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.

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

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

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.

Filed under: Azure, Azure SQL DB Tagged: Azure, Firewall, Security, SQL database, TSQL