PowerShell cmdlets for managing SQL Vulnerability Assessments

First published on MSDN on Jul 05, 2018

We are pleased to announce the availability of PowerShell cmdlets for managing SQL Vulnerability Assessments for your SQL Servers. The cmdlets can be used to run assessments programmatically, export the results and manage baselines. They enable the scenario of running assessments and managing baselines across multiple databases in your environment.

To get started, download the latest

SqlServer PowerShell module

on the PowerShell Gallery site.

Vulnerability Assessment

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. It can help you:

  • Meet compliance requirements that require database scan reports.
  • Meet data privacy standards.
  • Monitor a dynamic database environment where changes are difficult to track.

VA runs vulnerability scans on your database, flagging security vulnerabilities and highlight deviations from , such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft's and focus on the security issues that present the biggest risks to your database and its valuable data. These rules also represent many of the requirements from various regulatory bodies to meet their compliance standards.

Results of the scan include actionable steps to resolve each issue and provide customized remediation where applicable. An assessment report can be customized for your environment by setting an acceptable baseline for permission configurations, feature configurations, and database settings. This baseline is then used as a basis for comparison in subsequent scans, to detect deviations or drifts from your secure database state.

Cmdlets for managing assessments

Until now, SQL Vulnerability Assessment could be run and managed via the

Azure portal

for Azure SQL Database, and

using SQL Server Management Studio (SSMS)

for , supporting 2012 and up. Now, you can also use PowerShell cmdlets to run and manage scans at scale on installations, whether on-premises or installed on a VM.

The available cmdlets are:


Cmdlet

Usage

Invoke-SqlVulnerabilityAssessmentScan
Use this cmdlet to run a VA scan on your database. Provide the target server and database, and optionally an existing baseline, and get the scan results as output. You can to the database using Windows or using a valid credential.

Export-SqlVulnerabilityAssessmentScan
Use this cmdlet to export the results of a VA scan to an Excel file.

New-SqlVulnerabilityAssessmentBaseline
Use this cmdlet to create a new baseline for a particular VA security check. This baseline can then be added to a baseline set, which can in turn be used to run a new VA scan with customized result values. A result from a previous VA scan can be used to set the value for this baseline.

New-SqlVulnerabilityAssessmentBaselineSet
Use this cmdlet to create a new VA baseline set, which is a collection of VA baseline values for different security checks. The baseline set can be used to run VA scans with customized results, tailored to your database environment.

Export-SqlVulnerabilityAssessmentBaselineSet
Use this cmdlet to export a VA baseline set to a file. The output file can be opened and managed in SSMS.

Import-SqlVulnerabilityAssessmentBaselineSet
Use this cmdlet to import a VA baseline set from a file. It can be used to import baseline sets created by SSMS.

For a detailed reference on all SQL Server PowerShell cmdlets, see the

online documentation

.

Get started now with VA PowerShell Cmdlets

The SqlServer PowerShell module can be found on the

PowerShell Gallery

site. See the

download

instructions for more details.

For more details on working with VA in SSMS, see

Getting Started with SQL Vulnerability Assessment in SSMS

.

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!

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.