Synopsis

This PowerShell script has been written to get the discovered data classifications on an Azure SQL database and apply those classifications.

Applies to

Azure SQL Database

Description

From an automation perspective, when we need to apply the classifications for hundreds of databases or more the Azure PowerShell cmdlets come very handy, and this script makes use of these –

https://learn.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview?view=azuresql#powershell-cmdlets-for-azure-sql-database

This script does the following:

Authenticate to Azure Az using Multi-factor authentication (Use Managed Service Identity when running via automation runbook).
Loops through all subscriptions under a tenant, finds all resources of type- SQL Server and then loops through all databases (based on certain filters) and then first gets the recommended classifications and then applies them
Generates output list of applied classifications (a portion of it) per database in excel format.

Lets look at this with the help of an example database – PRODDB01 which dosent have any classifications applied.

Before Classification

We will now run the powershell script.

Code Snippet

 

Classifications Applied

Classifications Report is also generated in excel format.

Classification Report

Requirements

Az modules
ImportExcel module
Az.Sql Version 2.6.0
Code Download

https://github.com/thepracticaldba/Automation/blob/main/ApplyAzureDataClassifications.ps1

Notes

There is an issue with the [Set-AzSqlDatabaseSensitivityClassification] cmdlet and Microsoft hasn’t fixed it for quite a while now. You will get the below error if you run it Az.Sql versions higher than 2.6.0

Response status code does not indicate success: 404 (Not Found).

Refer: https://github.com/Azure/azure-powershell/issues/12304

This also makes it difficult to run this via Automation runbook and you might need to do some customizations. One way could be using a dedicated automation account just for this runbook (with Az.Sql Version 2.6.0 installed instead of the latest version).