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.
- Data Discovery & Classification (Preview feature)
- Vulnerability Assessment
- 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.
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.
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.
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.
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.
You can select one or more recommended columns and click the ‘Accept selected recommendations’ button to apply these columns as classified.
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.
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.
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.
You can see the custom classification being added in the bottom row, and click save button to save this newly classified column.
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.
|
1 2 3 4 5 6 7 |
ADD SENSITIVITY CLASSIFICATION TO dbo.dimcustomer.YearlyIncome, dbo.dimemployee.BaseRate WITH (LABEL='Highly Confidential', INFORMATION_TYPE='Financial') ADD SENSITIVITY CLASSIFICATION TO dbo.dimemployee.EmailAddress WITH (LABEL='Confidential', INFORMATION_TYPE='Contact Info') |
Once this is executed, these columns are added and listed in the Overview pane 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.
|
1 2 |
DROP SENSITIVITY CLASSIFICATION FROM dbo.dimemployee.EmailAddress, dbo.dimcustomer.YearlyIncome |
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.
|
1 |
SELECT * FROM sys.sensitivity_classifications |
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 |
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
- Oracle Substring function overview with examples - June 19, 2024
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023