Azure Automation script that scans for Azure SQL servers missing our IP addresses in their firewalls and sends an e-mail with the full list.

Audit-AzureSqlFirewallRules-AzAutomation.ps1

<#
    .Synopsis
        Scans for Azure SQL servers missing our IP addresses in their firewalls and sends a report by e-mail.

    .Description
        Intended for use from an Azure Automation account.

    .NOTES
        AUTHOR: rcabr
        LASTEDIT: 2019-06-04
#>

Param(
        [Parameter(Mandatory=$true, HelpMessage="Send to this address when we can't identify a user to notify.")]
    [String] $DefaultEmailAddress,
    [Parameter(HelpMessage="A string array of subscription names to ignore")]
    [string[]]$SubscriptionsToIgnore = "[myunsecuredsubscription]",
    [Parameter(HelpMessage="The NAME of the Azure Automation Account Credential for logging in to Office 365 to send e-mail.")]
    [String] $O365CredentialName = "O365Credential",
    [Parameter(HelpMessage="When set, no e-mail will be sent to any other address but this one.  Useful for debugging and testing.")]
    [String] $OverrideEmailAddress = $null,
    [Parameter(HelpMessage="The e-mail subject line.")]
    [String] $EmailSubject = "Azure Sql Server firewall scan"
)

# Constants
$attachmentFileName = "AzureSqlFirewallScan.csv"; # name of the e-mail's file attachment
$smtpServerName = "[myorg].mail.protection.outlook.com";
$smtpServerPort = 25;
$fromEmailAddress = "[my org name] Azure Automation <no.reply@[mydomain]>";
$emailBodyTitle = "<h2>Azure SQL Server firewall scan results &#x1F513;&#x1F6E1;</h2>";
$emailBodyInstructions = "<p>Verify that these Azure SQL Servers are configured correctly.</p>";
$upnSuffix = "[myorgupn]";
$scopeManagementGroupName = "[myrootmg]"
$managedByTagName = "managedBy";
$emailBodyNextSteps = "";


function Get-DestinationEmailAddress {
    param($User)

    if (($null -ne $OverrideEmailAddress) -and ($OverrideEmailAddress -ne "")) {
        Write-Warning "OVERRIDE: Sending results for user '$User' to '$OverrideEmailAddress' instead"
        return $OverrideEmailAddress;
    }

    if (($null -eq $User) -or ($User -eq ""))
    {
        Write-Warning "Sending results for unknown users to '$DefaultEmailAddress' instead"
        return $DefaultEmailAddress;
    }

    return $User;
}

# Try to find a managedBy tag on a resource, its resource group, or its subscription,
# and return the value (or $null).
# Throws an exception if the resource doesn't exist.
function Get-ResourceManagedBy {
    param([string] $ResourceType, [string] $ResourceId)

    $rt = $ResourceType;
    $rid = $ResourceId;

    # if not a resource group
    if ($rt -ne "/Microsoft.Resources/subscriptions/resourceGroups") {
        $r = Get-AzureRMResource -ResourceId $rid -ErrorAction Stop;
        if ($null -ne $r.Tags -and $r.Tags.ContainsKey($managedByTagName) -eq $True) {
            return $r.Tags[$managedByTagName];
        }

        # move up to the resource group level
        $rg = Get-AzureRMResourceGroup -Name $r.ResourceGroupName;
        $rt = "/Microsoft.Resources/subscriptions/resourceGroups";
    }
        
    # if it's a resource group
    if ($rt -eq "/Microsoft.Resources/subscriptions/resourceGroups") {
        if ($null -eq $rg) {
            $rg = Get-AzureRMResourceGroup -Id $ResourceId -ErrorAction Stop;
        }
        if ($null -ne $rg.Tags -and $rg.Tags.ContainsKey($managedByTagName) -eq $True) {
            return $rg.Tags[$managedByTagName];
        }

        # move up to the subscription level
        $sb = Get-AzureRmSubscription;
        $rt = "/Microsoft.Resources/subscriptions";
    }

    # if it's a subscription
    if ($rt -eq "/Microsoft.Resources/subscriptions") {
        if ($null -eq $sb) {
            $sb = Get-AzureRmSubscription;
        }
        if ($null -ne $sb.Tags -and $sb.Tags.ContainsKey($managedByTagName) -eq $True) {
            return $sb.Tags[$managedByTagName];
        }
    }

    return $null;
}

