PowerShell cmdlets for managing SQL Vulnerability Assessments
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 best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft’s best practices 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 scripts 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 SQL Server, supporting SQL Server 2012 and up. Now, you can also use PowerShell cmdlets to run and manage scans at scale on SQL Server 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 authenticate to the database using Windows Authentication 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!