Forum Discussion
PowerBI Embed, App Owns Data, Direct Query
Hi Mario,
I'm looking into something similar at the moment and so here are few pointers if they are of any use:
1) When you say direct query, have you set up gateway datasource to have direct query option ticked?
2) If you are using SSO, have you done all that is required on the gateway machine to delegate?
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sso-kerberos-sap-hana
3) With Direct Query there are 2 cases
i) do you want the report to be run using one single account - so if userA or userB or userC is opening the report the underlying datasource is hit with a predefined username/password which is set in the datasource of gateway
ii) do you want the report to be run in the context of userA if userA has opened the report and userB if userB has opened the report and so on? if so you'd have to configure kerberos SSO
Assming you want the most upto date info on your TV screen, you need direct query report and if you have kerberos SSO enabled your credentials will be passed over to the datasource (App will not have access to the datasource) and the report will keep refreshing (assuming you've enabled page refresh every xx mins). Provided all the settings are configured correctly report should refresh.
-Rakesh
Hi RakeshSinghr - I will try to answer your questions to my beste knowledge:
1) Yes Direct Query is up and running
2) We are sucessfully using SSO (with Hana and other sources), so I imagine our admins have correctly setup kerberos
3) Our gateway is configured that user is passed through to source, so we don't have to overthing row/item level security in PBI anymore. This is then probably the most interesting clue yet. Do we need an additional Gateway setup to be able to impersonate at Gateway level?
You assumption is correct, I setup a direct query report with auto page refresh and embedded it in an "APP owns Data" approach. Thank u, this was very helpful!
Just to eliminate access level issues - can you
1) create a pbix file with just one visual - say a table. Use direct query and deploy to service ( deploy it using app - by invoking the api) so the owner is App
2) you open the report in power bi service and record the session in sap hana to see whether your username is being used to run the query when u open the report. If yes then kerberos SSO has been set up correctly.this also means that on the gateway in advanced options 'use sso for both direct query and import' is ticked and no username/password provided on the datasource within gateway clustee)
Now perform the same for the visual which is cracking. What errors do u get? Share the snapshot of error pls. Also enable verbose logging on gateway and look at the error log and info log.
I did, let say something similar, as I'm not having access to all ressources needed and requested on your end.
I setup a simple report were I'm querying for the logged on username. I specifically created the report with a Database user I own and I retrieve this user from the database on my local machine. I then published it to the PBI Service and run the report off my browser and see that It is know taking my user account for connection via SSO to the source system. I think we have no troubles over all with kerberos as our service is up and running for a lot of users for about half a year.
As expected embedding and running the report with the service principal (app registration) breaks the visual - retrieving this error:
So Service Principal / registered APP is not able to retrieve (or logon) on on-premises Systems. So I think we need a new Gateway connection with preconfigured database access.