question

FredFred-3260 avatar image
0 Votes"
FredFred-3260 asked ·

Can you recommend the best/simplest way to regularly audit the IP Address Whitelists of the following Azure Resources: API Gateway, Storage Account, Function App, SQL Server/DBs

What is the simplest approach to auditing the IP Address Whitelists for the following Azure resources: API Gateways, Function Apps, Storage Accounts, and SQL Databases/Servers?

We control access to those Azure Resources to an approved list of IP Addresses. We want to regularly check those lists and compare them to a baseline.

My original idea was to write a Powershell script that queried all of those resources' Whitelists and comparing to my approved list. But now I find that there's no Powershell script to query Database level firewall rules, only servers. I can use T-SQL, but I wanted to keep it simple and use a single tool.

Is there another tool that would make that simpler? Or another way to use Powershell to gather all that info? I had also considered using LogAnalytics to alert support if a log that would create or modify those firewall comes through any of those resources.

Can anyone offer a different approach that I may be missing? Or a modification on my current approach that would minimize "the administrative overhead" of this activity?

azure-sql-databaseazure-functionsazure-storage-accountsazure-api-management
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErikEJ avatar image
0 Votes"
ErikEJ answered ·

You can only use SQL for database level firewall rules, but you can execute SQL from PowerShell.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FredFred-3260 avatar image
0 Votes"
FredFred-3260 answered ·

Is there an existing solution for testing this sort of thing regularly? Without having to build the entire solution myself? Surely there are others trying to audit their IP Address Whitelists.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @FredFred-3260 - Were you able to locate a solution? It sounds like you have most of the Azure Resources that you've listed out covered but the only missing piece is the ability to do the same for the database-level firewall?

0 Votes 0 ·
JaguaraciSilva-2394 avatar image
0 Votes"
JaguaraciSilva-2394 answered ·

Hi,

use Azure Cloud shell for running a single script file:

1) you can create security groups by application and filter the network traffic by client IP using a whitelist -> https://docs.microsoft.com/en-us/azure/virtual-network/tutorial-filter-network-traffic-cli

2) connect to Azure databases using database command prompt (e.g. sqlcmd) and execute sp_set_database_firewall_rule to set firewall rules on database.




·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.