SQL Server 2019 Security Tool Inadvertently Reveals Where Sensitive Data is Stored

The first step in protecting your data and ensuring your database is compliant with security benchmarks and privacy regulations such as SOX, PCI, HIPAA, and GDPR is to understand what sensitive information resides in that database.

Microsoft’s SQL Server 2019 introduces a new tool SQL Data Discovery & Classification. Built into SQL Server Management Studio (SSMS), the software allows users to discover, classify, label and report the sensitive data in their databases.

The classification engine scans a database and identifies columns containing potentially sensitive data. It then provides users with an easy way to review and apply classification recommendations, as well as to manually classify columns either by SSMS GUI or by the SQL statements ADD SENSITIVITY CLASSIFICATION. The classification state is also added to the audit log and helps monitor access to sensitive data for compliance and auditing purposes.

Common security tools perform this process outside the database. This abides by the principle of Segregation of Duties, which states that best practice is to empower DBAs to do only what they need to perform their duties, such as designing databases, managing the database as a technology, and monitoring database usage and performance.

This means that only security personnel would perform a classification scan – the application owner would review the results and label the sensitive data, and then the security person would protect it by applying audit and access controls on that sensitive data. By performing the scan outside the database, the DBAs are out of the process and miss out on the opportunity to participate in the classification of sensitive data.

While Microsoft’s new classification tool is easy to use, it reveals where sensitive data is stored by labeling it in the database itself (catalog view sys.sensitivity_classifications).

This means that, with simple queries, insider attackers, such as malicious employees or employees whose security has been compromised, and holds the VIEW ANY SENSITIVITY CLASSIFICATION permission, can track exactly which columns contain sensitive data and then access it. Indeed, it’s worth remembering here that DBAs and database accounts used by applications connecting to the database typically have the right to view both non-encrypted AND encrypted data.

The following code example returns classified columns with their corresponding classifications:

SELECT
schema_name(O.schema_id) AS schema_name,
O.NAME AS table_name,
C.NAME AS column_name,
information_type,
label
FROM sys.sensitivity_classifications sc
JOIN sys.objects O
ON sc.major_id = O.object_id
JOIN sys.columns C
ON sc.major_id = C.object_id AND sc.minor_id = C.column_id

Being able to easily pinpoint where valuable data is located sidesteps the lengthy process of scanning all application tables in search of sensitive data – a noisy approach which can trigger an incident from Behavior Analytics solutions such as Imperva Data Risk Analytics (formerly CounterBreach).

In addition, the labeling of the sensitive data can be updated with the SQL statement DROP SENSITIVITY CLASSIFICATION. An attacker that holds the server permission Control Server or the database permission ALTER, may use this in his attack to change the label of a sensitive column to non-sensitive in order to access it under the radar of Data Activity Monitoring (DAM) and other Behavior Analytics security tools.

We encourage you to use a security classifier outside the database, which empowers your security personnel, or a SQL Server classifier with the following mitigations:

• Verify that only authorized accounts have the server permission Control Server or the database permission ALTER, which allow them to execute the statement DROP SENSITIVITY CLASSIFICATION.

In addition, if you have a DAM tool, we encourage you to also add the following mitigation:

• Monitor access to catalog view sys.sensitivity_classifications
• Monitor execution of the SQL statement DROP SENSITIVITY CLASSIFICATION.

Classifying sensitive data is crucial to ensuring that data is secure and your databases compliant. By taking a few additional steps, and following our recommendations, you can protect your sensitive data from being inadvertently revealed.