Forum Discussion
Help: Web API works in Desktop but disconnected in Service
Hello,
I have a report that I have created in desktop that uses two connections to the data. The first via an ODBC connection and the second through a web API. When I refresh the data, everything works fine in the Desktop. When I publish to Service, I get a message that the file was published but disconnected.
I can narrow the error down to the Web API not working properly. I'm connectecting through http:// header call with API key and API secret key on the desktop. I cannot find an option in service to link via an API key. I've tried adding a gateway as 'anonymous' but that doesn't work. How am I able to pull in this data?
Hi Chief,
the first step is to create a free Azure account at https://azure.microsoft.com/free.
Once signed in, they should go to the Azure portal and register a new app under Azure Active Directory > App registrations and name the app (e.g., “PowerBI_API_App”), choose “Single tenant,”. After registering, generate a client secret under “Certificates & secrets” and copy the value immediately, as it won’t be shown again.Next, assign API permissions to the app by going to “API permissions,” selecting “Power BI Service,” and adding delegated permissions like Dataset.Read.All, Report.Read.All, and Workspace.Read.All. Admin consent must be granted for these permissions to take effect.
Then, in the Power BI Admin Portal (https://app.powerbi.com/admin-portal), enable tenant settings to allow service principals to use Power BI APIs and access workspaces. Finally, go to the Power BI workspace, click “Access,” and add the registered app (service principal) with a role like Member or Admin.
This setup allows Power BI Service to authenticate securely using the app instead of relying on unsupported API key headers.
Hope above setup resolves your query and with little research you will be able overcome permission issues in your usecase mentioned above
Thanks,
Prashanth Are
MS fabric community support
8 Replies
Hi Chief
As far as I am aware, if you are using an ODBC connection you will need to use a gateway to authenticate. Quite often what happens is when you have two different data connections, both of them will have to go through the gateway. So what you'll need to do is in the Power BI service you will have to create a new API/Web connection that will then go via your gateway. Add or remove a gateway data source - Power BI | Microsoft Learn
thanks for the response. When estabilishing on premis data gateway test connection fails to authenticate. I've tried Anonymous, Basic, etc. but none will allow connection. Result is: credentials provided for the source are invalid. I'm not sure what credentials they are looking for, my standard username and password to log into the web application or something else. Nothing seems to work.
Power BI Service can’t handle custom header auth directly.
Works in Desktop but fails in Service because Service doesn’t store API header credentials.
Fix: Install and use an On-premises data gateway (personal) → keep your Web.Contents with headers in Power Query.
Alternative: If API allows, switch to Basic Auth (username/password) or query string keys so you can set credentials directly in Service.
👉 Easiest solution: Use a gateway for the Web API connection.
Shahid12523 thanks for the response. When estabilishing on premis data gateway test connection fails to authenticate. I've tried Anonymous, Basic, etc. but none will allow connection. Result is: credentials provided for the source are invalid. I'm not sure what credentials they are looking for, my standard username and password to log into the web application or something else. Nothing seems to work.
Hi Chief,
Am attaching a similar type of thread link there they also used some API's to refresh there dataset see the thread and follow the steps or the lines of code what they used and what not done by you i think this will get your issue resolved.
Solved: Refreshing data from REST API works on Desktop, fa... - Microsoft Fabric Community
thanks,
Prashanth Are
Thanks for the tip. I did try that and still does not refresh. There is a work around to set up an API that pulls data into database on my server and point the PBI Service query to that instead of the direct source. The only problem is it's a work around. I'm not fully understanding why desktop would pass it fine and service hangs up but will probably be addressed at some future point. From my reading I think I could use Azure to hold my API keys and pass the query through that but I don't have the Azure service subscription and don't know anything about how it works.
Hi Chief,
the first step is to create a free Azure account at https://azure.microsoft.com/free.
Once signed in, they should go to the Azure portal and register a new app under Azure Active Directory > App registrations and name the app (e.g., “PowerBI_API_App”), choose “Single tenant,”. After registering, generate a client secret under “Certificates & secrets” and copy the value immediately, as it won’t be shown again.Next, assign API permissions to the app by going to “API permissions,” selecting “Power BI Service,” and adding delegated permissions like Dataset.Read.All, Report.Read.All, and Workspace.Read.All. Admin consent must be granted for these permissions to take effect.
Then, in the Power BI Admin Portal (https://app.powerbi.com/admin-portal), enable tenant settings to allow service principals to use Power BI APIs and access workspaces. Finally, go to the Power BI workspace, click “Access,” and add the registered app (service principal) with a role like Member or Admin.
This setup allows Power BI Service to authenticate securely using the app instead of relying on unsupported API key headers.
Hope above setup resolves your query and with little research you will be able overcome permission issues in your usecase mentioned above
Thanks,
Prashanth Are
MS fabric community support
