The New SQL Server Vulnerability Assessment Tool Goes GA

Feb 1, 2018

By Kevin Kline

The SQL Server Vulnerability Assessment tool (VA) is a feature within SQL Server Management Server (SSMS) 17.4 that scans your SQL Server instances of version 2012 and later, identifies security issues, and suggests fixes to the vulnerabilities it finds. It works for on-premises SQL Server and Azure SQL Database, whether housed on physical or virtual servers.

The tool helps ensure data privacy standards are met and adhered to, such as the EU’s new GDPR legal framework; monitors database environments, where changes are frequent and difficult to track; and aids with audit or compliance reporting requirements, which can be answered with a security report.

VA runs a scan directly against a SQL Server database based upon a large knowledgebase of rules related to security vulnerabilities, security best practices, configuration recommendations, permission best practices, and awareness of sensitive data.

How Does the SQL Server Vulnerability Assessment Tool Work?

VA is a straightforward and easy to use tool that provides a simple way to assess a given SQL Server’s database security status. You can access the feature in SSMS by selecting then right-clicking a database, then selecting Tasks ? Vulnerability Assessment ? Scan for Vulnerabilities.

Run the VA: Once selected, the first thing VA requests is a path on the local file system where you would like to save the scan results. The scan is very lightweight, taking only a few seconds to run. Don’t blink or you’ll miss it! It is entirely read-only and will not make any changes to your database.
Review the Report: Once processing is complete, VA will display a Vulnerability Assessment Result pane in SSMS. The report will tell you a variety of things about the security of your SQL Server database, ranging from the number of security checks performed, the number of security checks that failed, the number and type of security incidents discovered (categorized as either high-, medium-, or low-risk), and then provide detailed listings of the security checks that failed and those that were passed. The report also shows a map of sensitive data discovered within the database, including recommendations on which built-in features of SQL Server might best protect that data. (Note that when you run a VA scan on an on-premises SQL Server, you will see a richer set of security checks than when you run the scan on an Azure SQL Database. That’s because there are more security checks for an on-premises SQL Server since it presents a larger surface area. A VA scan from SSMS against an Azure SQL Database will look very similar to the results you see for security in the Azure Portal).
Analyze the Results: Once you’ve run the VA and seen the findings, it is now important to examine each failed security check to determine the extent of the security impact. The VA report provides details for an actionable remediation process for each failed check so that you are not left wondering how to resolve the issue. In addition, you can mark specific results as part of an acceptable baseline value. The baseline security state is basically a customization for your database security configuration, telling VA to only report on deviations from the baseline. For example, the best practice is to enable only the network protocol you are explicitly using to connect to SQL Server, usually meaning that only TCP/IP should be enabled. But let’s say your application also makes use of the Shared Memory protocol. Once VA finds that contra-indication, you can accept it as part of your baseline so that it is never flagged again. Another example would be to baseline all of your CLR assemblies, since they can represent a big security backdoor. (The baseline settings are saved together with the scan result.)

You can access past VA scan files within SSMS by selecting File ? Open or by right-clicking the database, selecting Tasks ? Vulnerability Assessment ? Open Existing Scan.

The only shortcoming in the tool at present is that there is no easy means to automate it. However, the SSMS tools team is developing features soon for both scripting and scheduling.