Vulnerability assessment is probably one of the most underrated new security features in SSMS 17. This feature is not new to SQL Server though, it already exists on Azure SQL DB but it was finally introduced in SSMS with the version 17.
If you are supporting a SQL Server environment where you have to face an exhaust external or internal IT audit process every quarter or perhaps monthly basis, I think this tool is great helping you to being proactive making you aware of all the security changes or threats you must remediate before auditors arrive to your desk asking for questions.
The only requirement for this new SSMS feature, is to have the 17.4 or latest SSMS version installed on your client machine, and a valid path to store the vulnerability scan result:
According to Microsoft, the VA (let’s use the short name from now on) runs lightweight scan directly to your SQL Server instance or a specific database, looking for security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. It also checks for security best practices to be in place in order to make your database environment safe.
The VA results contains all the security checks (passed or failed) , organized in three categories: high, medium and low risk.
Report header:
Security checks detail:
If we take a look at the example above, we can see how the Vulnerability assessments provides guidance on how to resolve the failed checks, in case the security check doesn’t apply to your environment you can dismiss such security check and it will no longer appear on future checks.
Let’s dig a little bit more about the security check ID VA1287 – Sensitive data columns should be classified:
As you noticed, the screenshot has a highlighted section where the scan result shows a couple of table columns not in compliance with GDPR. As you may know GDPR is s a regulation in EU law on data protection and privacy, which goes active on 05/25; so this is one major reason why you should consider using the VA tool.
If you scroll down to the bottom of the AV1287 check, the report provides a detailed description and the impact of having this vulnerability active in your environment:
So, at this point you have your scan results with the description and impact of each one. Now let’s take a look what the tool recommends as remediation:
As you can see there is no rocket science here, the vulnerability scan recommends to implement security measures as Always encrypted or Dynamic Data Masking (SQL Server 16 +) to protect the sensitive information in your environment, and the most important part it helps you to stay away from trouble with your internal or external IT audit.
Let’s not forget about the baseline option:
This is very useful option to avoid false positives, let’s say you determined one of the scan results does not apply in your environment and you don’t want to have it in this report. The solution is click on the “Approve as baseline”, so the next scan will show the same result marked as passed.
Thanks for reading!
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.
