Classifying SQL Data

Security professionals are tasked with maintaining the integrity and confidentiality of data.  Depending on your industry and the geographic reach of your organization the type, volume and responsibility associated with that data may vary.  Key to the success of protecting these assets is understanding the data in your possession as well as a means by which to label it. 

Identifying data can be problematic, particularly in unstructured data sets.  There are E-discovery and DLP tools that can help scan network and hard drive locations for this information.  We’ll discuss those in a future article.  Today we are going to focus on structured data maintained in SQL relational databases.   Fortunately for users of SQL Server 2012 and above, Microsoft provides a free tool that takes some of the pain out of discovering and labeling your data.  Before getting started it is worth mentioning you should have top level support and administrative policies in place to guide your data classification efforts.

SQL Data Discovery and Classification – is a fairly simple tool that is built into SQL Server Management Studio 17.5 and above.  You simply select the database you wish to evaluate and SQL will make recommendations.  From this list of recommendations, you can define and apply the labels.  SQL comes preloaded with a configuration file called the Information Protection Policy File.  This file contains a JSON structure with character strings that will be searched for within the column names.  Note I said column names. At this point SQL Data Discovery does not query your database and inspect the underlying data, it merely looks at the schema.  You could likely replicate this process by querying syscolumns.  I expect as the tool matures a deeper inspection capability will be introduced, though likely for an additional charge.  (I do have TSQL scripts that you can run to look for certain sensitive information like SSN and Credit Card)

Using SQL Data Discovery

1.      Open SSMS

2.      Navigate to the Database you’d like to scan

3.      Click Tasks - > Data Discovery and Classification -> Classify Data

4.      You will be presented with a list of columns and labeling recommendations.  You can override the recommendations by changing the drop-down selections.  The drop-down values are defined within the Information Protection Policy configuration file so you can change them to meet your needs.

5.      Check the recommendations you want and click Accept Selected Recommendations.   In the event you don’t see a column you are expecting in the recommendations, you can add a label manually by clicking “Add Classification”.  Be sure to click “Save” at the top in order for your labels to be applied.

 6.      Click View Report for a graphical representation

T-SQL Tricks

As an alternative to the GUI, you can use T-SQL to view existing classifications.

select * from sys.sensitivity_classifications

You can also use add Classification Labels in your table create schema

ADD SENSITIVITY CLASSIFICATION TO [dbo].[CustomerInfo].[DOB] WITH (label = 'Confidential - GDPR', label_id = '989adc05-3f3f-0588-a635-f475b994915b',  information_type = 'Date Of Birth', information_type_id = '3de7cc52-710d-4e96-7e20-4d5188d2590c', rank = Medium);

Pros:

·        Free to use

·        Available for SQL 2012 and above

·        Customizable Discovery and Labeling

·        Labeling unlikely to break existing application code

·        Easy to deploy

Cons:

·        Classification is based on Column Names NOT data

·        Obfuscated Column Names will be missed

·        SQL Server only, will not help with classifying external data sources

Summary:

While it does have some limitations, the SQL Data Discovery and Classification tool is an easy and free way to quickly identify and label sensitive information within your organizations relational database environment.  Particularly useful is the ability to apply labels via T-SQL, this could easily be promoted within an organization’s coding standards for table creation.  Built in reporting and sys views will also be helpful during audit processes. Lastly, SQL administrators can leverage the labels to build out security group permissions.