Long awaited version of SSMS 17.4 was released few days ago and with it - new shiny Vulnerability Assessment. I decided to include it in my Autonomous Databases presentation and spend some time with it. Definitely useful but …

Disappearing checks - high risk

My first VA scan contained 52 tests results which is 12 more than in Azure Sql DB version (changed from 39 to 40 recently). Next scan that I run for different database showed just 50 tests - number mysteriously decreased by 2. After closer look at generated reports in their native json format I found an explanation which was internal error:

{ “RuleId”: “VA1069”, “Title”: “Permissions to select from system tables and views should be revoked from non-sysadmins”, “Status”: “InternalError”, “QueryResults”: null, “ErrorMessage”: “Sql Error: -2146232060\r\nLine: 1; Code: 468; Class: 16: Cannot resolve the collation conflict between \“Latin1_General_CI_AS_KS_WS\” and \“Latin1_General_100_CI_AS_KS_WS_SC\” in the UNION operation.” }

{ “RuleId”: “VA1285”, “Title”: “Sensitive data columns should be identified”, “Status”: “InternalError”, “QueryResults”: null, “ErrorMessage”: “Sql Error: -2146232060\r\nLine: 78; Code: 468; Class: 16: Cannot resolve the collation conflict between \“Latin1_General_100_CI_AS_KS_WS_SC\” and \“Latin1_General_100_CI_AS\” in the like operation.” }

WideWorldImporters can be used to reproduce that behaviour. Long shot, but in theory it could be exploited by attacker. Microsoft - it’s a bug. Please fix it.

Default location - medium risk

Am I the only person paranoid about unauthorised access to VA’s scan results and baseline file? By the way all ‘Approve As Baseline’ changes are persisted to baseline.json file in the same location as the one selected for scan. SSMS by default uses C:\Users\UserName\Documents\SQL Server Management Studio\Vulnerability Assessment Reports\ which is not acceptable for two reasons:

1. Access to above location can be less secure then access to instance that was actually scanned. The better/more tests the more dangerous it becomes. Goldmine for attacker that could also modify bassline.
2. In multi user environments (read everywhere) all failed checks approved as baseline should be synchronised and merged with existing ‘master’ baseline.json files - can of worms.

The only appropriate location at the moment seems to be secured network folder that can be shared by authorised DBAs. I don’t want to change it in SSMS every time - Microsoft please add option to set default location. I’m ok for it to be in config.json file in VA’s default location.

Granularity - low risk

In Azure Sql Database VA is run in the scope of Logical Server (all tests results accessible in one place). SSMS edition performs checks in context of selected database only. I’d like to have an option to run scan on instance level.