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 on SSMS, don’t worry, you can download it here.

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

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 passed. It lists each item and assigns an appropriate a risk level.

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 really 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, 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.

You may have noticed in the two above screen shots the 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 are marked as passed in subsequent scans and will note reason for passing as Per 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 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 severs stand.