Data masking in Synapse serverless SQL pool

As per a Microsoft document, it is clearly stated that Dynamic data masking is only available for Dedicated SQL Pool, not for Serverless SQL Pool. As serverless SQL pool does not support Tables, Materialized views, DDL statements, DML statements, it might the reason.

Also, as Nandan suggested, it's not supported on external tables either.

You can raise a feature request here.

Just because something is not implemented, does not mean you can not implement it yourself.

First, I thought it would be great to create a function. But the dedicated and server less pools only support in line table value functions.

Second, we can also create a view with masked data. Then revoke the user from having rights to see that base table. Lets implement that for the customer id key. The code below shows the view.

--
--  Create view with masked customer number
--

CREATE VIEW saleslt.vw_dim_masked_customer
AS
SELECT 
  '***' + 
  SUBSTRING(CAST([CustomerKey] AS VARCHAR(5)), len([CustomerKey]) - 2,  2) AS MASKED,
  [CustomerKey],
  [FirstName],
  [MiddleName],
  [LastName]
 FROM [saleslt].[dim_customer]
 GO
 
 -- Test view
 SELECT * FROM saleslt.vw_dim_masked_customer
 GO

I have a database called mssqltips that contains the adventure works data as parquet data files exposed by external tables.

The output from the view shows that our data is masked. I did not get rid of the original column, Customer Key, since I wanted to do a comparison. Also, I would add some error handling for strings that are less than 2 characters long or null.

In short, dynamic data masking as a feature might not be supported. But you can easily mask data using custom logic and views. Just remember to revoke the user access to the base table.

Dynamic data masking is supported in actual physical tables and not supported on external tables. Hence DDM is not supported in serverless pools