Findings all Azure SQL Servers with "Deny public network access" disabled

[eitan1000]

Hello,

I didn't find a way to find all the Azure "SQL Server" objects that has their check box of "Deny public network access" in the Firewall section - disabled.

I also tried the "Azure Resource Graph Explorer", https://portal.azure.com/#blade/HubsExtension/ArgQueryBlade, but could not find any matching object to query by.

Does anyone has an idea how can I find it?

Extra info:

Azure SQL connectivity settings

https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings

Deny Public Network Access in Azure Database for MySQL using Azure portal

https://docs.microsoft.com/en-us/azure/mysql/howto-deny-public-network-access

Lesson Learned #126:Deny Public Network Access,Allow Azure Services and Private Link in SQL Database

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-126-deny-public-ne...

[#2]

Take a look at this Azure Policy:

Configure Azure SQL Server to disable public network access:
https://portal.azure.com/#blade/Microsoft_Azure_Policy/PolicyDetailBlade/definitionId/%2Fproviders%2...

You should be able to also use:

# Get the Public Network Access property
(Get-AzSqlServer -ServerName sql-server-name -ResourceGroupName sql-server-group).PublicNetworkAccess

https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings#change-public-networ...

[#3]

Thank you very much Luke, your links looks really helpful.
Still, I need a way to inventory the current status of this check box across our tenant - is there a way to do it?

[#4]

@eitan1000Try this:

$AzureSQLServers = Get-AzSqlServer

$results = @()
ForEach ($server in $AzureSQLServers)


{
  $SQLServer = Get-AzSqlServer -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName

  $results += [pscustomobject]@{
    ServerName          = $SQLServer.ServerName
    ResourceGroup       = $SQLServer.ResourceGroupName
    PublicNetworkAccess = $SQLServer.PublicNetworkAccess
  }
}

$results

[#5]

Thank very very much!!!

[#6]

Hi @eitan1000 ,

If you want to search across many subscriptions, you could use Azure Resource Graph query instead of PowerShell (where you need to switch / loop between subscriptions):

resources
| where ['type'] =~ 'Microsoft.Sql/servers'
| where properties['publicNetworkAccess'] == 'Enabled'