Tagging all the Columns to Data classification in the Azure SQL DB

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

  

One of our customers came up with a requirement where they wanted to tag all the columns in Azure SQL DB for Azure data Classification .

  

Currently, in the data classification module through Azure portal, you can use pattern to tag certain keywords with the specific classification (credit card, personal information etc) and you can Accept selected recommendations as well as you can add your own pattern.

 

For example: We can see below the default recommendation which is adding sensitive data to the confidential label.

 

KeshavKiran_0-1626673337429.jpeg

 

 

However if you want to add all the columns which exist in the database and doesn't qualify in any of the pattern, you need to do it manually by adding the column in the portal.

 

KeshavKiran_1-1626673337431.jpeg

 

 

 

Doing the activity manually for hundreds of columns can be cumbersome task. To overcome this, you can use below T-SQL script where we can add all the remaining columns in the database to the specific sensitivity label.

 

For example: For the rest of the columns which is not in any of the pattern are the columns not having any sensitive data and we will put that in General sensitivity level.

 

 

SET NOCOUNT ON

GO

 

DECLARE dataclassification CURSOR FOR

SELECT SCHEMA_NAME(schema_id) AS schema_name, t.name AS table_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

where c.name not in ( SELECT

    C.NAME AS column_name

FROM sys.sensitivity_classifications sc

    JOIN sys.objects O

    ON  sc.major_id = O.object_id

       JOIN sys.columns C

    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id)

 

OPEN dataclassification

DECLARE @tableSchema NVARCHAR(128)

DECLARE @tableName NVARCHAR(128)

DECLARE @Columnname NVARCHAR(128)

DECLARE @Statement NVARCHAR(300)

 

FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName, @columnname

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @Statement = 'ADD SENSITIVITY CLASSIFICATION TO '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + '.' + '[' + @Columnname + ']'

+ 'WITH ( LABEL=''General'' , Information_type =''Other'')'      --Here you can specify the specific senstivity label which  you want  your column to get added.

 

   PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below.

   --EXEC sp_executesql @Statement -- remove the comment on the beginning of this line to run the commands

 

  FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName,@columnname

END

CLOSE dataclassification

DEALLOCATE dataclassification

GO

SET NOCOUNT OFF

GO

 

 

After executing the above query, we can see that it has added the rest of the columns in the database to general sensitivity level .

 

KeshavKiran_2-1626673337434.jpeg

 

 

 

We can verify the same on Azure Portal as we can now see all the columns added in the General sensitivity level.

 

KeshavKiran_3-1626673337432.jpeg

 

 

Thanks to @RoshnaNazir for helping me in authoring this blog.

 

 

References : -

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.