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.
@slip67 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.
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?
@slip67 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.