Azure SQL Vulnerability Assessment - now with PowerShell support!

[Ronit Reger]

First published on MSDN on Aug 02, 2018
You can now manage your SQL Vulnerability Assessments at scale using the new SQL VA PowerShell cmdlets. The cmdlets can be found in the Azure Resource Manager module, AzureRM 6.6.0, within the AzureRM.Sql package. Take a look at the AzureRM PowerShell Gallery for more details.

Managing Vulnerability Assessment with cmdlets

SQL Vulnerability Assessment (VA) is a service that provides visibility into your security state, and includes actionable steps to resolve security issues, and enhance your database security. For more details, please see the Vulnerability Assessment Getting Started guide .

Vulnerability Assessment is part of the SQL Advanced Threat Protection package for Azure SQL Database. The full package provides a single go-to location for discovering and classifying sensitive data ( Information Protection ), detecting database threats ( Threat Detection ) and running vulnerability assessments with SQL Vulnerability Assessment .

The new cmdlets can be used initially to turn on the Advanced Threat Protection (ATP) package on your database. Additionally, there are cmdlets available for setting up Vulnerability Assessment parameters, running scans, managing scan results and managing baselines. Using these capabilities, you can now run and manage assessments across a fleet of databases, all from the comfort of your PowerShell console.

Example script using Vulnerability Assessment cmdlets

The following PowerShell script demonstrates the use of the new VA cmdlets. The script walks through the following steps:

1. Turn on Advanced Threat Protection on the server


2. Set up Vulnerability Assessment for all databases on that server, including setting recurring scans to run automatically once per week.


3. Set a customized baseline value for one of the checks run by the scan


4. Run a vulnerability scan on the database, and consume the results


5. Download the scan results to an Excel summary file

Sample script

{

# set parameters - resource group, server, database and storage account
$params =  @{ rgname = "rg";
serverName = "my-server";
databaseName = "my-db";
storageAccount = "mystorage"
}
# Turn on ATP
Enable-AzureRmSqlServerAdvancedThreatProtection -ResourceGroupName $params.rgname -ServerName $params.serverName

# Set Vulnerability Assessment storage settings for all the databases in the server

Get-AzureRmSqlDatabase -ResourceGroupName $params.rgname -ServerName $params.serverName `
| where {$_.DatabaseName -ne "master"}  `
| Update-AzureRmSqlDatabaseVulnerabilityAssessmentSettings `
-StorageAccountName $params.storageAccount

# Update vulnerability assessment settings to turn ON recurring scans, and provide email to receive results
$scanNotificationEmail = @(" user1@microsoft.com ")
Get-AzureRmSqlDatabase -ResourceGroupName $params.rgname -ServerName $params.serverName`
| where {$_.DatabaseName -ne "master"}  `
| Update-AzureRmSqlDatabaseVulnerabilityAssessmentSettings `
-RecurringScansInterval Weekly `
-NotificationEmail $scanNotificationEmail `
-EmailAdmins $true

# Set Vulnerability Assessment baseline for rule VA1143 on all the databases in the server
$ruleId = "VA1143"
$baselineResult = @( '1')
Get-AzureRmSqlDatabase -ResourceGroupName $params.rgname -ServerName $params.serverName `
| where {$_.DatabaseName -ne "master"}  `
| Set-AzureRmSqlDatabaseVulnerabilityAssessmentRuleBaseline `
-RuleId $ruleId `
-BaselineResult $baselineResult

# Run a new scan on a database
$scanId1 = "custom-scan1"
$scanJob = Start-AzureRmSqlDatabaseVulnerabilityAssessmentScan `
-ResourceGroupName $params.rgname `
-ServerName $params.serverName `
-DatabaseName $params.databaseName `
-ScanId $scanId1 `
-AsJob

$scanJob | Wait-Job
$scanRecord = $scanJob | Receive-Job

# Convert the raw scan results to an Excel file
$convertScanResult = Convert-AzureRmSqlDatabaseVulnerabilityAssessmentScan `
-ResourceGroupName $params.rgname `
-ServerName $params.serverName `
-DatabaseName $params.databaseName `
-ScanId $scanId1

# Download the scan results Excel summary file
$connectionStringToStorageAccount = "DefaultEndpointsProtocol=https;AccountName=......."
$convertedScanResultsDownloadLocalFolder = "C:\ScanResults\"
$storageAccountContext = New-AzureStorageContext -ConnectionString $connectionStringToStorageAccount
$convertScanResultSplitted = $convertScanResult.ExportedReportLocation -split "/"
$containerName = $convertScanResultSplitted[3]
Get-AzureStorageBlobContent -Blob ($convertScanResult.ExportedReportLocation -split $containerName + '/')[1] `
-Container $containerName `
-Destination $convertedScanResultsDownloadLocalFolder `
-Context $storageAccountContext
}

Reference

For a full set of Azure SQL-related PowerShell cmdlets, take a look at the Azure RM SQL PowerShell documentation. The new cmdlets supporting Advanced Threat Protection and SQL Vulnerability Assessment are:

Cmdlet	Usage
Enable-AzureRmSqlServerAdvancedThreatProtection	Enables Advanced Threat Protection on a server.
Get-AzureRmSqlServerAdvancedThreatProtectionPolicy	Gets the Advanced Threat Protection policy of a server.
Disable-AzureRmSqlServerAdvancedThreatProtection	Disables Advanced Threat Protection on a server.
Update-AzureRmSqlDatabaseVulnerabilityAssessmentSettings	Updates the vulnerability assessment settings of a database
Get-AzureRmSqlDatabaseVulnerabilityAssessmentSettings	Returns the vulnerability assessment settings of a database
Clear-AzureRmSqlDatabaseVulnerabilityAssessmentSettings	Clear the vulnerability assessment settings of a database
Set-AzureRmSqlDatabaseVulnerabilityAssessmentRuleBaseline	Sets the vulnerability assessment rule baseline.
Get-AzureRmSqlDatabaseVulnerabilityAssessmentRuleBaseline	Gets the vulnerability assessment rule baseline for a given rule.
Clear-AzureRmSqlDatabaseVulnerabilityAssessmentRuleBaseline	Clears the vulnerability assessment rule baseline. First set the baseline before using this cmdlet to clear it.
Start-AzureRmSqlDatabaseVulnerabilityAssessmentScan	Triggers the start of a vulnerability assessment scan
Get-AzureRmSqlDatabaseVulnerabilityAssessmentScanRecord	Gets all vulnerability assessment scan record(s) associated with a given database.
Convert-AzureRmSqlDatabaseVulnerabilityAssessmentScan	Converts vulnerability assessment scan results to an Excel file

Get started now with VA PowerShell Cmdlets

Get started now with managing your Azure SQL vulnerability assessments at scale using PowerShell cmdlets.

Install the latest Azure Resource Manager Module containing the new cmdlets at the AzureRM PowerShell Gallery .

Documentation for all supported Azure SQL-related PowerShell cmdlets can be found at Azure RM SQL PowerShell .

For more details on the SQL Vulnerability Assessment, see the SQL Vulnerability Assessment documentation .

To learn more about VA, and see an assessment in action on Azure SQL Database, check out this Channel 9 demo .

Try it out and let us know what you think!