SQL Analytics Endpoint - RLS for Security Groups

[fabricator1]

Hi,

I am trying to apply Row-Level Security on a Lakehouse SQL Endpoint. I have successfully applied RLS for individual users. However, I want to apply RLS for Entra ID Security Groups, as this is a more scalable solution and less maintenance.

I have read this documentation article: Row-level security in Fabric data warehousing - Microsoft Fabric | Microsoft Learn however I am not able to make it work for groups.

Here is my table (the data is just dummy data):

Here are my steps:

1. I added the Entra ID Security Group as a member of the workspace (workspace role is Member). 
I am a member of this Entra ID Security Group.

2. I executed the following SQL statements:

-- Creating role for salesDepartment 2
CREATE ROLE salesDepartment2 AUTHORIZATION [dbo];
GO

ALTER ROLE salesDepartment2 ADD MEMBER [NameOfSecurityGroup];
GO


-- Creating schema for Security
CREATE SCHEMA Security;
GO


-- Creating a function for the NameOfRole evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@NameOfRole AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE IS_ROLEMEMBER(@NameOfRole) = 1;
GO


-- Using the function to create a Security Policy
CREATE SECURITY POLICY NameOfRoleFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(NameOfRole)
ON dbo.Fact_Order
WITH (STATE = ON);
GO

When I try to view the Fact_Order table in the SQL Analytics Endpoint, I get this error message:

If I try to recreate the security policy like below, then I can see all of the data in Fact_Order table (because my Entra ID Security Group is a member of the salesDepartment2 role):

-- Drop the Security Policy in order to recreate it a bit differently
DROP SECURITY POLICY NameOfRoleFilter

-- Using the function to create a Security Policy
CREATE SECURITY POLICY NameOfRoleFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate('salesDepartment2')
ON dbo.Fact_Order
WITH (STATE = ON);
GO

If I try to recreate the security policy like below, then I can open the Fact_Order table but I cannot see any data (because my Entra ID Security Group is not member of such role salesDepartment1):

-- Drop the Security Policy in order to recreate it a bit differently
DROP SECURITY POLICY NameOfRoleFilter

-- Using the function to create a Security Policy
CREATE SECURITY POLICY NameOfRoleFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate('salesDepartment1')
ON dbo.Fact_Order
WITH (STATE = ON);
GO

I think the IS_ROLEMEMBER() function is not able to read the content of a table column in the SQL Analytics Endpoint. In the Fact_Order table, I have a column NameOfRole which says which role should have access to read each row.

I want to use the IS_ROLEMEMBER() to do a lookup on this column, so the rows will be visible only to members of the role in the lookup column.

However, it seems the IS_ROLEMEMBER() in SQL Analytics Endpoint is not able to do a lookup on the column. I think that is why I got the error response.

For example, see when I execute these queries to this small table:

(Below is hard coded string inside IS_ROLEMEMBER() for a role which I am member of, via the Entra ID security group. The function returns 1)

(Below is hard coded string inside IS_ROLEMEMBER() for a role which I am not a member of. The function returns 0)

(Below is trying to use IS_ROLEMEMBER() to check if I am member of the roles which are listed in the table column NameOfRole. It returns an error.)

I get the error "The query references an object that is not supported in distributed processing mode." when trying to apply the IS_ROLEMEMBER() to a column in the table which contains different role names.

How can I set up RLS for groups in SQL Analytics Endpoint? 

Thank you 😀

[danjo914]

I had to use the Microsoft Graph API to populate a table with Security Groups and Members for applying RLS. Power Query couldn't save a dynamic source to a table, so I used Power Apps to create a table and then Power Query to pull that table into the Warehouse from Power Apps. It was a bit messy but gets the job done and runs quick. Unfortunately I've not seen anyway to implement Entra Security Groups directly.

[xavier2110]

Hi,

I'm facing the same...

Did you find any solution or workaround ?

Best

[fabricator1]

Hi @xavier2110 

I didn't spend more time looking into it, but maybe I need to revisit this topic later.

Hope someone can provide a step-by-step procedure of how to do this.

[fabricator1]

Thank you @v-gchenna-msft, 

However, does it mean that RLS on SQL Analytics Endpoint / Data Warehouse is not feasible for Entra security groups at this moment?

[v-gchenna-msft]

Hi @fabricator1 ,

No, RLS on SQL Analytics Endpoint / Data Warehouse is feasible for Entra security groups.

I haven't tried it, but you can refer this - Microsoft Fabric Row-Level Security (RLS) & Column-Level security(CLS) in Warehouse and SQL Endpoint...

Hope this is helpful. Please let me know incase of further queries.

[fabricator1]

Thank you @v-gchenna-msft 

However, when reading the article, it seems that the article is explaining how to do RLS for individual users. For Entra ID group (AAD group in the article), the article only explains how to give access to all rows in a table. 

"The sales user will be able to view only his sales while the product admin AAD group user will have all access to view all the sales records."

However, I am interested in RLS (only access to specific rows) for Entra ID group.

Thank you! 

[v-gchenna-msft]

Hi @fabricator1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .

[frithjof_v]

CREATE ROLE seems to be unsupported at this time:

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations

[v-gchenna-msft]

Hi @fabricator1 ,

We haven’t heard from you on the last response and was just checking back to see if got some insights over your query.
Otherwise, will respond back with the more details and we will try to help .

As @frithjof_v  said currently CREATE ROLE is not supported.

T-SQL surface area - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.