EnableAuditAndThreatDetection.ps1

<#
    .DESCRIPTION
        A runbook which will enable security settings for all database servers and databases in a subscription

    .NOTES
        AUTHOR: @dazfuller
        LASTEDIT: Jan 30, 2016
#>

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

    "Logging in to Azure..."
    Login-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
    }
}

$SecurityResourceGroupName = ""

try {
    $SecurityResourceGroupName = Get-AutomationVariable -Name 'SecurityResourceGroupName'
} catch {
    Write-Error -Message $_.Exception
    throw $_.Exception
}

# Generates a unique and available storage account name
function Get-AzureRmStorageAccountUniqueName {
    Param (
        [string] $Suffix
    )
    
    # Generates a name in the format of <prefix><random number>
    $RandomNumber = Get-Random -Minimum 1000 -Maximum 99999
    $TestName = [string]$RandomNumber + $Suffix
    
    # Check to see if the name is available
    $Check = Get-AzureRmStorageAccountNameAvailability -Name $TestName
    
    # Keep on generating new names until one is available
    While (-not $Check.NameAvailable) {
        $RandomNumber = Get-Random -Minimum 1000 -Maximum 99999
        $TestName = [string]$RandomNumber + $Suffix

        $Check = Get-AzureRmStorageAccountNameAvailability -Name $TestName
    }

    $TestName
}

# Gets the storage account where audit and threat detection information will be stored
function Get-AzureRmAuditStorageAccount {
    Param (
        [string] $SecurityResourceGroupName,
        [string] $Location
    )
    
    # Check for an existing storage account in the correct region
    $StorageAccount = Get-AzureRmStorageAccount -ResourceGroupName $SecurityResourceGroupName | Where-Object { $_.PrimaryLocation -eq $Location -and $_.StorageAccountName -like "*$($suffix)" }
    
    # If a storage account does not exist then create it
    if ($StorageAccount -eq $null) {

        $StorageAccountName = Get-AzureRmStorageAccountUniqueName -Suffix $Suffix

        Write-Output "Creating storage account $($StorageAccountName)"

        $StorageAccount = New-AzureRmStorageAccount -ResourceGroupName $SecurityResourceGroupName `
                                                    -Name $StorageAccountName `
                                                    -SkuName Standard_LRS `
                                                    -Location $Location `
                                                    -Kind Storage `
                                                    -EnableEncryptionService Blob

    }

    $StorageAccount
}

# Check over each resource group
foreach ($rg in Get-AzureRmResourceGroup) {

    # Check each SQL Server in the resource group
    foreach ($sqlServer in $rg | Get-AzureRmSqlServer) {
    
        # Because location information is inconsistent, convert the SQL Servers location to a standardised single word option
        $ServerLocation = $sqlServer.Location.Replace(" ", "").ToLower()

        # Check the servers auditing policy and enable if one is not present
        $policy = $sqlServer | Get-AzureRmSqlServerAuditingPolicy
        
        if ($policy -eq $null -or $policy.AuditState -ne "Enabled") {

            $StorageAccount = Get-AzureRmAuditStorageAccount -SecurityResourceGroupName $SecurityResourceGroupName -Location $ServerLocation -Prefix $StorageAccountNamePrefix

            Write-Output "Enabling Auditing for $($sqlServer.ServerName)"
            
            # Change the values here to match your security requirements
            $sqlServer | Set-AzureRmSqlServerAuditingPolicy -EventType All `
                                                            -AuditType Blob `
                                                            -RetentionInDays 0 `
                                                            -StorageAccountName $StorageAccount.StorageAccountName `
                                                            -StorageKeyType Primary

        }

        # Check the servers threat detection policy and enable if one is not present
        $policy = $sqlServer | Get-AzureRmSqlServerThreatDetectionPolicy

        if ($policy -eq $null -or $policy.ThreatDetectionState -eq "Disabled") {

            Write-Output "Enabling threat detection for $($sqlServer.ServerName)"

            $StorageAccount = Get-AzureRmAuditStorageAccount -SecurityResourceGroupName $SecurityResourceGroupName -Location $ServerLocation -Prefix $StorageAccountNamePrefix

            $sqlServer | Set-AzureRmSqlServerThreatDetectionPolicy -EmailAdmins $true `
                                                                   -RetentionInDays 0 `
                                                                   -StorageAccountName $StorageAccount.StorageAccountName

        }

    }

}