Gauri Mahajan
Columns classified using T-SQL in Data Discovery & Classification.

Advanced Data Security in Azure SQL Database – Data Discovery & Classification

June 17, 2019 by

Azure SQL supports in building and managing wide range of SQL databases, tools, frameworks etc. on the cloud. Organizations are drifting towards Azure SQL because of its obvious advantages over on-premises SQL Server and are generating, exchanging and storing data at an exponential rate on Azure. It becomes essential to safeguard, monitor our sensitive data and get alerts on anomalous activities else companies may come under fire for letting data leak and hacked.

Introduction

Microsoft SQL Azure has recently introduced Advanced Data Security capability as a single pane to help monitor and store our confidential data in a more secure way. It is an integrated security package that offers a single go-to-place for classifying, assessing, tracking, remediating, detecting malicious activities and much more. Previously, features like ‘Threat Detection & Auditing and ‘Vulnerability Assessment’ were managed separately under Advanced Threat Protection heading and now they are updated and unified under one umbrella – Advanced Data Security in Azure SQL.

In this short article series, we will gain an in-depth understanding of the following topics that come under Advanced Data Security in Azure SQL.

  1. Data Discovery & Classification (Preview feature)
  2. Vulnerability Assessment
  3. Advanced Threat Protection

Pre-requisite

This article assumes that the reader is accustomed to create a basic Azure SQL Database with minimal configurations through the Azure portal. In case, you are unaware of how to create it, I would recommend going through this great article, Microsoft Azure SQL Database – Step by Step Creation tutorial.

I have deployed database AdventureWorksDW2017 from on-premise to Azure SQL database and will be using it to demonstrate and simulate Advanced Data Security features. If you want to go over steps to restore any database in Azure SQL, you can refer to this article, Microsoft Azure, our first steps to migrate data.

Before we go ahead and talk more on the security capabilities, let’s quickly enable the Auditing settings to have complete investigation experience while handling security. Go to the Auditing blade and toggle Auditing switch from OFF to ON. Select Storage and configure basic Storage details for audit logs to get saved and select the save button as shown below. Whenever a vulnerability assessment or threat protection scan happens, logs are stored in this storage account and are subsequently used for alerts. We will talk about these scans in detail in the subsequent part.

Enabling Auditing settings in SQL Database in Azure portal.

Small note – This Azure SQL DB (AdventureWorksDW2017) is restored in the server named sqlshackdemoserver and resource group sqlshackdemo_rg as shown below in the Overview blade in the Azure portal.

AdventureWorksDW2017 DB in Azure Portal.

If you scroll down the Azure SQL database page, you will find the Advanced Data Security blade under Security heading. Click on this feature and enable it on the server level by clicking the button as shown below. It usually costs $15/month/server.

Enable Advanced Data Security on the Server in Azure Portal.

Once it is enabled, all three features supported by Advanced Data Security become accessible as shown in the screenshot below. Let’s begin with the first feature in this capability.

Advanced Data Security enabled in Azure SQL Database.

Data Discovery & Classification

Data Discovery and Classification is a preview feature of Advanced Data Security. As the name suggests, it offers several services for discovering and classifying sensitive data to protect sensitive data by using sensitivity labels and Information Type in the database. We will demonstrate this definition shortly. This is implemented at the column level and also known as SQL Information Protection.

With General Data Protection Regulation (GDPR) imposed to protect the private information of individuals, there is a high need for the business to be GDPR compliant, otherwise, any breaches in these guidelines result in harsh fines. This feature helps to meet GDPR regulations by identifying and classifying the columns that potentially contain sensitive data in the database.

Click on the Data Discovery & Classification (preview) pane to open it and let’s move ahead to see this in action on the Azure portal. By default, nothing is identified and classified on this page. We will click on the notification in the blue bar that specifies classification recommendations as shown below. These classifications are scanned by the classification engine in the database to list all the columns that contain potential confidential data.

Data Discovery & Classification in Advanced Data Security in Azure SQL.

