As you know when you are using cross database queries or external tables and you execute a query the connection will be stablished from the database server that initiates the execution.
The IP of this database server may be different because this is dynamic and not fixed. For this reason, one of the requirements is to enable Allow Azure Service.
As a workaround if you want to have the status OFF to Allow Azure Service, I would like to suggest to use the following script that before to execute a query using an external table/cross database query you could call the stored procedure that needs the parameters of client IP and the name of the rule.
Run this stored procedure in the server/database that contains the data.
CREATE PROCEDURE bValidIP(@sIP as varchar(50), @NameRule as Nvarchar(20))
AS
DECLARE @start_ip_address AS VARCHAR(50) = ''
DECLARE @AddNewRule AS INTEGER = 1
SET @start_ip_address = ISNULL( ( SELECT TOP 1 start_ip_address FROM sys.database_firewall_rules WHERE Name = @NameRule ) ,'')
IF @start_ip_address <> @sIP
EXECUTE sp_delete_database_firewall_rule @NameRule
ELSE
SET @AddNewRule=0
IF @AddNewRule=1
BEGIN
EXECUTE sp_set_database_firewall_rule @NameRule, @sIP, @sIP;
END
As we mentioned, just only execute this stored procedure in the server/database that contains the data before execute any query the cross database query/external table.
EXECUTE bValidIP '10.10.10.10','VALUE1'
Enjoy!
