Enable Vulnerability Assessment for Microsoft SQL Servers

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access your Azure cloud resources.

03 From the Type equals all filter box, select Equals and choose SQL server to list only the SQL database servers provisioned for the selected Azure subscription.

04 Click on the name (link) of the SQL database server that you want to examine.

05 In the blade navigation panel, under Security, choose Microsoft Defender for Cloud.

06 On the Microsoft Defender for Cloud page, check the Enablement Status attribute value. If the Enablement Status value is set to Disabled, Microsoft Defender for Cloud and its Vulnerability Assessment (VA) feature are not enabled for your SQL database server. If Enablement Status is not available, instead a Get Started page is displayed, Microsoft Defender for Cloud is disabled, therefore, Vulnerability Assessment (VA) is also disabled for the selected SQL database server.

07 Repeat steps no. 4 – 6 for each SQL database server provisioned in the selected Azure subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

01 Run account list command (Windows/macOS/Linux) with custom output filters to list the IDs of the cloud subscriptions available in your Azure cloud account:

az account list
  --query '[*].id'

02 The command output should return the requested SQL database server information:

[
	"abcdabcd-1234-abcd-1234-abcdabcdabcd",
	"abcd1234-abcd-1234-abcd-abcd1234abcd"
]

03 Run account set command (Windows/macOS/Linux) with the ID of the Azure cloud subscription that you want to examine as the identifier parameter to set the selected subscription to be the current active subscription (the command does not produce an output):

az account set
  --subscription abcdabcd-1234-abcd-1234-abcdabcdabcd

04 Run account get-access-token command (Windows/macOS/Linux) with custom query filters to describe the name of the Microsoft Defender for Cloud pricing plan configured for the SQL database servers available within the current subscription:

az account get-access-token
  --query "{subscription:subscription,accessToken:accessToken}"
  --out tsv | xargs -L1 bash -c 'curl -X GET -H "Authorization: Bearer $1" -H "Content-Type: application/json" https://management.azure.com/subscriptions/$0/providers/Microsoft.Security/pricings?api-version=2018-06-01' | jq '.|.value[] | select(.name=="SqlServers")'|jq '.properties.pricingTier'

05 The command output should return the name of the configured pricing tier:

"Free"

06 Repeat steps no. 3 - 5 for each subscription available in your Microsoft Azure cloud account.

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access your Azure cloud resources.

03 From the Type equals all filter box, select Equals and choose SQL server to list only the SQL database servers provisioned for the selected Azure subscription.

04 Click on the name (link) of the SQL database server that you want to examine.

05 In the blade navigation panel, under Security, select Microsoft Defender for Cloud.

06 On the Microsoft Defender for Cloud page, check the Enablement Status attribute value and make sure that Microsoft Defender for SQL is enabled, then choose (Configure) to access the Vulnerability Assessment (VA) settings available for the selected SQL server. If (Configure) is not available, instead a Get Started page is displayed, Microsoft Defender for Cloud is disabled, therefore, Vulnerability Assessment (VA) is also disabled and the Audit process ends here. Otherwise, you can continue the Audit process with the next step.

07 In the VULNERABILITY ASSESSMENT SETTINGS section, check the Storage account attribute value. If there is no storage account listed as value for the Storage account attribute, instead the Select Storage account link is available, there is no Azure storage account configured for Vulnerability Assessment, therefore, the Vulnerability Assessment (VA) security feature is not enabled for the selected Microsoft SQL database server.

08 Repeat steps no. 4 – 7 for each SQL database server provisioned in the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

01 Run Get-AzSqlServer PowerShell command with custom output filters to list the name of each SQL database server and the name of its associated resource group, available within the current Azure subscription:

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

02 The command output should return the requested SQL database server information:

ServerName          ResourceGroupName
----------          -----------------
cc-prod-db-server   cloud-shell-storage-westeurope
cc-web-sql-server   cloud-shell-storage-westeurope

03 Run Get-AzSqlServerVulnerabilityAssessmentSetting command with the name of the SQL database server that you want to examine as the identifier parameter and custom output filters to describe the name of the Azure Storage account configured for Vulnerability Assessment (VA):

Get-AzSqlServerVulnerabilityAssessmentSetting
  -ServerName "cc-prod-db-server"
  -ResourceGroupName "cloud-shell-storage-westeurope" | Select-Object StorageAccountName

04 The command output should return the name of the storage account associated configured for Vulnerability Assessment:

StorageAccountName
------------------

05 Repeat steps no. 3 and 4 for each SQL database server available within the current subscription.

06 Repeat steps no. 1 – 5 for each subscription created in your Microsoft Azure cloud account.

01 Sign in to the Microsoft Azure Portal.

02 Navigate to Microsoft Defender for Cloud blade at https://portal.azure.com/#view/Microsoft_Azure_Security/SecurityMenuBlade/~/0.

03 In the left navigation panel, under Management, choose Environment settings.

04 Under Azure, click on the name (link) of the Azure subscription that you want to access.

05 In the left navigation panel, under Settings, choose Defender plans to access the Defender for Cloud pricing plans available for the selected Azure subscription.

06 Under Cloud Workload Protection (CWP), find the Databases pricing plan, and choose On from the Status column, to enable Microsoft Defender for Cloud for SQL database servers. Choose Save from the top menu to apply the configuration changes. This will automatically enable the Vulnerability Assessment (VA) feature for your SQL database servers. Setting an external storage account is not required for Express configuration.

