Azure SQL Database Dynamic Data Masking won't save for one column

Question

I am applying Dynamic Data Masking to my Azure SQL Database. I have applied masking to all the fields I need to, except one. The table is AspNetUsers and the column that won't save is NormalizedUserName. The username in our case is the user's email address. The UserName, Email, and NormalizedEmail fields all saved with the correct mask - Email ([email protected]). All four fields are the same datatype - (nvarchar, null) The error I get when I try to apply the same mask to the NormalizedUserName field is

Failed to save Dynamic Data Masking settings for database: xxxxxxxxxx-auth.

ErrorMessage: Failed to update data masking rule dbo_AspNetUsers_NormalizedUserName - properties -

{
    "id":"dbo_AspNetUsers_NormalizedUserName",
    "resourceId":"/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/XXXXXXXXX-Development/providers/Microsoft.Sql/servers/xxxxxxxxxx-sql/databases/xxxxxxxxxx-auth",
    "schemaName":"dbo",
    "tableName":"AspNetUsers",
    "columnName":"NormalizedUserName",
    "maskingFunction":"Email",
    "aliasName":"",
    "numberFrom":"0",
    "numberTo":"0",
    "prefixSize":"0",
    "suffixSize":"0",
    "replacementString":""
}

Any ideas on why this error is happening, or where I can look for more details?

JeffCren JeffCren · Accepted Answer · 2019-09-27T15:10:32

It turns out that the issue is that there is a unique index on the NormalizedUserName field. There is a non-unique index on the Email field, which doesn't cause an issue. The solution is to drop the index, save the data mask rule, then re-create the index.

Azure SQL Database Dynamic Data Masking won't save for one column

1 Answers