Dynamic Data Masks stopped working

Question

I had a test web page that used a restricted SQL user to query an Azure SQL DB to get data from masked fields. It returned the data and it was masked. I rejoiced. I changed the user with a button and voila! I could see the actual data.

Then today, for some reason, it stopped working and I cried.

I changed no code in between. I did however use the Azure portal to turn on auditing (can't image that affecting it). I thought TDE may conflict with DDM but I've read elsewhere that it doesn't - plus it was already enabled when it was working. I ran a query to show what permissions my "nobody" SQL user had - "connect" only.

So I deleted my "nobody" user and created a new SQL login with associated user (as opposed to just a user). This way I could use SSMS to log in as a restricted user that should be affected by masking - but nope.

So I can setup masks in TSQL or via Azure and Azure shows what's fields have been affected. But I every damned time I run a query in SSMS or via .net I see unmasked data.

I really am stumped so any help would be awesome.

Are there certain circumstances masking would stop working? Is there anything else I can check to ensure it's setup correctly?

ta muchly

Answer 1

SQL users with administrative privileges are always excluded from dynamic data masking. DB_owners can also see the data unmasked. Data is only masked for the data_reader. So if you’re seeing the unmasked data make sure, to use data_reader permissions to the users from whom you want to mask the data.

Answer 2

After working with Microsoft to figure out what the issue is the conclusion is that dynamic data masking doesn't play nicely with the Azure Data Classification feature.

When Data Classification is used DDM is turned off resulting in someone with only select permissions able to see the masked data.

According to the MS representative the fix will be in 4-5 weeks but no ETA as to when it will be rolled out.

Answer 3

Could you please provide your subscription, server name and database name? You can send it directly to me - ronitr microsoft com We would like to investigate this - you may have hit a case of a bug we are tracking down. Thanks