power bi gateway data source credential vs report data source credential

[Anonymous]

Hi,

Currently I am using direct connect to my on-premises SQL server data source via Enterprise Gateway.

My understanding is that if I publish a report to Powerbi service, the report will be using the credential (Credential A) specified in the report to connect to the SQL server regardless of which users use the report. 

The confusion comes in is that the SQL server data source configuration under the Enterprise Gateway also needs a credential  (Credential B). I can assume this is only used for Power BI internal use.

Apparently for the whole things to work, both credential A and B need to have sufficient access to the SQL server. But each report hit will hit the SQL server as credential A, I assume. Could anyone please confirm / clarify this?

Thanks,

[ankitpatira]

@Anonymous Yes credentials A which are stored in cloud (ofcourse encrypted) are passed to the gateway when you access report in power bi service. In gateway on-premise they are decrypted and used to access the data source. Can you not use credential A for both gateway and report data source. 

[Anonymous]

@ankitpatira Thanks for clarifying. I assume you asked me why not use credential A for both configuration. My consideration is that for the gateway setup (credential B), the credential B should be a system account rather than user account. What if the User B leaves the company and the account is closed, the data source for the Gateway will be broken. Credential A does not have this problem because if A leaves, new user C publishing the same report will be using credential C to connect to the server. Hope this makes sense.

[Anonymous · Accepted solution]

@ankitpatira Another test I think I found the truth. Which is, when the report is executed on the power bi service, it turns out using the credential B to hit the database - which is the gateway's data source credential. When the report is executed on the Power BI desktop, it uses credential A to hit the database. 

I used the profiler the trace the database users, so I am confident this is the case and it makes perfect sense. Any objections?