Classify your Azure SQL Database

Here I am talking about SQL Data Discovery & Classification feature that is built into Azure SQL Database. With this feature you have the ability to classify your database, which is what I will do today. There are 2 attributes to classification which are important components. These are labels and information types. Labels are used to define the sensitivity level of the data stored in the column and information types being the type of data stored in the column.

To start this process, you need to navigate to the security section of your SQL Database, it is actually within Advanced Threat Protection.

class

ddf

This needs to be enabled, it does cost but after the trial period expires.

enabled

As you can see below it will return a list of recommended columns to classify with the type and label.

theportal

While the classification was running I executed the famous sp_WhoIsActive (http://whoisactive.com/) command where you can clearly see the DcService running, well I guess that is the Data Classification Service.

dcservice

Whilst connected to the database via SSMS you can issue the below query to get a really good summary of classification, basically the outcome of the process above.


SELECT
    sys.all_objects.name AS TableName, sys.all_columns.name As ColumnName,
    Label, Information_Type
FROM
          sys.sensitivity_classifications
left join sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id
left join sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_id
                         and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id

infotype

You can use TSQL to add new labels and information types for your classification purposes. For example, via SSMS I execute the following:

 	ADD SENSITIVITY CLASSIFICATION TO
  [dbo].[ErrorLog].[UserName]
    WITH ( LABEL='Highly Secret', INFORMATION_TYPE='CIA' )

newlab

Checking this via the Azure portal confirms the change.

portalchanged

 

1 thought on “Classify your Azure SQL Database

  1. Pingback: Dew Drop – February 4, 2019 (#2891) | Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s