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

Question

Hello,&nbsp;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.&nbsp;I also tried the "Azure Resource Graph Explorer",&nbsp;https://portal.azure.com/#blade/HubsExtension/ArgQueryBlade,but could not find any matching object to query by.&nbsp;Does anyone has an idea how can I find it?&nbsp;Extra info:Azure SQL connectivity settingshttps://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings&nbsp;Deny Public Network Access in Azure Database for MySQL using Azure portalhttps://docs.microsoft.com/en-us/azure/mysql/howto-deny-public-network-access&nbsp;Lesson Learned #126:Deny Public Network Access,Allow Azure Services and Private Link in SQL Databasehttps://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-126-deny-public-network-access-allow-azure/ba-p/1244037

pazdedav · Answer

Hi&nbsp;eitan1000&nbsp;,&nbsp;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):&nbsp;resources
| where ['type'] =~ 'Microsoft.Sql/servers'
| where properties['publicNetworkAccess'] == 'Enabled'

lukemurraynz · Answer

eitan1000Try this:&nbsp;$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

lukemurraynz · Answer

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%2FMicrosoft.Authorization%2FpolicyDefinitions%2F28b0b1e5-17ba-4963-a7a4-5a1ab4400a0bYou should be able to also use:# Get the Public Network Access property(Get-AzSqlServer -ServerName sql-server-name -ResourceGroupName sql-server-group).PublicNetworkAccesshttps://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings#change-public-network-access-via-powershell

eitan1000 · Answer

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?

eitan1000 · Answer

Thank very very much!!!

Forum Discussion

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

5 Replies

Resources