The regular headlines of security breaches, along with increasing pressure from governments to hold companies accountable for the breaches, are making more developers (and their managers) pay more attention to security in their IT solutions.
Automated code analysis for application code is nothing new, but database code analysis has been behind the curve for many years.
Microsoft introduced the SQL Vulnerability Assessment (VA) feature in SSMS 17.4 in December 2017. The idea behind the feature is to easily scan your database(s) for standardized security best practices. The rules of the scan are supplied by Microsoft and (at the time of writing) don't allow for user-designed rules to be implemented. Microsoft states that they are working on multiple improvements, as well as adding to the number of security checks that the tool performs.
In its current state, the tool runs from the SSMS installation and requires no internet connection. The rules that are used for the scan are installed locally to the SSMS installation and are only updated when an update for SSMS is installed.
A vulnerability scan can be performed on any database (including system databases) on any instance from SQL Server 2012 and higher (including Azure SQL Database). The scan is extremely lightweight and generally runs in seconds. The scan is also completely read-only and does not make any changes to a scanned database.
Starting a vulnerability scan is simple. Right-click on a desired database, choose the Task menu, and navigate to Vulnerability Scan. The options available are to run a new scan or open the report from a previous scan. A scan that is run will store the results in a user-defined location, but it defaults to the user's default documents location. The following screenshot illustrates the process:
Once a scan is completed, SSMS will automatically open the generated report and display it inside SSMS. At the time of writing, it is not possible to automatically export the results in a different format (for example, Word or Excel). The basis of the report is a JSON file, so any further processing would require waiting for an export functionality from Microsoft or require your ingenuity in parsing the JSON file.
The resulting report displayed in SSMS allows for further analysis of the security threats that may have been found. In the following screenshot, we can see an example scan of an AdventureWorks database:
The scan indicates that the database has passed 50 tests and failed 4. By clicking on one of the tests we can see more details about the failure/pass.
The following screenshot shows the details of the failed check ID VA1219, which states that a database should have Transparent data encryption activated:
Each check is accompanied with a range of information, including the query that was run against the scanned database. This allows us to see whether the check is correct for our database/environment.
Should a certain check be irrelevant for a database/environment, the Approve as Baseline button can be clicked. This will override the check outcome to ensure that a future scan against this database will deem the overridden check to be a pass rather than a failure.
Upon marking the failing TDE check as an acceptable value, a second scan of the AdventureWorks database provides us with 51 passes and 3 failures. The TDE check is now listed as a passing check, with the extra information being set as a custom baseline value. The following screenshot illustrates the baseline:
The Vulnerability Assessment is by no means a perfect tool, currently only covering a limited set of scenarios. It does, however, provide a good start to assessing the security threats inside a database. The tool will continue to receive updates (especially to the rules that are checked against). A further tool to help improve the security of database systems should always be welcomed.