Has anyone had any luck implementing RLS in Azure SQL DB and having it work in Power BI reports? I was able to implement and get the desired result set in Azure SQL DB. However, I am not able to get any rows using the same account in PowerBI service\premium using directquery on a basic report using only one table. I cannot see anything in PBI Desktop either, but that is expected since it uses a domain account and Azure SQL DB uses the O365 UID. When I set the security policy below to off then I get all of the rows for the table in Power BI without publishing again. Below is brief description of what I have done.
Setup a Mapper table
- Create Table[dbo].[Mapper]([User] sysname NULL, CurrentOfficerCode [varchar](255) NULL)
- Insert into Mapper([User], CurrentOfficerCode) VALUES('Actual office 365 UID', 'Actual officer code')
- Office 365 UID example: account@domain.net
- Actual officer code: would be same as account above
Create Schema
CREATE SCHEMA Security;
Create Function
CREATE FUNCTION Security.fn_ChkSavPredicate(@CurrentOfficerCode as varchar(255))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccesResult FROM dbo.Mapper
WHERE ([CurrentOfficerCode] = @CurrentOfficerCode AND [User] = USER_NAME());
Create Policy
CREATE SECURITY POLICY ChkSavMap
ADD FILTER PREDICATE Security.fn_ChkSavPredicate([CurrentOfficerCode])
ON [dbo].[ChkSav]
WITH (STATE = ON);
Validate
Execute AS USER = 'account@domain.net'
Select * from ChkSav
- When I run this statement, I get the desired result set in Azure SQL DB.
