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
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;
5 Comments
- 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
- 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.
- Status changed:NewtoPlanned
- Status changed:PlannedtoCompleted
