question

FredFred-3260 avatar image
0 Votes"
FredFred-3260 asked JaguaraciSilva-2394 answered

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
5 |1600 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.

5 |1600 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 MikeUrnun commented

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
5 |1600 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.




5 |1600 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.