Showing results for 
Search instead for 
Did you mean: 
Not applicable

Windows Authentication with Import & Direct Query



I’m looking for information on how credentials are handled from the service down to the data source. We are looking to implement a windows authentication model where by the users logs into the Power BI service and their credentials are passed down all the way to the data sources (SQL Server), so running traces on SQL server you would see user domain accounts from Power BI accessing views etc. We will mostly be using Direct Query but would like to get a good understanding of import mode to.


Seems to me you can connect with the following credentials


Windows Current Credentials (is this windows authentication?)

Windows Alternate Credentials (is this a static windows account?)

Database User Credentials (is this like the sa account on the database?)


For Import Mode

From within the power bi desktop I understand that when connecting using current credentials I must be connecting to the data source under windows authentications because if I’m not added to the database as a user and given permissions i cant connect. 


Question 1) once a report is deployed to the service what happens when the data-set is refreshed using the "Refresh now" feature when data sources credentials are set to Current Credentials?


Question 2) When a scheduled refresh has been set and the data sources credentials are set to Current Credentials who’s credentials are used to connect to the data source as this happens uninitiated from any user?


For Direct Query Mode

Again similar to import mode if connecting with Current Credentials from within desktop using direct query I must be using windows authentication.


Question 3) When I access anything in the service that initiates a query request and Current Credentials has been set within data-source dose the initiating users windows credential get passed down to the data source because from the below Microsoft online documentation it seems to state in the last paragraph this does not happen and the same fixed credentials will be used all the time.




As discussed earlier in this article, a report using DirectQuery will always use the same fixed credentials to connect to the underlying data source, after publish to the Power BI service. Again, note this refers specifically to DirectQuery, not to live connections to SQL Server Analysis Services, which is different in this respect. Hence immediately after publish of a DirectQuery report, it is necessary to configure the credentials of the user that will be used. Until this is done, opening the report on the Power BI service would result in an error.


Once the user credentials are provided, then those credentials will be used, irrespective of the user who opens the report. In this regard it is exactly like imported data, in that every user will see the same data, unless Row Level Security has been defined as part of the report. Hence the same attention must be paid to sharing the report, if there are any security rules defined in the underlying source.

Super User
Super User

Re: Windows Authentication with Import & Direct Query

@Anonymous You can't secure reports in this way, you need to look at row level security in the model. This method allows you to show different filter contexts to different audiences based on how you set up the tables and DAX.

You can do this in each report in the PBIX file, or use SSAS Tabular as your source and set it up there.

Near SE WI? Join our PUG Milwaukee Brew City PUG
Not applicable

Re: Windows Authentication with Import & Direct Query

Thanks for the reply @Seth_C_Bauer


I managed to do a bit more digging over the weekend and found an explanation from Adam Saxton the sheds a bit more light on the topic:


“If you choose Windows Auth for the data source credentials, within Power BI Desktop, it will use the current user's windows token. Your's on your machine, and their's on their machine.When you publish to the service, and create a data source within the enterprise gateway, you hard code the credential it will use to connect. That will be the same credential for anyone that uses it within the service”


But i also found some MS documents about using single sign-on in gateways that also sounds like if you do these configuration steps on your data source and gateway you should achieve what I’m after. The first two paragraphs of the below link seem to suggest this? No?


Not applicable

Re: Windows Authentication with Import & Direct Query

Can you confirm @Seth_C_Bauer?

Super User
Super User

Re: Windows Authentication with Import & Direct Query

@Anonymous I stand corrected, I've never done this setup personally but it does appear that you should be able to set this up for Direct Query scenarios. Import will still only use the account you set up as the owner of the data source in the service though. I haven't found any new documentation that would make me think differentely for that scenario.

As a side note, DQ is going to be the slowest of all connection types. 

Near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 882 guests
Please welcome our newest community members: