SQL Vulnerability Assessment (VA) is an easy to use tool that you can use to identify, track, and remediate potential database vulnerabilities.
The VA service runs a scan directly on database, and follows base of rules which are based on Microsoft’s recommended best practices, and focus on the security issues that present the biggest risks to your database and its valuable data. The output of the scan includes an actionable steps to resolve each issue and provide customized remediation scripts where applicable.
VA scans does not make any changes to your database.
This feature is only available on SQL Server Management Studio (SSMS) v17.4 or later versions, and works for SQL Server 2012 and later.
Lets run a vulnerability assessments on your databases using following steps.
First open your SQL Server Management Studio, and connect to the SQL Server instance.
Now right click on database and point to Tasks then select Vulnerability Assessment then select Scan For Vulnerabilities…
Once you Scan For Vulnerabilities… a Scan For Vulnerabilities dialog box opens.
Here you can specify the location where you want to save the scan result.
Now you can either leave the default location as is or if you want to save the scan result on specific location then click on Browse… to specify the location.
After that click on OK button to scan your database for vulnerabilities.
Once the scan is completed, you will see a scan report query editor window as shown below.
The report displays brief explanation of your security state, including number of found issues and their respective severities, also includes warning related to security configurations such as database principals and roles and their associated permissions.
You can see there are 8 failing checks, which are security issues with breakup of their in terms of High Risk, Medium Risk and Low Risk. While there are 47 passing checks.
Now you can review your results and determine which findings in the report are true security issues in your environment.
[Also read: How to Identify column containing sensitive data Data discovery and classification ]
In order to understand the impact of each failing security issue and to see the reason security check failed, you can click on each each failed result in grid and use the actionable remediation information that is specified to resolve the issue as shown below.
You can also approve the failed security check results as acceptable using the approve as baseline option, so the next time you run a scan it will be marked as pass.
The baseline is essentially a customization of how the results are reported. Results that match the baseline are considered as passing in subsequent scans.
Suppose that you reviewed the assessment result for Id VA2052, and you observed that this failed security issue can be acceptable for your environment then you can mark this results as being an acceptable Baseline in your environment.
For that click on Approve as baseline button, then a warning dialog box opens, click on OK button.
Once you click on OK Button, you will see a warning message indicating that ‘There are pending baseline changes. Run a new scan to see updated results.’
So the next time when you run the VA scans, the security check result for Id VA2052 will be marked as Pass.
You can also read about Vulnerability Assessment in detail, refer Microsoft documentation – SQL Vulnerability Assessment