AZURE SQL - DATA DISCOVERY & CLASSIFICATION

Azure SQL Overview

Azure SQL Database is a fully managed Platform as a Service (PaaS) Database Engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. As with any database platform, security remains a top concern to address this issue or concerns Microsoft have offering called Advanced Data Security with Azure SQL service.

Pre-requisites

• Enable Advanced Data Security (ADS) at the database level by navigating to Settings > Advanced Data Security for your SQL database and click Enable.

• Alternatively, ADS can also be configured and managed at the server level by navigating to Settings > Advanced Data Security for your server and switching the ‘Advanced Data Security’ setting from ‘Off’ to ‘On’.
• AdventureWorksLT database will be used.
• Enable the Auditing settings to have complete investigation experience. Go to the Auditing blade and toggle Auditing switch from OFF to ON. Choose Audit log destination to BlobStorage, LogAnalytics, Eventhub any combinations based on your preference.

Data Discovery and Classification

It is important for your organisation to know which data is stored and what kind of data it is. It can be Financial data, Personal data, Health, Sensitive (Passwords) or any other type. With GDPR imposed there is a high need for the business to be GDPR compliant, its especially important to mark personal data and have a documentation ready, which data is stored and why. This can be a challenging and annoying task if you have to do this for an existing database with a few hundred tables. Fortunately, there ways to make life easier by enabling ADS, SQL Server will try to automatically detect Columns and Tables that need to be classified, also it provides capability for labeling and classifying based on your organisation standards. 

Azure SQL Server Recommendation

Applying Recommendation 

As shown above you have option to add your own classification which is not suggested by Azure by using Add Classification option which is highlighted above. Azure ADS is smart enough once you have classified a column under a particular category that column is greyed out and not available for subsequent classification which means avoids duplication.

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. Also, option for download as a report which can be shared with your project team members.

Adding custom classifications manually

Apart from the recommendation made by this tool,you can add your own custom classification using below option

Similarly you have option to remove the classifications.

Note: Classifications can be added to database using multiple options like Azure portal as shown above, Using Rest API , Using PowerShell Cmdlet , Using T-SQL

Auditing

As part of Auditing enabled at database level below LogAnalytics query helps us to trace who requested when data from database as part of which we have 'data_sensitivity_information_s' which displays as part of the query was their any sensitive data was queried.

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| where ResourceId == '/SUBSCRIPTIONS/XXXX(Subscription Id)/RESOURCEGROUPS/BLOG/PROVIDERS/MICROSOFT.SQL/SERVERS/BLOGSERVER/DATABASES/BLOGDATABASE'
| project event_time_t, statement_s, succeeded_s, affected_rows_d, server_principal_name_s, client_ip_s, application_name_s, additional_information_s, data_sensitivity_information_s
| order by event_time_t desc
| take 100

References

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-data-discovery-and-classification

https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver15&tabs=t-sql