You can see the classification report generated with a lot of details like schema name, table names, column names, information type and Sensitivity label for each column that this tool considers as sensitive data. Recommendations on Information Type like name, financial, contact info, credit card, etc. and Sensitivity labels like Confidential, Confidential – GDPR are listed in the dropdown as shown in the screenshot below.

Recommended classifications by Data Discovery and Classification feature in Azure SQL Database.

You can select one or more recommended columns and click the ‘Accept selected recommendations’ button to apply these columns as classified.

Accepting recommendations in Data Discovery and Classification in Advanced Data Security.

We can make changes to the default recommendations of Information Type and Sensitivity label. I have made a couple of changes to the below three columns and also edited type and label accordingly. Additionally, this tool gives the liberty to make classification per the business needs and we are not obliged to go just by recommendations. Hit the Save button to save these classification changes.

Editing information type and labels and Saving these classifications in Data Discovery and Classification.

Once these changes are saved, we can see the updated report in the Overview pane of Data Discovery & Classification feature. This gives the summary of the no. of classified columns and also the no. of tables that contain sensitive data.

Updated overview of Data Discovery & Classification feature.

Adding custom classifications manually

Apart from the recommendation made by this tool, you can also manually add classifications by using the ‘Add classification’ button on the top menu bar. Select schema, table, column, information type and label in the context window and hit Add classification at the bottom as shown below.

Adding classification manually.

You can see the custom classification being added in the bottom row, and click save button to save this newly classified column.

Saving manual classification in SQL Azure Database.

Adding custom classifications using T-SQL

With this, we come to the last leg of this article, this feature provides one more option to manage column data classification using T-SQL. We can add and remove these classifications and also check all the classifications available in the entire Azure SQL database using T-SQL queries.

Add sensitivity classification

We can add the sensitivity classification to one or more columns in a database with information type and label. Identifying and discovering such sensitive columns and classifying them helps to achieve better data protection. Let’s say we want to categorize columns YearlyIncome and BaseRate in dimcustomer table as Highly Confidential and EmailAddress column in dimemployee table as Confidential. Execute the below code in the new query window to add below classifications.

Once this is executed, these columns are added and listed in the Overview pane in Data Discovery & Classification.

Columns classified using T-SQL in Data Discovery & Classification.

Also, one more feature to acknowledge in the above screenshot is the Export button. This tool supports downloading a report in an excel format using the Export button on the top menu bar.

Drop sensitivity classification

‘DROP Sensitivity Classification’ helps to delete classified data from one or more columns in the Azure SQL database, one such example to drop column classification is shown below.

Dropping classified columns in Data Discovery & Classification.

Retrieve classifications in the database

You can retrieve all the classifications made to the database using view, sys.sensitivity_classifications. This view lists all the classified columns with their corresponding details like class, id, label, type etc.

Retrieve classified columns using T-SQL.

Conclusion

We explored one of the new components of Advanced Data Security available with Azure SQL Database in this article. This feature is a potential way to exhibit private data security. Since this feature – Data Discovery & classification is in preview mode, we can expect a lot more new advancements in the near future from the SQL Azure team.

Stay tuned

Keep a tab on the next block to continue your learning on two other important aspects of data security in Azure SQL – Vulnerability Assessment and Advanced Threat Protection.

Table of contents

Advanced Data Security in Azure SQL Database – Data Discovery & Classification
Vulnerability Assessment and Advanced Threat Protection in Azure SQL Database

Gauri Mahajan

Gauri Mahajan

Gauri is a SQL Server Professional and has 6+ years experience of working with global multinational consulting and technology organizations. She is very passionate about working on SQL Server topics like Azure SQL Database, SQL Server Reporting Services, R, Python, Power BI, Database engine, etc. She has years of experience in technical documentation and is fond of technology authoring.

She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.

View all posts by Gauri Mahajan
Gauri Mahajan
Security, SQL Azure

About Gauri Mahajan

Gauri is a SQL Server Professional and has 6+ years experience of working with global multinational consulting and technology organizations. She is very passionate about working on SQL Server topics like Azure SQL Database, SQL Server Reporting Services, R, Python, Power BI, Database engine, etc. She has years of experience in technical documentation and is fond of technology authoring.She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.View all posts by Gauri Mahajan

700 Views