November, 2018 | Kaizen - Continuous Improvement

Azure Database Firewall Queries

Posted on November 30, 2018 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

-- Server Level Firewall Rules
SELECT * FROM sys.firewall_rules

-- Database Level Firewall Rules
SELECT * FROM sys.database_firewall_rules

-- -- Remove server level firewall setting  
EXECUTE sp_delete_firewall_rule N'AllowAllWindowsAzureIps';

-- Remove database-level firewall setting  
EXECUTE sp_delete_database_firewall_rule N'Allow Azure';

-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '40.79.152.30', '40.79.152.30';

-- Add server-level firewall setting  
EXECUTE sp_set_firewall_rule N'Allow_Azure_Elastic_Query', '40.79.152.30', '40.79.152.30';

-- Server Level Firewall Rules

SELECT * FROM sys.firewall_rules

-- Database Level Firewall Rules

SELECT * FROM sys.database_firewall_rules

-- -- Remove server level firewall setting

EXECUTE sp_delete_firewall_rule N'AllowAllWindowsAzureIps';

-- Remove database-level firewall setting

EXECUTE sp_delete_database_firewall_rule N'Allow Azure';

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'Allow Azure', '40.79.152.30', '40.79.152.30';

-- Add server-level firewall setting

EXECUTE sp_set_firewall_rule N'Allow_Azure_Elastic_Query', '40.79.152.30', '40.79.152.30';

Logic Apps – US East Region

/********************************************************************************************************
-- https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config#configuration
East US	
13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81

********************************************************************************************************/

declare @pIPAddresslist nvarchar(4000)
select  @pIPAddresslist = '13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81'
select value from string_split(@pIPAddresslist,',')

declare @IPAddress varchar(50)
select  @IPAddress = '13.92.98.111'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_1', @IPAddress, @IPAddress;


select  @IPAddress = '23.100.29.190'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_2', @IPAddress, @IPAddress;


select  @IPAddress = '23.101.132.208'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_3', @IPAddress, @IPAddress;

select  @IPAddress = '23.101.136.201'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_4', @IPAddress, @IPAddress;

select  @IPAddress = '23.101.139.153'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_5', @IPAddress, @IPAddress;

select  @IPAddress = '40.114.82.191'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_6', @IPAddress, @IPAddress;

select  @IPAddress = '40.121.91.41'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_7', @IPAddress, @IPAddress;

select  @IPAddress = '104.45.153.81'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_8', @IPAddress, @IPAddress;

/********************************************************************************************************

-- https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config#configuration

East US

13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81

********************************************************************************************************/

declare @pIPAddresslist nvarchar(4000)

select @pIPAddresslist = '13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81'

select value from string_split(@pIPAddresslist,',')

declare @IPAddress varchar(50)

select @IPAddress = '13.92.98.111'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_1', @IPAddress, @IPAddress;

select @IPAddress = '23.100.29.190'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_2', @IPAddress, @IPAddress;

select @IPAddress = '23.101.132.208'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_3', @IPAddress, @IPAddress;

select @IPAddress = '23.101.136.201'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_4', @IPAddress, @IPAddress;

select @IPAddress = '23.101.139.153'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_5', @IPAddress, @IPAddress;

select @IPAddress = '40.114.82.191'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_6', @IPAddress, @IPAddress;

select @IPAddress = '40.121.91.41'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_7', @IPAddress, @IPAddress;

select @IPAddress = '104.45.153.81'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_8', @IPAddress, @IPAddress;

Month: November 2018

Azure Database Firewall Queries

Recent Posts

Categories

Recent Posts

Archives