Vulnerability assessment for SQL Server

Applies to: SQL Server

SQL vulnerability assessment is an easy to use tool that can help you discover, track, and remediate potential database vulnerabilities. Use it to proactively improve your database security.

Note

The vulnerability assessment tool is available in SQL Server Management Studio (SSMS) prior to 19.1, for SQL Server 2012 (11.x) and later versions.

Removal of the SQL Vulnerability Assessment in SQL Server Management Studio 19.1

SQL Vulnerability Assessment in SSMS provided a way to scan and report on possible security misconfigurations on your SQL Server databases in a disconnected manner, on SQL Server 2012 (11.x) and later versions. This capability is consolidated into a comprehensive database security package, called Microsoft Defender for SQL, which allows you to perform vulnerability assessment scans and identify real-time attacks on your database at scale across cloud and on-premises resources. Defender for SQL provides customers with the latest updates to scan rules and updated threat protection algorithms.

Conversely, the SQL Vulnerability Assessment in SSMS doesn't consume findings from Defender for Cloud, nor can findings from local scans be uploaded. Additionally, the SQL Vulnerability Assessment on SSMS doesn't receive updates in real time, which may cause inconsistencies compared to the updated findings from Defender for Cloud. To prevent further confusion and inconsistencies in customers' database security experiences, we have removed the SQL Vulnerability Assessment from SSMS as of version 19.1. While SQL Vulnerability Assessment remains available in older SSMS versions, it is recommended to use Microsoft Defender for SQL to evaluate your environment's security configuration, regardless of your SSMS or SQL version.

For more information, see Enable Microsoft Defender for SQL Servers on machines and Scan your SQL Servers for vulnerabilities.

For Azure SQL Database, Azure Synapse Analytics, and SQL Managed Instance, use Microsoft Defender for SQL Database.

Vulnerability assessment features

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

The VA service runs a scan directly on your database. The service employs a knowledge base of rules that flag security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft's recommended 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.

Prerequisites

This feature is only available on SQL Server Management Studio (SSMS) v17.4 or later. You can find the latest version here.

Get started

To run a vulnerability scan on your database, follow these steps:

1. Open SQL Server Management Studio.

2. Connect to an instance of the SQL Server Database Engine or localhost.

3. Expand Databases, right-click a database, point to Tasks, select Vulnerability assessment, and select Scan for Vulnerabilities...

4. You can run a scan that checks for server-level issues by scanning one of the system databases. Expand System Databases, right-click the master database, point to Tasks, select Vulnerability Assessment, and select Scan for Vulnerabilities...

Tutorial

Use the following steps to run and manage vulnerability assessments on your databases.

1. Run a scan

The Scan For Vulnerabilities dialog allows you to specify the location where scans are saved. You can leave the default location or select Browse... to save the scan results to a different location.

When you are ready to scan, select OK to scan your database for vulnerabilities.

Note

The scan is lightweight and safe. It takes a few seconds to run, and is entirely read-only. It does not make any changes to your database.

2. View the report

When your scan is complete, your scan report is automatically displayed in the primary SSMS pane. The report presents an overview of your security state; how many issues were found, and their respective severities. Results include warnings on deviations from best practices, as well as a snapshot of your security-related settings. These settings include database principals and roles, and their associated permissions. The scan report also provides a map of sensitive data discovered in your database, and includes recommendations of the built-in methods available to protect it.

3. Analyze the results and resolve issues

Review your results and determine which findings in the report are true security issues in your environment. Drill down to each failed result to understand the impact of the finding, and why each security check failed. Use the actionable remediation information provided by the report to resolve the issue.

4. Set your Baseline

As you review your assessment results, you can mark specific results as being an acceptable Baseline in your environment. The baseline is essentially a customization of how the results are reported. Results that match the baseline are considered as passing in subsequent scans.

Once you have established your baseline security state, VA only reports on deviations from the baseline, and you can focus your attention on the relevant issues.

5. Run a new scan to see your customized tracking report

After you complete setting up your Rule Baselines, run a new scan to view the customized report. VA now reports only failing security issues that deviate from your approved baseline state.

6. Open a previously run scan

You can view the results of previously run vulnerability assessments at any time by opening an existing scan. Do so by right-clicking a database, pointing to Tasks, selecting Vulnerability Assessment, and selecting Open Existing Scan.... Select the scan results file you would like to view and select Open.

You can also open an existing scan result via the File->Open menu. Select Vulnerability Assessment... and open the scans directory to find the scan result you wish to view.

VA can now be used to monitor that your databases maintain a high level of security at all times, and that your organizational policies are met. If compliance reports are required, VA reports can be helpful to facilitate the compliance process.

Manage vulnerability assessments using PowerShell

You can use PowerShell cmdlets to programmatically manage vulnerability assessments for your SQL Server instances. The cmdlets can be used to run assessments programmatically, export the results and manage baselines. To get started, download the latest SqlServer PowerShell module from the PowerShell Gallery site. You can learn more here.

Next steps

• Running a vulnerability assessment scan on Azure SQL Database
• Channel 9 video demonstrating the use of vulnerability assessments