Dynamic Data Masking Using SQL Endpoint and One Lake Security

[lali1409]

Premise:

I have 2 workspaces - WS1 and WS2. WS1 is created by me and WS2 is created by service principal/another user but I am the workspace admin.
Issue:
I did a POC for DDM in fabric in WS1 (the workspace created by me). For POC, I had 2 users added to the workspace - WS1 as viewers. I added mask to a column and provided unmask priviledge to only one user. The POC worked fine - The user with UNMASK privilege was able to see the unmasked data and the user with NO UNMASK privilege could see on mask data.

When I replicated the POC in WS2 (Workspace created by created by service principal/another user) with the same 2 users added as Viewer to WS2. When they try to query the table, they are getting the error:
"The SELECT permission or external policy action 'Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Rows/Select' was denied on the object 'sample_table', database 'db', schema 'dbo'."

To fix the above error users are facing in WS2, I(workspace admin) tried granting the users the select permission to the users :
GRANT SELECT ON dbo.sample_table (sample_column) TO [your_user_or_group];
But I got an error while querying data using SQL analytis endpoint -
"Cannot grant, deny, or revoke SELECT permission because the warehouse is in user's identity mode for OneLake access. To use these statements, the warehouse or SQL endpoint must be in delegated identity mode."

I enabled the One Data Security Preview in WS2 and change the security mode from Users Identity to Delegated Identity. After this, the users are able to mask data but the challenge is now how to show the UNMASK data to the users as when I run "GRANT UNMASK on schema:dbo to [USER]", I get an error mentioning user is already added to the Lakehouse.

Narrowing down the questions:

1. WS1 and WS2 must behave the same - having saif I am the workspace admin for both (but created only WS1). What is causing the issue?
2. If I use the OneLake Security data preview feature, how to unmask the data for selected users?

[tayloramy · Accepted solution]

Hi @lali1409,  

I think your two workspaces are behaving differently because their SQL endpoints are in different OneLake access modes.

• WS1 was effectively using Delegated identity (SQL-layer permissions apply).
• WS2 is/was in User’s identity (OneLake Security governs table reads). In this mode, GRANT/REVOKE on tables is blocked, which is why you saw the “external policy action … Rows/Select” denial and the “warehouse is in user’s identity mode” message. See Microsoft’s explanation of the two modes and limitations. OneLake Security for SQL endpoints (Preview), OneLake access control model

• If you want Dynamic Data Masking (DDM) to control who sees unmasked values, you must run the SQL endpoint in Delegated identity and use T-SQL to grant UNMASK. DDM is a SQL feature, not a OneLake Security feature. DDM in Fabric warehousing, OneLake Security (DDM note)
• Working recipe for WS2 (recommended):
  1. Keep the SQL endpoint in Delegated identity.
  2. Ensure each user can connect (Workspace Viewer or share the item to give “Read/ReadData” as needed). Share & manage Warehouse permissions
  3. Run T-SQL in the SQL endpoint:

     -- (Optional) Create contained user from Entra ID
     CREATE USER [user1@contoso.com] FROM EXTERNAL PROVIDER;
     CREATE USER [user2@contoso.com] FROM EXTERNAL PROVIDER;
     
     -- Allow both to read the table(s)
     GRANT SELECT ON SCHEMA::dbo TO [user1@contoso.com], [user2@contoso.com];
     
     -- Only user1 may see real values (unmasked)
     GRANT UNMASK ON SCHEMA::dbo TO [user1@contoso.com];
     -- Don't grant UNMASK to user2 (they’ll keep seeing masked)

     UNMASK can be granted at database, schema, table, or column scope. DDM permissions & UNMASK

• If you insist on User’s identity (OneLake Security) mode, understand that DDM isn’t managed there. You cannot “grant unmask” via OneLake roles. Your options are:
  • Use Column-Level Security (CLS) to hide sensitive columns for most users and present a SQL view that returns a masked projection for them; grant SELECT on the view (GRANTs to views work in both modes). Mode comparison (tables vs views), CLS in OneLake Security
  • Or switch the endpoint to Delegated identity for that item and stick with native DDM.

I’d start by keeping WS2 in Delegated identity and managing DDM purely with SQL-this aligns with what worked in WS1 and keeps your UNMASK logic simple and auditable.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[lali1409]

Thanks, @tayloramy I will try and confirm here.

I am wondering on why WS1 (I created 3 weeks before) and WS2(admin created - 3 months befo are in 2 different modes to begin with.

Also, for WS2 when I enable the onelake data access preview feature to use delegated identity , when I ran "GRANT UNMASK ON SCHEMA::dbo TO [user1@domain.com];" , I got an error:

The login already has an account with the user name 'user1@domain.com'.

I added a new user - user3, as a viewer, to the WS2 workspace and ran the command:
GRANT UNMASK ON SCHEMA::dbo TO [user3@domain.com];

This command ran without any error.

So, I removed the user1 from the workspace but I still the user1@domain in the sys.database_principals view. Not sure, what is the issue.