Reitse's avatar
Reitse
Icon for Most Valuable Professional rankMost Valuable Professional
1 year ago
Status:
Completed

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', '[email protected]', '1900-01-01', 99),
('Try there is not', 'Yoda', '[email protected]', '1945-01-01', 99),
('UWAAHguuuhff', 'ChewBakka', '[email protected]', '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;
Databases | SQL Database

5 Comments

  • sukkaur's avatar
    sukkaur
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago
    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's avatar
    Reitse
    Icon for Most Valuable Professional rankMost Valuable Professional
    1 year ago
    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's avatar
    sukkaur
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago
    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

Recent ideas