Rajendra Gupta
SQL Data Classification Report in SSMS version 17.5

SQL data classification – Add sensitivity classification in SQL Server 2019

October 31, 2018 by

SQL Server 2019 offers powerful new features to help in safeguarding your data and complying with various privacy regulations, which we’ll be covering in this article

SQL Server Management Studio 17.5 introduced a new feature for data discovery and data classification into different categories. This helps to discover sensitive data such as SSN number, credit card number, bank account details, personal records etc. It is very critical for the data to be secure and compliant under SOX, PCI, and GDPR for each organization. The Classify data features adds extended properties to the columns to specify the label and the information type.

This option is available under (Right click on Database) Tasks->Data Discovery and Classification ->Classify Data in SSMS version 17.5 and above.

Classify Data in SSMS version 17.5

If we run the ‘Classify Data’ on WideWorldImporters database, we get the ‘Data Classification’ report.

We can see that we have the option to classify the information type. We can decide whether this column contains contact info, banking, credit card, date of birth, financial, National ID etc. Classify Data Report in SSMS version 17.5

Similarly, we can choose the sensitive level as shown below:

Sensitivity label in Classify Data in SSMS version 17.5

Therefore, we can choose the appropriate information type and sensitivity level and apply the recommendation accordingly. Once we apply and save the recommendation, we can view the SQL Data Classification Report that shows the information such as Classified columns, tables containing sensitive data.

SQL Data Classification Report in SSMS version 17.5

Once we have defined the information type and sensitivity level, we can go to particular column properties and can see that this information is added as part of extended properties as shown below:

Extended Properties

We can see all the columns in a database where we defined the data classification.


View the data classification report using query

So far, we have explored the way Classify Data in SQL Server Management Studio using the extended properties in the columns. Now let us view the enhancements in the SQL Server 2019.

SQL Server 2019 and Data Classification

Before we move further, you can go through below articles to get familiar with SQL Server 2019.

SQL Server 2019 provides enhancements to Data classification feature. SQL Server 2019 provides new command ‘ADD SENSITIVITY CLASSIFICATION’. We can define the information type and label for our sensitive data similar to SQL Server Management Studio 17.5 and above Classify data. We will see the difference in the later section of the article.

The syntax for command ‘ADD SENSITIVITY CLASSIFICATION’ is as shown below.


[Object names]

[schema_name].[table_name].[column_name]

Label

  • Public
  • General
  • Confidential – GDPR
  • Confidential
  • Highly Confidential
  • Highly Confidential – GDPR

Information Type

  • Banking
  • Contact Info
  • Credentials
  • Credit Card
  • Date of Birth
  • Financial
  • Health
  • Name
  • National ID
  • SSN
  • Other

Now we will define the data classification using SQL Server 2019 ADD SENSITIVITY CLASSIFICATION.

Data Classification Columns

Run the below query to define the data classification for the above table and columns.


In this example, we defined data classification individually for each table. Let us assume that we want to define the same label and information type for two columns from different tables. We can either run the separate statement or club them into a single statement.

In the below example, we will define the same data label (Confidential) and information type (Financial) for the SpecialDeals and StockItems table.

Target table and columns to classify data.

Therefore, we can run the below query. In this query, we have added both the tables in a single statement of ‘ADD SENSITIVITY CLASSIFICATION’.


ADD SENSITIVITY CLASSIFICATION' SQL Sever 2019

Now, let us look at the column property similar way we checked above after specifying data classification using SQL Server Management Studio 17.5. We do not find any extended property in SQL Server 2019 way of classifying data.

Extended Properties

SQL Server 2019 does not store information as the extended properties. Instead, it adds metadata about the sensitivity classification information as per defined columns. We can view the information about classified columns from the system view sys.sensitivity_classifications.


view information using sys.sensitivity_classifications

Audit behaviour for classified data in SQL Server 2019

Suppose we have defined auditing on the database table. Later we have defined the data classification using ADD SENSITIVITY CLASSIFICATION. Do we need to make changes in the data auditing in order to capture the related information about the data classification?

Firstly, we can see that the on below columns we have not defined any data classification. We will define it later.

taget columns to specify classfication for demo

Now let us create the SERVER AUDIT first using below query:


Create and Alter server audit

This query creates the audit file in C:\sqlshack\Audit folder. We can see a server audit file in this path.

Audit file in the specified folder

  • Define Database Audit on object level and specify the operation we want to capture for example select, insert, update etc.

Create DATABASE AUDIT SPECIFICATION

This database audit will capture the event for select statements on the Application. People Table.

  • Now let us classify data using ‘ADD SENSITIVITY CLASSIFICATION’ in SQL Server 2019 for the columns

'ADD SENSITIVITY CLASSIFICATION' in SQL Server 2019

  • Perform some select activity on the [Purchasing].[SupplierTransactions] the table in the WideWorldImporters database. These select events will be captured by the database audit created in the above step
  • Now let us analyze the database audit data collected in the file created on the path specified. We will analyze the Database Audit using the sys.fn_get_audit_file function

We observed so far that we have not made any changes in the database audit. SQL Server 2019 adds a new column data_sensitivity_infomation in the audit file to analyze the database audit.

In the below query, we can see that we provided input to the function as audit file created by the database audit.

sys.fn_get_audit_file( ‘C:\sqlshack\Audit\DataClassficationSQL2019_*.sqlaudit’

In addition, in the output, we will analyse the information from data_sensitivity_information column.

data_sensitivity_information = CONVERT(xml, data_sensitivity_information)


View the information using sys.fn_get_audit_file

We can see that the select statement is captured in the database audit. Let us click on the XML link in the data_sensitivity_column. It returns the below information for the label and information_type.

XML information return by the data_senstivity_columns

<sensitivity_attributes>
<sensitivity_attribute label=Confidentialinformation_type=Financial/>
</sensitivity_attributes>

Now let us verify the same using the sys .sensitivity_classification. we can see the database audit collected the same information without modifying anything in the audit.

view information using sys.sensitivity_classifications .

  • Now let us add data classification to one more column in this [Purchasing].[SupplierTransactions] table.

LABEL = ‘Highly Confidential’, INFORMATION_TYPE = ‘Banking’

  • Let us view the database audit result again:

View database audit result

Click on the XML link and we can view both the label and information type for the table.

<sensitivity_attributes>
<sensitivity_attribute label=Highly Confidentialinformation_type=Banking/>
<sensitivity_attribute label=Confidentialinformation_type=Financial/>
</sensitivity_attributes>

XML output return by the audit

We can verify the same information in below image as well.

verify the classification data information.

Conclusion

Add sensitivity classification is a nice enhancement in SQL Server 2019. We can identify the columns from the SQL Server Management Studio Classify Data report and add the metadata without the extended properties. You can work on this to take a step closer to achieving full data privacy regulation compliance.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
97 Views