Forum Discussion
Power bi direct query with windows authentication, how to setup
I succesfully setup sql 2016 direct query in power bi desktop, it's run fine.
But when published, it still use my creditenials to connect, not from another user which is connected to published file.
How to "get" current windows user to connect to sql?
Because pbix use is based on bunch of views, which all have build-in control to connected user ID, so each user must "use" own user id from windows.
8 Replies
Hi sekic,
From your description, it seems that you want to let each user access the report can only see his own data, right?
In your scenario, you can utilize the RLS feature. Assume there is a column contains user's User Principal Name (UPN), you can create a RLS role in Power BI desktop user's [email] = username( ). After publish the report to service, add members under this role. For more information, see: Row-level security (RLS) with Power BI.
By the way, as the .pbix file get data from the SQL server database in DirectQuery mode, after publish to the Power BI service, it requires the data gateway to build connection. See: Manage your data source - SQL Server.
Best Regards,
Qiuyun YuThanks, this is fine and usable.
But can I do one more step forward; to get win user/pass thru DAX or env.variables, and connect to sql with this?
Hi sekic,
Sure. We can get data from SQL Server data source, and define RLS role use USERNAME() function mentioned above to limit each user access the report can only see his own data.
Best Regards,
Qiuyun Yu
