Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sekic
Helper I
Helper I

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 8
v-qiuyu-msft
Community Support
Community Support

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 Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, 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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ok, direct query use live connection to sql database, and each action (click, filter, etc...) on dasboard is visible in SQL profiler as query on database, but I can't use Trusted_Connection=true in connection string or anything similar?

 

I have zilions of views on MS SQL, each of them filter records using current database user information (Microsoft Dynamics CRM filtered views), but you say that I must make copies of all of them using crossjoin with sysusers table or similar, and then separate records with RLS rule on Power BI side? 

And that I must rewrite all CRM security code in filtered views to surpass Power BI RLS?

 

And all three products (Dynamics CRM and Power BI and SQL server) are Microsoft's but you can't solve using windows authentification in Power BI?

 

 

Anonymous
Not applicable

Any news with updated version on this old subejct ?

None. Main answer is that Power bi only can "read" data, and can't send any info to DB, even in live query. 

Which is, in case of Microsoft CRM, useless, because CRM have so many "Filtered" views, and development is, if some still not give up from Power BI, unnecessary slow, because I must rewrite all to the last view to 'duplicate' authorisation system form one Microsoft product to another Microsoft product  Man Frustrated Man Mad Man Frustrated

 

Anonymous
Not applicable

Hi Sekic,

 

When you get data from CRM, did you try using API and not SQL DB ?

But in API mode, you will be in import and not Direct Query. Then, in import mode, it will be the same problem, in deploy mode, there is only one unique acount for refresh :(:(

 

Read live data from sql CRM database is request, so API is not applicable. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors