SQL Server Vulnerability Assessment

Introduction

SQL Server Vulnerability Assessment (VA) in SQL Server Management Studio 17.4 or later lets SQL Server scan your databases for potential security vulnerabilities and can be run against SQL Server 2012 or higher. If you are not on a newer version of SSMS, don’t worry, you can download it here.

Performance Considerationsand Benefits

Running any kind of scan against data always concerns me as performance impacts can ruin your day. Luckily, VA is lightweight and runs without performance impacts while still giving you an in-depth view of where you could improve your SQL Server’s security. The process is designed to meet data privacy standards and compliance using knowledge-based rules that look for deviations from Microsoft’s best practices.

Running an assessment

To run an assessment simply choose a database, right-click, and choose Tasks. Here, you will see Vulnerability Assessment choose that, and Scan for Vulnerabilities. If you have run one previously, you can access it here by choosing Open Existing Scan.

It will pop up a window to choose where you want the results saved. Once you click ok, the process will run.

Here, you can see my results from the AdventureworksDW2016CTP3 database. It has 6 failed items and 47 have passed. It lists each item and assigns an appropriate risk level.

Remediation steps and Scripts

Clicking on one of the listed items under failed gives you more details and remediation steps with scripts to fix it. Let’s look.

In this example, I chose an easy one. This database is not auditing for successful and failed login attempts. You can see below it gives us a description of the best practice rule not followed and provides us a query that we can run to see the results. I like this feature and it’s a handy script to keep for later use when evaluating another server’s health. It even gives us a little copy button to copy out the script and the option to open it in a query window.

If you scroll down further, you will get to the recommended remediation steps and script. If there is no script provided it will give you a link to where to find the proper documentation on how to fix the issue. In my opinion, from what I have seen, the VA does a good job explaining what’s needed to fix the issue. Always keep in mind, although this is created by Microsoft, I suggest running these in test first before production and taking the time to fully understand what it is doing.

Customization with Baselines

You may have noticed in the two above screenshots that I have drawn a box around BASELINE and Approve as Baseline. A baseline allows you to add a customization of how the results are reported. This helps to reduce clutter on future scans.

By marking the result as a BASELINE you are telling VA that this is acceptable in your environment although it may not meet best practices or regulatory standards. Anything in the future that matches the baseline is marked as passed in subsequent scans and will note the reason for passing as Per the Custom Baseline.

We can see this when I run another scan. You’ll note the report now shows I only have 5 failed (without me fixing the issue) and the additional information column shows the baseline for the reason.

SQL Server Vulnerability Assessment is a great non-third-party starting place for evaluating data privacy, security, and compliance standards and is very easy to use. Give it a try and see where your servers stand.