Control: 4.2.5 Ensure that Vulnerability Assessment (VA) setting 'Also send email notifications to admins and subscription owners' is set for each SQL Server

Description

Enable Vulnerability Assessment (VA) setting 'Also send email notifications to admins and subscription owners'.

VA scan reports and alerts will be sent to admins and subscription owners by enabling setting 'Also send email notifications to admins and subscription owners'. This may help in reducing time required for identifying risks and taking corrective measures.

Remediation

From Azure Console

Go to SQL servers
Select a server instance
Click on Security Center
Select Configure next to Enabled at subscription-level
In Section Vulnerability Assessment Settings, configure Storage Accounts if not already
Check/enable 'Also send email notifications to admins and subscription owners'
Click Save

From Powershell

If not already, Enable Advanced Data Security for a SQL Server:

Set-AZSqlServerThreatDetectionPolicy -ResourceGroupName <resource group name>
-ServerName <server name> -EmailAdmins $True

To enable ADS-VA service and Set 'Also send email notifications to admins and subscription owners'

Update-AzSqlServerVulnerabilityAssessmentSetting `
-ResourceGroupName "<resource group name>"`
-ServerName "<Server Name>"`
-StorageAccountName "<Storage Name from same subscription and same Location" `
-ScanResultsContainerName "vulnerability-assessment" `
-RecurringScansInterval Weekly `
-EmailSubscriptionAdmins $true `
-NotificationEmail @("mail1@mail.com" , "mail2@mail.com")

Default Value

By default, 'Also send email notifications to admins and subscription owners' is enabled.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v150_4_2_5

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v150_4_2_5 --share

SQL

This control uses a named query:

select
  s.id as resource,
  case
    when
      security -> 'properties' ->> 'state' = 'Disabled'
      or
      (
        security -> 'properties' ->> 'state' = 'Enabled'
        and assessment -> 'properties' ->> 'storageContainerPath' is not null
        and assessment -> 'properties' -> 'recurringScans' ->> 'emailSubscriptionAdmins' = 'false'
      )
      then 'alarm'
    else 'ok'
  end as status,
  case
    when
      security -> 'properties' ->> 'state' = 'Disabled'
      or
      (
        security -> 'properties' ->> 'state' = 'Enabled'
        and assessment -> 'properties' ->> 'storageContainerPath' is not null
        and assessment -> 'properties' -> 'recurringScans' ->> 'emailSubscriptionAdmins' = 'false'
      )
      then s.name || ' VA setting not configured to send email notifications to subscription admins and owners.'
    else s.name || ' VA setting configured to send email notifications to subscription admins and owners.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_server s,
  jsonb_array_elements(server_security_alert_policy) security,
  jsonb_array_elements(server_vulnerability_assessment) assessment,
  azure_subscription sub
where
  sub.subscription_id = s.subscription_id;