Control: 4.2.3 Ensure that Vulnerability Assessment (VA) setting 'Periodic recurring scans' is set to 'on' for each SQL server

Description

Enable Vulnerability Assessment (VA) Periodic recurring scans for critical SQL servers and corresponding SQL databases.

VA setting 'Periodic recurring scans' schedules periodic (weekly) vulnerability scanning for the SQL server and corresponding Databases. Periodic and regular vulnerability scanning provides risk visibility based on updated known vulnerability signatures and best practices.

Remediation

From Azure Console

Go to SQL servers
For each server instance
Click on Security Center
In Section Vulnerability Assessment Settings, set Storage Account if not already
Toggle 'Periodic recurring scans' to ON.
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 with 'Periodic recurring scans'

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

Enabling Microsoft Defender for SQL enables 'Periodic recurring scans' by default but does not configure the Storage account.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v150_4_2_3

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v150_4_2_3 --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' ->> 'isEnabled' = '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' ->> 'isEnabled' = 'false'
      )
      then s.name || ' VA setting periodic recurring scans disabled.'
    else s.name || ' VA setting periodic recurring scans enabled.'
  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;