Control: Ensure that VA setting Periodic Recurring Scans is enabled on a SQL server
Description
Enable Vulnerability Assessment (VA) Periodic recurring scans for critical SQL servers and corresponding SQL databases.
Usage
Run the control in your terminal:
powerpipe control run azure_compliance.control.sql_server_va_setting_periodic_scan_enabled
Snapshot and share results via Turbot Pipes:
powerpipe loginpowerpipe control run azure_compliance.control.sql_server_va_setting_periodic_scan_enabled --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 subscriptionfrom azure_sql_server s, jsonb_array_elements(server_security_alert_policy) security, jsonb_array_elements(server_vulnerability_assessment) assessment, azure_subscription subwhere sub.subscription_id = s.subscription_id;