Problems with Auth2, Azure AD and SQL Row level security not working

[erwiegman]

Hi Community

I have a problem with Azure AD authentication in PowerBI to pass through to an Azure SQL Database with row level security configured. When i connect to the SQL database everything works like i should and the user get the filtered data. When i create the report in Power BI desktop also everything works as expected.

But when i publish the report to the PowerBI service and set the 'Report viewers access this data source with their own Power BI identities in DirectQuery mode' option (and succesful do the authentication thing after the apply). I cannot see the data in my report. It gives the error 'Login failed for user '<token-identified principal>'.. The exception was raised by the IDbConnection interface.' 

I'm doing something wrong, but i cannot seem to find the problem (and google isnt helping this time ;). So is there someone out there already solved this last piece of the puzzle ? 

[V-lianl-msft]

Hi @erwiegman ,

It may be that the instance of the login account does not match the account with RLS.

Refer to this:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sso-kerberos 

The map user names feature is a great way to work around when your user name in Azure Active Directory (Azure AD) doesn't match a UPN in your local Active Directory instance.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-ssas#map-us... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[erwiegman]

@V-lianl-msft 

Hi,

We are not using a gateway to a on-premise datasource. We are connecting to a Azure SQL database, and in the PowerBI desktop everything works as expected. I also think that in the PowerBI service something else is going on with the authentication, but i cannot seem to find a way to see which username is passed to the Azure SQL database. Those links are not helping with this.

Also the RLS in PowerBI isnt helping, because this report should be a personal report with GDPR data in it (phonecall data). So we want 1 report and every use must have his own data. That is why we created a 'personal' RLS (which works in SQL with SSMS).

[GilbertQ]

Hi there

have you configured SSO via the Gateway connection?

https://docs.microsoft.com/en-us/power-bi/connect-data/service-azure-sql-database-with-direct-connec....

[erwiegman]

No i didnt i'm connected to a Azure SQL database so no need for a Gateway. I did the exact step as in the link you supplied. Also i did a SQL profile trace with Azure data Studio, and i dont see any queries coming by. So i think it is related to the logon by the PowerBI service, but i dont see a way to confirm if this is the case.