Scans for Azure SQL servers using unauthorized IP addresses in their firewalls. Report. Optionally, remove.

Audit-AzureSql-UnauthorizedFirewallRules-AzAutomation.ps1

<#
    .Synopsis
        Scans for Azure SQL servers using unauthorized IP addresses in their firewalls, 
        groups them by user (by looking for a managedBy tag where the value is an e-mail address),
        and sends an e-mail to each user.

    .NOTES
        AUTHOR: Reuben Cabrera
        LASTEDIT: 2019-08-22
#>

Param(
    [Parameter(Mandatory = $true, HelpMessage = "Send to this address when we can't identify a user to notify.")]
    [String] $DefaultEmailAddress,
    [Parameter(Mandatory = $true, HelpMessage = "When true, sends separate reports to each suspect. When false, sends a single report to the DefaultEmailAddress.")]
    [Boolean] $SendSeparateReports,
    [Parameter(HelpMessage = "When true, violating firewall rules will be automatically removed.")]
    [bool] $RemoveViolatingRules = $false,
    [Parameter(HelpMessage = "ServerName of Azure Sql servers to ignore.")]
    [String[]] $ExceptionList = @(),
    [Parameter(HelpMessage = "A string array of subscription names to ignore")]
    [string[]]$SubscriptionsToIgnore = "my-playground",
    [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 = "[your-org].mail.protection.outlook.com";
$smtpServerPort = 25;
$fromEmailAddress = "[YourOrg] Azure Automation <no.reply@[your-domain]>";
$emailBodyTitle = "<h2>Azure SQL Server firewall scan results &#x1F513;&#x1F6E1;</h2>";
$emailBodyInstructions = "<p>Verify that these Azure SQL Servers are configured correctly.</p>";
$managedByTagName = "managedBy";
$emailBodyNextSteps = "";

$allowedIPAddresses = @(
    "0.0.0.0",
    "1.1.1.1" # change/add your IPs
);
Write-Output "Allowed IP addresses are: $allowedIPAddresses"
Write-Output ""
Write-Output "These servers will not be scanned: $ExceptionList"
Write-Output ""
if ($RemoveViolatingRules) {
    Write-Output "Violating firewall rules will be removed automatically."

    $emailBodyInstructions = "<p><strong>Violating firewall rules may have been removed automatically.</strong>  Verify that these Azure SQL Servers are configured correctly.</p>";
}

function Get-DestinationEmailAddress {
    param($Users)

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

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

    return $Users;
}


# 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..."
    $login = 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, $FirewallRuleName, $StartIpAddress, $EndIpAddress, $ViolationMessage) {
    return New-Object PSObject -Property @{
        Subscription      = $SubscriptionName
        ServerName        = $ServerName
        ResourceGroupName = $ResourceGroupName
        ManagedBy         = $ManagedBy
        ViolationMessage  = $ViolationMessage
        FirewallRuleName  = $FirewallRuleName
        StartIpAddress    = $StartIpAddress
        EndIpAddress      = $EndIpAddress
    }
}

function CheckRulesForUnauthorizedIPAddr($FirewallRules, $ManagedBy) {

    $foundByStart = $FirewallRules | Where-Object StartIpAddress -NotIn $allowedIPAddresses;

    if ($null -ne $foundByStart) {
        $subName = (Get-AzureRMContext).Subscription.Name
        $v = CreateViolationRecord $subName $serverName $resourceGroupName $ManagedBy $foundByStart.FirewallRuleName $foundByStart.StartIpAddress $foundByStart.EndIpAddress `
            "Unauthorized IP address permitted by firewall rule $($foundByStart.FirewallRuleName) $($foundByStart.StartIpAddress)"
        $index = $violations.Add($v)
        Write-Warning "VIOLATION FOUND: $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 | Where-Object ServerName -NotIn $ExceptionList;
    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

        CheckRulesForUnauthorizedIPAddr $firewallRules -ManagedBy $managedBy

        if ($RemoveViolatingRules -ne $false) {
            foreach ($violation in $violations) {
                Write-Output ">> Removing firewall rule '$($violation.FirewallRuleName)' from server '$serverName' in resource group '$resourceGroupName'"

                $removeResult = Remove-AzureRmSqlServerFirewallRule -FirewallRuleName $violation.FirewallRuleName `
                    -ResourceGroupName $resourceGroupName -ServerName $serverName
            }
        }
    }
}


# 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
if ($SendSeparateReports -eq $false) {
    $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

    $count = 1
    if ($records.GetType().FullName -eq "System.Object[]") {
        $count = $records.Length
    }

    $emailBody = $emailBodyTitle `
        + "<p>$count unauthorized IP addresses are allowed by Azure Sql firewall rules. 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 $count violation(s)."
}
else  # send out the e-mails to each user
{
    # group violations by notifyees
    $violationsByUser = $violations | Group-Object ManagedBy;

    foreach ($violations in $violationsByUser | Where-Object -Property Count -NE -Value 0) {

        #$user = $violations[0].NotifyWho;
        $user = ($violations.Group | Select-Object ManagedBy)[0].ManagedBy;

        $records = $violations.Group | Sort-Object Subscription, ServerName `
            | Select-Object ManagedBy, Subscription, ServerName, ResourceGroupName, ViolationMessage;

        $toEmail = Get-DestinationEmailAddress $user

        $records | Export-Csv -Path $attachmentFileName -NoTypeInformation; # export to CSV so we can attach the file to the e-mail
        
        $count = 1
        if ($records.GetType().FullName -eq "System.Object[]") {
            $count = $records.Length
        }

        $emailBody = $emailBodyTitle `
            + "<p>$count unauthorized IP addresses are allowed by Azure Sql firewall rules. See the attached file and below.</p>" `
            + ($records | ConvertTo-Html -Fragment) `
            + $emailBodyInstructions `
            + $emailBodyNextSteps `
            + "<p><em>This message is intended for: $user </em></p>";

        Send-MailMessage `
            -To $toEmail `
            -From $fromEmailAddress `
            -Subject "Warning: $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 e-mail to $toEmail with $count violation(s)."
    }
}

exit