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 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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.