cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeBaker New Member
New Member

PBI Issue using RLS implemented in Azure SQL DB

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.

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!