Power BI Embedded connecting to Azure SQL Database with Authenticated user

[mrhodes]

Our requirement is to create a workspace with the API and that the Datasets pointing to the Azure SQL Databases with the credential of the current authenticated user in Azure Active Directory.

This would allow the connection to the Azure SQL Database using the current user for purposes of row level security. Otherwise using a service account would not provide the Row Level Security and it would expose data to the user that they would not be authorized to see. We will have both Single Tenant and Multi-Tenant databases. In both cases it would require row level security, so we need to be able to have the data source recognize the current logged in user.

Using the Power BI Embedded API's can the Database Connection String be configued for the Azure SQL Database to use the context of the current authenticated user in Azure Active Directory. If so are there codes samples for this.

[pritesho]

Direct query mode does not support row level security authentication.

I have not tried configuring access through AD account yet, but it is probably a way to create multiple role based SQL accounts and map for the access.

[mrhodes]

We intend to do row level securiity via sql views which will test the current user. The issue is that in the dataset credentials in the power bi file we do not see an option to indicate currrent user if logged in via azure active directory. the azure sql dataabase is confiigured for azure active directory authentication. so when the user runs a report in powerbi we want it to connect to he azure database with the context of the current user and then the sql views will detect the current user and return the appropriate rows. the only other option would be for us to create a sql login for every user and then configure the user workspace with that credential. this could mean having to create thousands of additional logins plus maiintenance of those logins which would be not desirable as all this information currently exist in Azure Active Directory.