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
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.