By now you all know how hackers are having their way with business all over the world. I don’t need to give examples to remind you of that. Some are mentioned here and here and I’ve written a number of blogs about the importance of protecting the database here, here and here.
So instead of talking about those issues again, let’s dive right in and discuss one of the simplest ways to identify typical vulnerabilities in your SQL Server database. This is a tool that is already available to you that can significantly minimize your risk.
Microsoft provides a tool called the Vulnerability Assessment tool that will scan your databases for typical vulnerabilities. These include configuration errors, excessive permissions, and permissions granted to users vs roles, among others. These checks look for violations of best practices for managing a database. Before this tool was released, one had to use a third party vulnerability assessment tool like Trustwave’s Appdetective Pro, or manually run scripts to find such vulnerabilities.
How This Assessment Tool Compares With Third-Party Tools
I have used third party tools like Trustwave’s and Impreva’s, to identify vulnerabilities in customer systems, and I have used Microsoft’s Vulnerability Assessment (VA) as well. While I have not produced a master list of vulnerability checks that are done in each system in order to do a direct comparison, it feels to me that the VA checks for fewer vulnerabilities. Also, Appdetective Pro adds other features like a discovery tool, a penetration test, and a user rights review (more on that later), but here we will focus mostly on the vulnerability assessment tool.
If you have not taken any steps to secure your database, then using the SQL Server Vulnerability Assessment tool, and taking action based on its recommendations, will probably get you 90% of the way to a secure database. I am not suggesting that you should stop there. 90% is not good enough. But 90% is much better than 0%, which is where you might be if you haven’t run any vulnerability scan at all.
An Overview Of The SQL Server Vulnerability Assessment Tool
I will mention a few highlights here to give a sense of what kinds of things are covered and will provide a link below to a comprehensive guide provided by Microsoft.
The SQL Vulnerability Assessment Tool compares the configuration of your database to Microsoft best practice rules for database management from a security perspective. According to Microsoft’s guide at this point 87 rules are checked, but some only apply to later versions of SQL server. The rules are broken down into six categories.
- Authentication and Authorization
- Auditing and Logging
- Data Protection
- Installation Updates and Patches
- Surface Area Reduction
Authentication and Authorization
These rules ensure that only the right people are able to connect to your database. These address the confidentiality and integrity principles of the Information Security Triad. Authentication deals with ensuring that the users are who they represent themselves to be, and Authorization deals with what data assets they should have access to. Here are a few important rules that are checked in this category:
- Password expiration check should be enabled for all SQL logins
- Database principals should not be mapped to the sa account.
- Excessive permissions should not be granted to PUBLIC role on objects or columns
Auditing and Logging
These rules check to ensure that what gets done and seen in the database is traceable and provable. This addresses the non-repudiation principle of information security and enables forensic analysis in the event of a suspected security breach. A few sample rules checked in this category include:
- Auditing of both successful and failed login attempts should be enabled
- Auditing should be enabled at the server level
- There should be at least 1 active audit in the system
Data Protection
Data protection rules are primarily related to encryption. Addressing the confidentiality principle, these rules ensure that data is protected at rest and in transit. Rules such as these are checked:
- Transparent data encryption should be enabled
- Database communication using TDS should be protected through TLS
- Database Encryption Symmetric Keys should use AES algorithm
Installation Updates and Patches
This category would be very helpful but I am not sure the results can be trusted. When running this on a SQL Server 2012 database, it seems that the check for patches was not executed. It did not appear in the result set either as passed or failed. So I do not recommend using this tool to determine whether you are up to date on your patches until this is resolved.
Surface Area Reduction
Rules in this category address all three principles in the information security triad. They focus on protecting the database environment by reducing the threat vectors posed by external interfaces and integrations. Some interesting rules in this category include:
- CLR should be disabled
- Unused service broker endpoints should be removed
- SQL Server instance shouldn’t be advertised by the SQL Server Browser service
Comprehensive List Of Vulnerabilities
Microsoft provides this reference guide that describes all of the vulnerabilities that VA checks. The guide references which version of SQL Server each rule applies to. This guide provides a lot of good information about this tool and about securing your SQL Server database in general. It is not perfect of course. For example, the description of the check related to patches seems to have some cut and paste remnants, but there is good information there.
How Buda Employs A Vulnerability Scanner To Protect Our Customers’ Data Assets
When we perform a database security assessment for one of our customers using this tool or one of the other vulnerability scanners, we start of course by running the tool. We then examine the result set and determine the actual risk posed by each of the reported vulnerabilities in the context of the specific database, application, and customer. Often, some of the reported vulnerabilities are mitigated by processes that the organization has in place, or by the nature of the application or the data. After filtering out those that do not represent a real threat, we create a report for management that shows the action items that need to be taken, which may include further analysis.
For example, some of the rules may fail because no baseline has been created for which users should have access to a given role. Addressing this will involve a study of what roles should be active in the system and who should be granted access to them. This can result in creation of baselines for use in future scans.
Trustwave’s App Detective Pro that I mentioned earlier provides a user rights review report that may be useful for creating those baselines.
Application Authorization Schemes
The Authorization and logging related checks that these scanners perform (and the Trustwave User Rights Review) are with respect to actual database users. Many applications, however, use application based authorization. These vulnerability scanners will not be able to provide insight about user authentication or logging in those cases.
In these cases, we create a user rights review report that identifies what data assets a given application user can access, and we ensure that application logging is robust enough to provide the necessary level of granularity to support the security objectives.
Where to find it
The Microsoft Vulnerability Assessment tool is available in SSMS v 17.xxx and above. So you may have to upgrade your SSMS (free) in order to get this tool. But the good news is that it works with all currently supported SQL Server versions.
In Summary
Running the Microsoft Vulnerability Scanner can be an important part of a robust security plan for your SQL Server Databases. Running this scanner is an excellent first step to identify many vulnerabilities, some of which can be easily remediated.
It is important that an experienced SQL Server database expert implement the recommendations and that additional analysis be done beyond the results produced by the tool. Additionally, when using applications that use application level authentication, deeper study must be done to ensure the security of the data in those applications.
If you like this article, please share it with your colleagues and subscribe to our blog to get the latest updates. If you have any questions, don’t hesitate to contact us as well!