To protect SQL Server databases from vulnerabilities, organizations must continually assess database systems to determine vulnerabilities, if any. This is a beneficial proactive approach to analyze databases for potential vulnerabilities and deviations such as excessive permissions, exposure of sensitive data, misconfiguration, etc. This is the requirement for meeting compliance as well.
Currently, with SQL Server, we need to use custom scripts or external tools to detect and remediate these vulnerabilities. In this tip, we will see how SQL Server Management Studio 17.4 can be useful in this case.
Microsoft recently released SQL Server Management Studio (SSMS) 17.4 which contains many enhancements and new features over previous SSMS versions. We have explored SSMS 17.3 features in my previous tip.
It is very important for organizations, for security as well as compliance, to identify and fix any security vulnerabilities. Some of the database vulnerabilities can be:
- Extensive user and group privileges
- Sensitive data such as credit card, bank account number, etc.
- SQL injection
- Instance or Database configurations
- Orphaned Database users, Roles, Groups
SQL Server Management Studio v17.4 contains an important enhancement that will help SQL Server DBAs scan their databases for security vulnerabilities and remediate with the suggestions provided.
To use this feature, install or upgrade the SSMS version to SSMS v17.4. It can be downloaded from the link Download SQL Server Management Studio.
- Download SQL Server Management Studio 17.4 - use this link to download SSMS 17.4 version.
- Download SQL Server Management Studio 17.4 Upgrade Package (upgrades 17.x to 17.4) - if you have an existing SSMS 17.x installation can use this download link.
For my case, SSMS 17.3 is installed so I have downloaded the upgrade package and upgraded to SSMS v17.4.
SQL Vulnerability Assessment
The SQL Vulnerability Assessment (VA) service employs a knowledge base of rules that flag security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft’s best practices.
VA reports can be useful to:
- Generate the database Vulnerability assessment reports
- Set baseline for database Vulnerability for your environment
- Remediate the Vulnerability available in the database especially for the critical databases having financial data.
To use this SQL Vulnerability Assessment (VA), right click on a database and select Tasks > Vulnerability Assessment > Scan for Vulnerabilities...
We can specify the location where we want to save the scan results. By default, it stores it in the below path:
C:\Users\user_name\Documents\SQL Server Management Studio\Vulnerability Assessment Reports
When the Vulnerability Assessment scan is complete, the scan report is automatically displayed. This report presents an overview of your security state with the following details:
- Total number of security checks
- Total number of failing checks with their risk profile as high, medium, and low
- Details of failed and passed rules including recommendations to resolve the failed checks
Below is the Vulnerability Assessment report generated for my database.
We can see in the report that out of the total 52 security checks 6 checks failed with 1 high risk, 2 medium risks, and 3 low-risk assessments.
Click on the Passed tab to see the security checks rules that passed.
Remediate the failed security checks
Click on the failed result to get the details of the findings with description, impact and remediation suggestions or scripts to resolve the issue.
After reviewing the assessment results, if we accept the security risk then we can accept a baseline in the environment. The baseline is considered as passed in the subsequent scans and will not be marked as failed scans.
To accept the security risk, click on Approve as Baseline.
This gives a warning that you are now setting the current result set as your approved baseline for the security check in the database.
Once the baseline is set, it marks the security check as a successfully set baseline.
Resolve Security Assessment Risk
The Vulnerability Assessment report also provides the details of the failed assessment. For example, in my report, it shows VA1054 - Excessive permissions should not be granted to PUBLIC role on objects or columns.
After clicking on it, it gives the permissions having issues with the Public role.
In the below screen shot, it provides remediation information along with the remediation script.
To run the script, it provides an option to simply run the query using click on Open in Query Editor Window.
Similarly, you can either resolve the assessment result using the remediation script or accept as an approved baseline. After we have completed setting up Rule Baselines and resolving the assessment results, run a new scan to view the customized report. VA now reports only the security issues that deviate from your approved baseline state.
The thing to note is that currently checks are done with a set of Microsoft recommended security rules and currently there is no option to create personalized rules.
Open Existing Vulnerability Assessment Reports
To open existing reports, right click on a database and select Tasks > Vulnerability Assessment > Open Existing Scan and select the scan you want to view.
We can see that the scan results are saved in JSON format.
Opening an existing assessment report will open in read-only mode.
We can see that rule baseline now reports as Passed as per the custom baseline.
VA can now be used to monitor the database to manage a higher level of security. We can easily track security vulnerabilities in the database time to time and take actions.
Note: The Vulnerability Assessment is supported for SQL Server 2012 and later.
- This new feature of scanning database for security issues and displaying the current security state in a dashboard format is a great add-on to SSMS. Explore this in your environment and resolve the risks.
- Download and read more about SSMS v17.4.
- Explore SQL Server 2017 What's new in SQL Server 2017.
- Read more about SQL Server 2017 tips.
- Explore Database Scope Options in SQL Server.
Last Update: 2018-02-02
About the author
View all my tips