How to do a SQL Vulnerability Assessment in SSMS

Making sure that your SQL Server environment is secured can be cumbersome and tricky. In SQL Server 2012 (and later), Microsoft introduced a tool called, “SQL Vulnerability Assessment“, which helps in finding potential database vulnerabilities. It’s a great tool to help proactively improve your SQL Server database security.

The Vulnerability Assessment (VA) tool is available in SQL Server Management Studio (SSMS) 17.4 or later.  The ‘VA’ tool is supported for SQL Server version 2012 and later, and can also be run on Azure SQL Database. So if you’re wondering why you do not see the option in SSMS, you are most likely running an older version of SSMS (download latest SSMS version here) or running a SQL Server version older than 2012.

How to do a SQL Vulnerability Assessment

Running the VA tool is quite simple. So far it’s only database specific. You can run it by right-clicking the database in question, choosing “Tasks“, then “Vulnerability Assessment” then, “Scan for Vulnerabilities“. This brings up the following dialog box:

You see the options in this “Scan for Vulnerabilities” dialog box is minimal. It only has an option to select where to store the vulnerability report.

Click OK to being the assessment.

After the assessment is done, a “Vulnerability Assessment Results” pane is shown in SSMS (see below)

This result pane is pretty self-explanatory.

As you can see the top left section has “Total security checks” which is 54 and “Total failing checks” at 3. You can also see that I ran this against the WideWorldImporters database.

In case you’re wondering what these 54 checks/rules are, and why were they chosen:

According to Microsoft, “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.”

I’m sure the number of rules checked will increase in later versions of SQL Server and/or SSMS, but for now the number is 54.

The beauty about the VA tool is that each vulnerability (or check) provides the following:

• The T-SQL “rule” query used to run against the database (in case you wanted to copy and run it yourself).
• The remediation section offers advice on how to “fix” the failed check.
• Each vulnerability allows you the choice of “Approve as Baseline” or “Clear Baseline.” This will be saved so as you fix/clear vulnerabilities and re-run the VA tool, it doesn’t count those against you.
• It is light-weight and a read-only tool.

It’s super-easy to run and makes the process of securing and hardening the database a no brainer.

Vulnerability Assessment Tool Wish List

One thing I do wish for is an option to print the report. Right now the reports are saved as .json files. What if I just want to print the report (or save it as a .pdf) and show management what a great job I’m doing?

Guess I have to keep my fingers crossed!