SQL Analytics Endpoint - RLS for Security Groups

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 😀