Azure SQL Vulnerability Assessment – now with PowerShell support!

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 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 . 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 () 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 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 -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 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 , check out this

Channel 9 demo

.

Try it out and let us know what you think!

 

This article was originally published by Clustering For Mere Mortals. You can find the original article here.