Azure-docs: Set-AzSqlDatabaseSensitivityClassification not working

[CharmanderJieniJieni]

[Enter feedback here]

I am referring to this page

First I am retrieving all recommandations

$databases = Get-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName | where  {$_.DatabaseName -ne "master"}

It is returning a list of recommendation like this

SensitivityLabels : {{
                        SchemaName: dbo,
                        TableName: Personal Information,
                        ColumnName: First Name,
                        SensitivityLabel: Confidential - GDPR,
                        InformationType: Name,
                        Rank: Medium
                    }, {
                        SchemaName: dbo,
                        TableName: Personal Information,
                        ColumnName: Last Name,
                        SensitivityLabel: Confidential - GDPR,
                        InformationType: Name,
                        Rank: Medium
                    }, {
                        SchemaName: dbo,
                        TableName: Personal Information,
                        ColumnName: Social Insurance Number,
                        SensitivityLabel: Confidential,
                        InformationType: Financial,
                        Rank: Medium
                    }}

Then I looped through the list of labels and accept the recommendation using Set-AzSqlDatabaseSensitivityClassification

foreach ($database in $databases){
    $databaseName = $database.DatabaseName
    $recommandation = Get-AzSqlDatabaseSensitivityRecommendation -resourcegroupname $rgname -servername $serverName -databasename $databaseName
    foreach ($label in $recommandation.SensitivityLabels){
        # enable recommandation
        Write-Host "Set database classification recommandation for $databaseName"
        Set-AzSqlDatabaseSensitivityClassification `
            -ResourceGroupName $rgName `
            -ServerName $serverName `
            -Databasename $databaseName `
            -SchemaName $label.SchemaName `
            -TableName $label.TableName `
            -ColumnName $label.ColumnName `
            -InformationType $label.InformationType `
            -SensitivityLabel $label.SensitivityLabel
    }
}

The command executes successfully, however, when going to portal, I am still seeing the recommendation and nothing got changed

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 9ca439af-510f-303c-e72c-424b78ac0ac7
• Version Independent ID: 616d8cf5-1631-1179-0c59-f8b4f6c1f7bb
• Content: Data Discovery & Classification - Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse
• Content Source: articles/azure-sql/database/data-discovery-and-classification-overview.md
• Service: sql-database
• Sub-service: security
• GitHub Login: @DavidTrigano
• Microsoft Alias: datrigan

[NavtejSaini-MSFT]

@CharmanderJieniJieni We are checking this and will get back to you.

[DavidTrigano]

@Basel [e-mail] to assist

D.

---

From: NavtejSaini-MSFT [e-mail]
Sent: Friday, June 5, 2020 10:11:19 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: David Trigano [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] Set-AzSqlDatabaseSensitivityClassification not working (#56488)

@CharmanderJieniJienihttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FCharmanderJieniJieni&data=02%7C01%7CDavid.Trigano%40microsoft.com%7Cd6e25fa4e7e748b8e45b08d809843aa6%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637269810814885665&sdata=7gWA1PqY7Eq%2Fo%2BUDCMm%2FZZusvHhPTbNvtaXp%2B2bw%2BIo%3D&reserved=0 We are checking this and will get back to you.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F56488%23issuecomment-639725278&data=02%7C01%7CDavid.Trigano%40microsoft.com%7Cd6e25fa4e7e748b8e45b08d809843aa6%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637269810814885665&sdata=yhuOr%2Bx7mWpC0mtZ5uqDHfaV4dkus%2BVkMEaRx3ifpPs%3D&reserved=0, or unsubscribehttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANB4JAR3375LOUP3ZYHKV2TRVE7NPANCNFSM4NUNZIPA&data=02%7C01%7CDavid.Trigano%40microsoft.com%7Cd6e25fa4e7e748b8e45b08d809843aa6%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637269810814885665&sdata=8wiUkeFCU9eGO8VTTqNO49%2BvsqIrEd9PaOYhGqc4b%2FA%3D&reserved=0.

[bashahee]

Hello @CharmanderJieniJieni,

Thanks for your feedback!

Can you please verify that the latest version of Az.Sql (https://www.powershellgallery.com/packages/Az.Sql/2.7.0) is installed on your machine?

In case your are facing the issue though latest version of Az.Sql is installed, can you please share the schema of the database that its columns are still being recommended though they were classified?

FYI, you can get same impact of the above script using
Get-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName | where {$_.DatabaseName -ne "master"} | Get-AzSqlDatabaseSensitivityRecommendation | Set-AzSqlDatabaseSensitivityClassification

[CharmanderJieniJieni]

Not sure how to check Az.sql version, I used this line
to install Azure Powershell and Az.Sql is there.

Regarding the schema, below is the single table I use

/****** Object:  Table [dbo].[Personal Information]    Script Date:
6/7/2020 8:06:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Personal Information](
[First Name] [nchar](10) NULL,
[Last Name] [nchar](10) NULL,
[Social Insurance Number] [nchar](10) NULL
) ON [PRIMARY]
GO

On Sun, Jun 7, 2020 at 3:27 AM bashahee [e-mail] wrote:

Hello @CharmanderJieniJieni https://github.com/CharmanderJieniJieni,

Thanks for your feedback!

Can you please verify that the latest version of Az.Sql (
https://www.powershellgallery.com/packages/Az.Sql/2.7.0) is installed on
your machine?

In case your are facing the issue though latest version of Az.Sql is
installed, can you please share the schema of the database that its columns
are still being recommended though they were classified?

FYI, you can get same impact of the above script using
Get-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName |
where {$_.DatabaseName -ne "master"} |
Get-AzSqlDatabaseSensitivityRecommendation |
Set-AzSqlDatabaseSensitivityClassification

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MicrosoftDocs/azure-docs/issues/56488#issuecomment-640170465,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AFPUB5FU2YMEXYRWBUKQB4DRVM6P5ANCNFSM4NUNZIPA
.

[CharmanderJieniJieni]

Tested the second approach Get-AzSqlDatabase -ResourceGroupName $rgname -ServerName $serverName | where {$_.DatabaseName -ne "master"} | Get-AzSqlDatabaseSensitivityRecommendation | Set-AzSqlDatabaseSensitivityClassification as well, periodically getting 401 error
Set-AzSqlDatabaseSensitivityClassification: Response status code does not indicate success: 401 (Unauthorized).

But one out of 5 trials went through

[bashahee]

Hi @CharmanderJieniJieni,

The 401 error is being raised because of a bug, it was fixed, and next release (2020-06-23) of Az.Sql will contain the fix.

You can determine the version that is running on your machine using Get-Module Az.Sql.

[CharmanderJieniJieni]

I see. Thanks!

On Mon, Jun 8, 2020 at 5:49 PM bashahee [e-mail] wrote:

Hi @CharmanderJieniJieni https://github.com/CharmanderJieniJieni,

The 401 error is being raised because of a bug, it was fixed, and next
release (2020-06-23) of Az.Sql will contain the fix.

You can determine the version that is running on your machine using Get-Module
Az.Sql.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MicrosoftDocs/azure-docs/issues/56488#issuecomment-640905907,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AFPUB5GAPHMAGORCCHI24DTRVVMHVANCNFSM4NUNZIPA
.

[NavtejSaini-MSFT]

@CharmanderJieniJieni We will go ahead and close this thread by EOD. Please get back to us if you have any further question.