Lesson Learned #102: External Data Source and Allow Azure Service setting

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.