Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Fabric SQL Database dynamic data masking doesn't work

I've been doing some testing with Dynamic Data Masking to try and find out if/how it works.

 

To do that, I'm running a script that works perfectly fine in SQL Server and Azure SQL Server.

But it fails to mask the data in Fabric SQL Database. I got some confirmation yesterday at SQL Konferenz that this really looks like a bug and therefore I'm sharing the full code with you to reproduce and hopefully find a solution.

 

DROP TABLE IF EXISTS dbo.MaskingTable;

 

CREATE TABLE dbo.MaskingTable (
ID int IDENTITY(1,1),
FirstName varchar(20) MASKED with (FUNCTION = 'Partial(3,"AbC",1)') NOT NULL,
LastName varchar(30) MASKED with (FUNCTION = 'default()') NOT NULL,
Email varchar(40) MASKED WITH (FUNCTION = 'email()') NOT NULL,
SecretDate datetime MASKED WITH (FUNCTION = 'datetime("Y")')NOT NULL,
TopSecretNumber int MASKED with (FUNCTION = 'RANDOM(1,1000)')
)

 

INSERT INTO dbo.MaskingTable (FirstName, LastName, Email, SecretDate, TopSecretNumber)
VALUES
('Hello There', 'Obi Wan', 'Kenobi@Rebellion.Empire', '1900-01-01', 99),
('Try there is not', 'Yoda', 'MasterYoda@Rebellion.Empire', '1945-01-01', 99),
('UWAAHguuuhff', 'ChewBakka', 'Chewy@Rebellion.Empire', '2025-07-29', 99)

 

SELECT *
FROM dbo.MaskingTable

 

CREATE USER NoUnMask WITHOUT LOGIN;
GRANT SELECT ON dbo.MaskingTable TO NoUnMask;

 

EXECUTE AS USER = 'NoUnMask'
SELECT *
FROM dbo.MaskingTable
REVERT;
Status: Planned
Comments
sukkaur
Microsoft Employee
Hi Reitse, Just want to let you know that masking works fine. Please try it with an actual user by giving your user connect access to the SQL database artifact in fabric. You are not seeing the result because you using execute as user for this. Thanks Sukhwant
Reitse
Most Valuable Professional
Hi Sukhwant, So to test this functionality I can't use a method that's been in use for ages? If execute as doesn't work in Fabric SQL (and it does in Azure SQL and SQL Server), it should return an error instead of an unwanted result. I've seen the execute as clause being used in stored procedures as well in the past. If people assume this will work in Fabric SQL too, they might be in for a surprise after migrating. I hope this one can be fixed or that there will be clear documentation on the fact that this doesn't work.
sukkaur
Microsoft Employee
@Reitse, I appreciate you bringing this forward. We are working on getting the right message when the customer uses it as well as provide clarity thru our documentation. Thanks Sukhwant
JakubSzymaszek
Microsoft Employee
Status changed to: Planned