07 Repeat step no. 4 – 6 for each subscription available within your Microsoft Azure cloud account.

01 Run account list command (Windows/macOS/Linux) with custom output filters to list the IDs of the cloud subscriptions available in your Azure cloud account:

az account list
  --query '[*].id'

02 The command output should return the requested subscription identifiers (IDs):

[
	"abcdabcd-1234-abcd-1234-abcdabcdabcd",
	"abcd1234-abcd-1234-abcd-abcd1234abcd"
]

03 Run account set command (Windows/macOS/Linux) to set the selected Azure subscription to be the current active subscription (the command does not produce an output):

az account set
  --subscription abcdabcd-1234-abcd-1234-abcdabcdabcd

04 Define the configuration parameters for the account get-access-token command, where the "pricingTier" parameter value is set to "Standard" to turn on the Defender for Cloud pricing plan for Azure SQL database servers. Save the configuration document to a JSON file named enable-defender-for-sql-databases.json and replace the highlighted details, i.e. \<azure-subscription-id\>, with your own Azure account subscription ID:

{
	"id": "/subscriptions/<azure-subscription-id>/providers/Microsoft.Security/pricings/SqlServers",
	"name": "SqlServers",
	"type": "Microsoft.Security/pricings",
	"properties": {
		"pricingTier": "Standard"
	}
}

05 Run account get-access-token command (Windows/macOS/Linux) with the configuration document defined at the previous step (i.e. enable-defender-for-sql-databases.json file), to enable Microsoft Defender for Cloud for all the SQL database servers provisioned in the selected subscription. This will automatically enable the Vulnerability Assessment (VA) feature for your SQL database servers. Setting an external storage account is not required for Express configuration:

az account get-access-token
  --query "{subscription:subscription,accessToken:accessToken}"
  --out tsv | xargs -L1 bash -c 'curl -X PUT -H "Authorization: Bearer $1" -H "Content-Type: application/json" https://management.azure.com/subscriptions/$0/providers/Microsoft.Security/pricings/SqlServers?api-version=2018-06-01 -d@"enable-defender-for-sql-databases.json"'

06 The command output should return the information available for the enabled pricing tier:

{
	"id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/providers/Microsoft.Security/pricings/SqlServers",
	"name": "SqlServers",
	"type": "Microsoft.Security/pricings",
	"properties": {
	"pricingTier": "Standard",
		"freeTrialRemainingTime": "PT0S"
	}
}

07 Repeat steps no. 3 - 6 for each subscription available in your Microsoft Azure cloud account.

01 Sign in to the Microsoft Azure Portal.

02 Navigate to All resources blade available at https://portal.azure.com/#browse/all to access your Azure cloud resources.

03 From the Type equals all filter box, select Equals and choose SQL server to list only the SQL database servers provisioned for the selected Azure subscription.

04 Click on the name (link) of the SQL database server that you want to configure.

05 In the blade navigation panel, under Security, select Microsoft Defender for Cloud.

06 On the Microsoft Defender for Cloud page, check the Enablement Status attribute value and make sure that Microsoft Defender for SQL is enabled, then choose (Configure) to access the Vulnerability Assessment (VA) settings available for the selected SQL server.

07 In the VULNERABILITY ASSESSMENT SETTINGS section, perform the following actions:

1. Choose Select Storage account under Storage account to select an existing Azure storage account, required to enable the Vulnerability Assessment feature. Select the name of the storage account that you want to use for VA from the Choose storage account list.
2. Select ON under Periodic recurring scans to enable the Periodic Recurring Scans setting for the Vulnerability Assessment (VA) feature.
3. In the Send scan reports to configuration box, provide a semicolon separated list of email addresses to which Vulnerability Assessment (VA) scan reports and alerts will be sent.
4. (Optional) Check the Also send email notification to admins and subscription owners checkbox to enable sending Vulnerability Assessment email notifications to admins and subscription owners for the selected SQL database server.
5. Choose Save to apply the configuration changes.

08 Repeat steps no. 4 – 7 for each SQL database server that you want to configure, provisioned in the selected Azure subscription.

09 Repeat steps no. 3 – 8 for each subscription created within your Microsoft Azure cloud account.

01 Run Update-AzSqlServerVulnerabilityAssessmentSetting cmdlet with the name of the SQL server that you want to configure and the name of the associated resource group as the identifier parameters to enable Vulnerability Assessment (VA) for the selected SQL database server by setting a storage account. Set the -RecurringScansInterval parameter to Weekly to ensure that the Periodic Recurring Scans setting is enabled. Replace <storage-account-name> and <notification-email-address> with your own information. The selected storage account must be from the same Azure subscription and location:

Update-AzSqlServerVulnerabilityAssessmentSetting
-ServerName "cc-prod-db-server"
-ResourceGroupName "cloud-shell-storage-westeurope"
-StorageAccountName "<storage-account-name>"
-RecurringScansInterval Weekly
-NotificationEmail @("<notification-email-address>")

02 The command output should return the Vulnerability Assessment (VA) settings:

ServerName               : cc-prod-db-server
ResourceGroupName        : cloud-shell-storage-westeurope
StorageAccountName       : <storage-account-name>
ScanResultsContainerName : vulnerability-assessment
RecurringScansInterval   : Weekly
EmailAdmins              : True
NotificationEmail        : {<notification-email-address>}

03 Repeat steps no. 1 and 2 for each SQL database server that you want to configure, available within the current Azure subscription.

04 Repeat steps no. 1 - 3 for each subscription created in your Microsoft Azure cloud account.