I previously wrote about Azure Defender for SQL that brings some remarkable security capabilities to your cloud-based SQL Server estates. And while you can also get some of that goodness to your on-premises servers, sometimes you just don’t need all that comprehensive solution. Well, I have good news for you. There’s also a quick and easy solution to do one off security checks against databases. And it only requires you to have Management Studio installed.
The feature is called “Vulnerability assessment for SQL Server”, and it’s available from version 2012 and onwards. Let’s take a look at how to use this great, and sometimes overlooked feature.
What is Vulnerability assessment for SQL Server
Vulnerability assessment is a service that performs a scan of your database and compares the collected data against Microsoft provided knowledge base. To quote Microsoft Docs directly, 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
Using the Vulnerability assessment
This couldn’t really get much more simpler. The only requirement is, that you need to install SQL Server Management Studio somewhere. Then connect to the SQL Server instance where your databases are hosted, pick one and run the scan.
When you start the scan, it will ask you where to store the results. As you can open existing scans, it might be a good idea to store these in some central location. Once you have selected the location, click OK to initiate the scan itself. Typically, I have never seen this take too long, before you get the results.
What is it good for?
While Vulnerability assessment is freely available for everyone, in my opinion the use cases are rather limited. Personally, I find the best use case in onboarding new environments. If you’re not familiar with it, this can give you a good idea of what issues should be prioritized when tackling security.
One thing that I am really missing is the history view. Sure, you can store multiple reports for the databases, but there’s no easy way to compare them. Adding this, similar to what we have when running SQL Assessment from Azure Data Studio, would be great. The other limitation that I’ve found quite annoying is the ability to run the assessment only against a single database, in larger environments this can be rather frustrating.
Happy New Year!
And with that, I’ll wrap up my blogging for the year 2021. Happy New Year and I’ll see you back here 2022!
Leave a Reply