SQL Vulnerability Assessment is a feature available in the latest versions of SQL Server Management Studio (SSMS). This feature is very easy to use and it will show you all the security vulnerabilities and deviations in your SQL database. This is something you can run on your most critical databases to ensure you’re properly following strict security practices and that your client’s databases are in safe hands. In this article, we will describe the process of running these scans against your databases. With the amount of data growing with each year, database security is an important aspect every DBA needs to take care of. The consequences of data breaches are severe, so they may affect your future as a DBA and severely damage your firm’s reputation.
Download the latest SQL Server Management Studio (SSMS)
Before we begin with SQL vulnerability assessment, it is important for us to download the latest version of SQL Server Management Studio (SSMS). SSMS is free to download and no license is required. As you know, SSMS is no longer a part of the SQL Server installation and there are new releases of the product getting released frequently with newer and upgraded features. As SQL vulnerability assessment is an internal SSMS tool, it is recommended to always have your SSMS version up to date. At the time of me writing this, the latest SSMS general availability (GA) version is 17.9.1, which can be downloaded here. Make sure to close all versions of SSMS on your server before installing the new one. Once the installation is finished, restart your server and proceed with the following steps.
Restore a Sample database
In order to test the new built-in feature in SSMS, you could use one of the sample SQL Server databases available for download. I used the “WideWorldImporters” sample database in this case. You could download the sample backup file and restore it on your server. Refer to this link for the backup file. You can download the file as shown on the figure below:
Once the backup file is downloaded, you can restore the database by using the script below. Remember to change the folder paths according to your needs.
restore database WideWorldImporters
from disk='C:\WideWorldImporters-Full.bak'
with move 'WWI_Primary' to 'C:\SQLData\WideWorldImporters.mdf',
move 'WWI_UserData' to 'C:\SQLData\WideWorldImporters_UserData.ndf',
move 'WWI_Log' to 'C:\SQLData\WideWorldImporters.ldf',
move 'WWI_InMemory_Data_1' to 'C:\SQLData\WideWorldImporters_InMemory_Data_1',
stats
Vulnerability Assessment
Once the database is restored, right-click on the database in SSMS, go to “Tasks”, then to “Vulnerability Assessment” and click “Scan for Vulnerabilities” as shown below:
In the next window, you will need to specify where the scans should be saved. Select the desired folder and click “Ok” to start the scan.
The scan will take some time to be completed – usually, this is quite quick. Once this is finished, you will be able to view the scan reports in SSMS. The details of the performed security checks, failed checks and other information is presented in an easy-to-read format. You can see a sample vulnerability assessment report below:
You can see that a security check with the VA1219 ID has a “Medium” risk rating and has failed. You can click on that security check to view more details:
Here, you can see that the Microsoft recommendation for this setting is “True” but it is set to “False” in your database, which means TDE is disabled on your database. Once you click “Open in Query Editor Window”, the query will be opened directly in SSMS. The screenshot below displays this query being ran in SSMS:
Here, you can see the query that is used in the back-end to return the security violation as either true or false. If you don’t intend enabling TDE on your database, you may just approve the baseline by clicking “Approve as Baseline” and then “Yes”. This is the message you get when you click on the “Approve as Baseline” option:
Once you click “Yes”, you will see the following message displayed in the vulnerability scan:
Right now, the total number of failing checks is still 4. You need to re-run the vulnerability scan in order to view the updated results. Once you run the scan again, the TDE-related check will not be failed and the total amount of failing checks will change to 3:
Upon further investigation, you will notice that the total amount of successful check will be changed to 49 – in comparison with the previous run’s 48 successful checks. The TDE check is making the difference here:
View older vulnerability scans
You can also view the vulnerability scan reports you ran earlier on your database. This can be done by opening these reports in the scan report folder. You can get to the older reports by right-clicking on the database and going to “Tasks”, then to “Vulnerability Assessment” and, finally, to “Open Existing Scan”.
Once you click “Open Existing Scan”, you can view the scan reports that were saved earlier when you ran the scans:
On the screenshot, you can see the list of previously-ran scan reports. You can just click on the one you need, select the .json file and then click “Open”. Once this is done, you will be able to view the report – and notice the “Read only” attribute:
You will see that this report is for statistical purposes only, so you won’t be able to make any changes to the security checks – no matter if they passed or failed. For example, when you click on the “VA1245” security check related to the “Surface Area Reduction” category, you won’t see an option to change the baseline setting.
On the screenshot, you can see that both the “Approve as Baseline” and “Clear Baseline” options are grayed out and cannot be changed.
Server-Level Scan – Run the scan on the master database
In the previous example, we ran the vulnerability scan to view the security checks on a user database. You can also run the scan to view the security checks on the server level. To do this, just right-click on the master database and run the vulnerability assessment scan. The steps you will need to follow are the same you went through when running the scan on a user database.
Once you click “Scan for vulnerabilities”, you will get the option to save the scan in a folder of your choice. Click “Ok” to complete the scan.
Here, you can view the scan results at the level of your SQL instance. The total number of security checks is larger when compared to that on the user database. In our case, there are some checks with a “High” risk level, so it is recommended to review them. Review each of those failed security checks and perform the required actions by either accepting them as a baseline or resolving the security issues. Once this is done, re-run the vulnerability assessment scans to confirm that all security issues are addressed and the corresponding security checks have passed.
Vulnerability Assessment Scan – MSDB database
In the previous example, we ran the assessment on the master database. In this example, we will run it on the msdb database. Just follow the same steps as before, and you will get the following window:
You can review the scan report, perform the necessary actions and run the scan again to ensure that all security risks are addressed.
Vulnerability Assessment Scan – Model database
Below is a sample scan report from the model database. As any new database you create on the SQL instance will be structured similarly to this database, it is important to address its security flags.
You will see that the scan reports yield different results when being ran on different system databases. The information may overlap, but it is still a good practice to run the vulnerability assessment scans on each indiviual database to get a general picture of what security risks there may be on the instance level. This will help us ensure that all security requirements are met and that high security standards are met on the SQL Server instances and databases we manage.
Vulnerability Scan on the Azure SQL Database
The same features we described earlier are also available for Azure SQL databases. However, this article is mainly focused on the topic of running the vulnerability assessment scans against on-premises SQL databases. So, we won’t get too much into the details of scanning Azure SQL databases. The scan reports from Azure SQL databases look liker to any other scan reports. However, one benefit of the cloud version is that the vulnerability assessment scans can be scheduled to occur periodically, with the reports being sent to a chosen email address. This makes the tool much more easy to use by automating a process we would otherwise need to perform manually. For the on-premise SQL databases, we still need to run the assessment scans manually to review the reports as there seems to be no way to schedule and automate the scans in SSMS.
Load on the systems while running the vulnerability scan
This vulnerability assessment tool is really lightweight – you may have noticed it when running the scans. Even for large databases, it doesn’t take long to create a report. Also, no changes are made in the databases we run these scans on -basically, the vulnerability assessment scan validates a database on a basis of best practices recommended by Microsoft and highlights any discrepancies. Overall, this is a great free tool which you can run periodically on the most critical databases to make sure there are no security issues. You can also develop your own custom security baseline and keep track of how well your databases and SQL server instances adhere to it.
Conclusion
In this article, we learned:
- Why it’s important to keep your SSMS version up-to-date
- How to easily test new features by restoring a sample SQL Server database from a backup
- How to run the vulnerability assessment scans on user and system databases
- How to open previous scan reports and review archived scan reports
- How lightweight and robust the SQL Vulnerability Assessment feature is
