Description:

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

Rationale:

Enabling Azure Defender for SQL server does not enables Vulnerability Assessment capability for individual SQL databases unless storage account is set to store the scanning data and reports.

The Vulnerability Assessment service scans databases for known security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. Results of the scan include actionable steps to resolve each issue and provide customized remediation scripts where applicable.

Additionally an assessment report can be customized by setting an acceptable baseline for permission configurations, feature configurations, and database settings.

Impact:

Enabling the Azure Defender for SQL features will incur additional costs for each SQL server.

Default Value:

By default Azure Defender for SQL is not enabled for a SQL server. Enabling Azure Defender for SQL does not enable VA scanning by setting Storage Account automatically.

Audit:

From Azure Console

  1. Go to SQL servers

  2. Select a server instance

  3. Click on Security Center

  4. Ensure that Azure Defender for SQL is set to Enabled

  5. Select Configure next to Enabled at subscription-level

  6. In Section Vulnerability Assessment Settings, Ensure Storage Accounts is does not read Configure required settings

Using Azure PowerShell

Get the list of all SQL Servers

1Get-AZSqlServer

For each Server

1Get-AzSqlServerVulnerabilityAssessmentSetting -ResourceGroupName <resource group name> -ServerName <server name>

Remediation:

From Azure Console

  1. Go to SQL servers

  2. Select a server instance

  3. Click on Security Center

  4. Select Configure next to Enabled at subscription-level

  5. In Section Vulnerability Assessment Settings, Click Storage Account

  6. Choose Storage Account (Existing or Create New). Click Ok

  7. Click Save

Using Azure PowerShell

If not already, Enable Azure Defender for a SQL:

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

To enable ADS-VA service by setting Storage Account

1Update-AzSqlServerVulnerabilityAssessmentSetting ` 2-ResourceGroupName "<resource group name>"` 3-ServerName "<Server Name>"` 4-StorageAccountName "<Storage Name from same subscription and same Location" ` 5-ScanResultsContainerName "vulnerability-assessment" ` 6-RecurringScansInterval Weekly ` 7-EmailSubscriptionAdmins $true ` 8-NotificationEmail @("[email protected]" , "[email protected]") 


References:

  1. https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability- assessment

  2. https://docs.microsoft.com/en- us/rest/api/sql/servervulnerabilityassessments/listbyserver

  3. https://docs.microsoft.com/en-in/powershell/module/Az.Sql/Update- AzSqlServerVulnerabilityAssessmentSetting?view=azps-2.6.0

  4. https://docs.microsoft.com/en-in/powershell/module/Az.Sql/Get- AzSqlServerVulnerabilityAssessmentSetting?view=azps-2.6.0

  5. https://docs.microsoft.com/en-us/azure/security/benchmarks/security-controls- v2-posture-vulnerability-management#pv-6-perform-software-vulnerability- assessments