Forum Discussion

sekic's avatar
sekic
Helper I
9 years ago

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....
Need Help
sekic's avatar
sekic
Helper I
9 years ago

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?

 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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

  • sekic's avatar
    sekic
    Helper I
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Any news with updated version on this old subejct ?

      • sekic's avatar
        sekic
        Helper I
        8 years ago

        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  :manfrustrated: :manmad: :manfrustrated: