SQL Server Security Vulnerability Assessment Tool in SSMS 17.4

By: Rajendra Gupta | Updated: 2018-02-02 | Comments (1) | Related: > Security

Problem

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.

Solution

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

SSMS Vulnerability Assessment report path

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.

SSMS Vulnerability Assessment report passed checks

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.

SSMS Vulnerability Assessment report failed checks

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.

SSMS Vulnerability Assessment report approve 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.

SSMS Vulnerability Assessment report approve baseline warning

Once the baseline is set, it marks the security check as a successfully set baseline.

SSMS Vulnerability Assessment report - 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.

SSMS Vulnerability Assessment report - details of failed assessment

In the below screen shot, it provides remediation information along with the remediation script.

SSMS Vulnerability Assessment report - remediation script

To run the script, it provides an option to simply run the query using click on Open in Query Editor Window.

SSMS Vulnerability Assessment report - remediation script execute

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.

SSMS Vulnerability Assessment report - baseline output

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.

SSMS Vulnerability Assessment report - open existing scan

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.

Next Steps

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.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-02-02

Comments For This Article

Friday, February 2, 2018 - 1:09:59 PM - Jimbo99

Typical, security people have to bless the tools for DBAs to actually do their jobs. Just me, but the security issues are mostly disgruntled IT people, it's what I refer to as the enemy from within. Those that really add no value other than to break things to justify their security careers. Most data breaches I've ever seen are the internal hacks and information thefts from the control freaks that have to be the security experts. These people are like the most negative people you could ever be around, they're suspicious of everyone except themselves. All they do is shadow & spy on everyone and security breaches happen in spite of their gestapo-like presence. I mis-trust them more than I mis-trust any co-worker that uses the database to support operations in the business model of the corporation. Security types are like CIA or KGB.

We all know who the embezzlers are, it's most often the executives. Take the Wells Fargo banking issue. Tell me that as widespread as that was, that the top execs didn't know it was going on, the security people looked the other way and it went on for as long as it did. Any server that has to be open to connection is a security risk. A server needs to be updated for the OS or even Database patches, whether it's a virtual machine or not, still vulnerable. IT Security people remind me of security at a gated community, they aren't real police officers, they might as well be your neighborhood watch program of volunteers. It's comical, the SSMS itself, the GUI is a security risk. So employers require Powershell level of sophistication, which, again is yet another security risk. The only server that is secure is the local machine that never connects to anything except the power outlet to boot up & run & used by a single individual as the administrator. Nobody else even knows it exists. Take it off-line to any networked connection, internet or intranet.

Sorry for the rant, but the enemy too often is the one's you work with rather than the hackers or competition that It security is so fearful of as the enemy. They don't know you and hae to have control enough to turn off your accounts, and often so that you are completely unaware of them screwing with you. Too often, virtually every threat(s) they shut down against database & informatics staff are fabricated nonsense. I worked for a newly hired director that would lock up a server with the classic 3 failed password attempt every night as he was dismantling the staff he inherited to create openings for his new empire that he was building. For 3 years that server was created & configured, never had a single problem. Rhat is until he gets hired, learns of it's existence and every night that server was locked out. I had to get a security expert to reset the admin password everyday. I mean look at the ticket model, it's designed to track IT staff. I feel for IT today, used to be able to do your job and earn a living and now they spend more money to fabricate security issues with the MIB, the no smile guys, have no sense of humor. Watch the Seinfeld episode where the security guy at the front desk when Jerry Seinfeld goes to pick up Elaine in that one episode. Everything to them is a red alert, nothing can just be a normal daily routine where you are just living your life or doing your job.