$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

    "Logging in to Azure..."
    Add-AzureRMAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}


# Collect violations to return
$violations = New-Object System.Collections.ArrayList

function CreateViolationRecord ($SubscriptionName, $ServerName, $ResourceGroupName, $ManagedBy, $ViolationMessage) {
    return New-Object PSObject -Property @{
        Subscription      = $SubscriptionName
        ServerName        = $ServerName
        ResourceGroupName = $ResourceGroupName
        ManagedBy         = $ManagedBy
        ViolationMessage  = $ViolationMessage
    }
}

function CheckRulesForIPAddr($FirewallRules, $StartIp, $RuleName, $ManagedBy) {
    $foundByStart = $FirewallRules | Where-Object StartIpAddress -EQ $StartIp;
    if ($null -eq $foundByStart) {
        $subName = (Get-AzureRmContext).Subscription.Name
        $v = CreateViolationRecord $subName $serverName $resourceGroupName $ManagedBy "Missing firewall rule for $RuleName ($StartIp)"
        $index = $violations.Add($v)
        Write-Output $v
    }
}

$subscriptions = Get-AzureRMSubscription
foreach ($subscription in $subscriptions) {
    if ($subscription.Name -in $SubscriptionsToIgnore) {
        Write-Output "Skipping subscription '$($subscription.Name)'"
        continue;
    }
    $ctx = Set-AzureRMContext -Subscription $subscription.Name;

    $servers = Get-AzureRMSqlServer;
    Write-Output "Examining $($servers.Count) Sql Servers in subscription '$($subscription.Name)'"

    $ErrorActionPreference = "Continue";

    foreach ($server in $servers) {
        $resourceGroupName = $server.ResourceGroupName;
        $serverName = $server.ServerName;

        $firewallRules = Get-AzureRMSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
            -ServerName $serverName;

        $managedBy = Get-ResourceManagedBy "Microsoft.Sql/servers" $server.ResourceId

        CheckRulesForIPAddr $firewallRules -ManagedBy $managedBy -StartIp "[my ip address a]" -RuleName "[myorg location a]"
        CheckRulesForIPAddr $firewallRules -ManagedBy $managedBy -StartIp "[my ip address b]" -RuleName "[myorg location b]"
       
    }
}


# get O365 credentials to send e-mails with
$o365Cred = Get-AutomationPSCredential -Name $O365CredentialName;
if ($null -eq $o365Cred) { 
    Write-Error "Credential $O365CredentialName does not exist in this Automation Account. Please create one.";
    exit
} 


# send full report by e-mail
$records = $violations | Sort-Object ManagedBy, Subscription, ServerName -Descending `
| Select-Object ManagedBy, Subscription, ServerName, ResourceGroupName, ViolationMessage;

$toEmail = Get-DestinationEmailAddress $null

$records | Export-Csv -Path $attachmentFileName -NoTypeInformation; # export to CSV so we can attach the file to the e-mail

$emailBody = $emailBodyTitle `
    + "<p>There are $($records.Count) Azure resources that are missing firewall entries for [my org name] public IP addresses. See the attached file and below.</p>" `
    + ($records | ConvertTo-Html -Fragment) `
    + $emailBodyInstructions `
    + "<p><em>This message is intended for: $toEmail</em></p>";

Send-MailMessage `
    -To $toEmail `
    -From $fromEmailAddress `
    -Subject "Report: $EmailSubject" `
    -Body $emailBody `
    -Attachments $attachmentFileName `
    -UseSsl `
    -Port $smtpServerPort `
    -BodyAsHtml `
    -SmtpServer $smtpServerName `
    -Credential $o365Cred;

Remove-Item -Path $attachmentFileName; # remove attachment file from filesystem

Write-Output "Sent full report e-mail to $toEmail with $($records.Count) findings"

exit