The SQL Vulnerability Assessment (VA) feature in now available in SSMS (SQL Server Management Studio)! I blogged about this feature in Azure recently, and hinted that SQL Vulnerability Assessment Available in SSMS would be coming soon. Well, today is that day!
Go here to download the latest version of SSMS. You will need version 17.4 for the VA feature. Once you are up and running, all you need to do is right-click to start a scan, like this:
When the scan is complete you get the results:
From there, you can select a row and examine the T-SQL used:
And you can set this result to be your baseline (see upper left, ‘Approve as Baseline’ button). And here is one of my favorite features for you to play with right now, you can see a list of columns that are candidates for enhanced security such as Dynamic Data Masking or Always Encrypted:
I will leave scanning the master database as an exercise for the reader.
You may have noticed that there is an ID column in the results, with a six character tag similar to “VA1285”. This would imply that there is a master list of items that VA is scanning for. I am not able to find any such list online at this time. I’m hopeful Microsoft will publish something soon. For now, I can point you to the main page for more information. That’s the page where you will find nuggets of info like “Vulnerability Assessment is supported for SQL Server 2012 and later”. Keep an eye on that page for future details.
Summary
To me, SSMS 17.4 is *HUGE* for two reasons. First, the VA feature. This is a wonderful thing. Even more wonderful is that we didn’t need to wait for a service pack in order to get this feature into SSMS. This is why decoupling SSMS from the installer was a step in the right direction. We get wonderful features, delivered faster.
The next step of course is automating this for all databases 🙂
Yes! That’s why I’m pushing for Microsoft to publish more documentation on this feature. Otherwise we have to scrape the T-SQL ourselves to reverse engineer. Would be great if they put this into Git, and had other users start building out similar scripts for other engines, too.
I am so glad to see this come into play for SQL as a built in functionality. Just from an auditing perspective and being in the financial district this is a huge step for SQL. Thanks for sharing this post Tom; have a good one.
Yessir, this is huge. A wonderful step forwards, IMO. Thanks for the comment Chris.
In poking around, it looks like a lot of this is already part of the Check_BP_Servers script out in the Tigerteam GitHub
I’m looking in the file here: https://github.com/Microsoft/tigertoolbox/blob/master/BPCheck/Check_BP_Servers.sql
But I don’t see the code for VA in there. I see a lot of great stuff, just not the specific stuff for VA broken out.
I’d really like to see all of this fully documented on MSDN…I wonder if Microsoft would be willing to let us create our own page? They already allow edits to BOL articles, maybe we just need to help them get this done.
Of course, I’d love to see a full project, cross platform.