SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

 


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...