row level & view level security not working through live connection to SQL server

[nkkinariwala]

I am having a report which fetches the data from a view from SQL server through a live connection. 

This view has underlying security based on Microsoft Sales security.

View definition looks like this

select <blah>
from <blah>
where UserAlias = (SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME(), 1) + 1, LEN(SUSER_SNAME())))

If users query the view in SQL Server, they are looking at the data what they are supposed to see.

But if they look at the report (which I published on powerbi), they look at the data what I am supposed to see, so basically it shows the same data (based on my access) to everyone. 

Funny thing is - I have created a measure using DAX formula USERNAME(), there it shows respective user's credential but while connecting to SQL server, somehow, it always considers my credential from powerbi.com.

While connecting to sql server through live connection, I use "Use my current credential" and not "use alternate credential".

So not sure if this is Power BI issue. Can you please help me here?

Thanks,

Nisarg

[nkkinariwala]

I have created a report which fetches the data from View (from SQL server) through live connection.

This view gives the result based on individual user's access.

Following is the query 

select <blah>
from <blah>
where UserAlias = (SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME(), 1) + 1, LEN(SUSER_SNAME())))

If individual goes to SQL server and fetches the data from this view, user can see only those records what they are supposed to see.

But from report (which I have published on PowerBI), it always takes my credential and shows the same data to everyone. I also created a new measure using USERNAME() DAX function. And funny thing is - it shows current user who is looking at the report but while fetching the data from SQL server, it uses my credential.

Not sure if this is a bug, can you please help me ASAP?

We are dealing with creating a report which fetches the data based on MS Sales security. 

Thanks,

Nisarg

[v-jiascu-msft]

@nkkinariwala,

Hi Nisarg,

In this scenario, it's time to use RLS to control the data access. Usually, a few people will connect to the SQL Server to create reports, while many more people only read the report. So it isn't a good practice that everyone connects to the DB with an account. Power BI provides the function RLS (Row level security). People only see the data filtered by the RLS. Please reference powerbi-admin-rls for more details. One important tip: RLS roles can be only created in the Desktop and then be assigned to users in the Service.

If users create their own report, they will connect to SQL DB with their own account, perhaps there won't be a problem.

Best Regards!

Dale

[nkkinariwala]

Thank you. I think RLS is not helping me here. I am looking for dynamic security. I have a separate table for security. I would like to leverage on that and use it in view while retrieving records. The problem is - with live connection to SQL server, it always passes credential of someone who has published the report. From desktop it is working fine, from service, this is an issue. I logged the bug with PBI team, not sure when they are going to fix it

[GilbertQ]

Hi @nkkinariwala

As far as I am aware the functionality for Effective User Name mapping is only for Analysis Services 

https://powerbi.microsoft.com/en-us/blog/tech-tip-thursday-user-principal-name-upn-mapping-in-power-...