Forum Discussion
Power BI Gateway looses Service Principal credentials
Hello,
We are using Power BI to render reports having a SQL Server as one of the datasources. The SQL server is the built-in server in Azure Synapse Analytics. We have a Power BI Gateway configured with network access to the SQL Server.
The Connection to the SQL Server is configured with a Service Principal (we use Client ID + Client Secret + Tenant ID).
This worked fine for few weeks, but last Thursday (August 8th, 2024) things went sideways.
Reports failed to refresh giving a DM_GWPipeline_Gateway_InvalidConnectionCredentials error.
The Connection showed up as Offline:
The weird thing is that once we refresh the SQL Connection with the exact same Client ID + Client Secret + Tenant ID, the status turns to Online:
and reports start refreshing for a while (usually about an hour). Then a refresh fails, the connection truns offline again and stays that way untill we re-input the service principal details again.
The issue still persists today, so we assume it's not a glitch. We didn't update the Power BI gateway since the issue occured. Nothing informative could be found in the Gateway logs.
This issue doesn't show when the SQL Connection is done with Oauth.
Looking forward to any ideas for why this may happen.
Cheers
12 Replies
- lbendlin
Super User
Oauth tokens do expire. Looks like your company IT has changed the expiry timeout.
We experienced same issue on a newly created web connection (cannot say if it worked before as we did not use this type od connection before).
Connection Via SPN is valid ca. 1 hour, after that time all schedulled reports fail with same error message. Recreating connection string helps for one hour.
Gateway is up to date (August '24 version).
Any ideas how to proceed?
Hello Paulina_B,
Could you please collect logs in the Power BI Gateway when issue reoccurs? The additional logging should be checked.
We've been asked to do this by Microsoft support but the issued doesn't surface anymore on our end.
Have a great day.
- Similarly, the issue is gone 🙂 we did nothing, refreshes worked properly after the weekend
Hello,lbendlin ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,Anonymous.I am glad to help you.
Based on your description, I think there is merit in what lbendlin is proposing.
According to the official Platform workflow, when a user accesses a series of Microsoft products, it involves authentication and authorization methods for different types of applications. Generally it involves the token authentication process.As for the token lifecycle, the official documentation also describes it, mentioning that it is typically 60 minutes to 90 minutes (about an hour).
When the token life cycle is over. It will expire and need to be reapplied.
And for your description and expiration time is also similar to the official description.This kind of problem is not only for Power BI, in fact, for most cases, token expiration is an unavoidable problem. I hope you can understand, of course, for the gateway connection and the service subject permissions are also you need to check the place, sometimes the change of permissions will lead to connection problems.
Here is the relevant documentation to help you.
URL:
Microsoft identity platform overview - Microsoft identity platform | Microsoft LearnAccess tokens in the Microsoft identity platform - Microsoft identity platform | Microsoft LearnHi,Anonymous.Thank you for your reply.
Thank you for your reply, you can try updating the gateway connection because according to your description, your gateway has a warning triangle error, which generally indicates a problem with the gateway itself or a serious problem with the gateway connection configuration. I noticed that you mentioned that your gateway is not currently being updated and that the issue has not existed since it was first configured, but has been an issue for the last two weeks. If you don't think it has anything to do with expired authentication tokens and you want to make sure that your authentication credentials are correct and not expired, I suggest you update your gateway to the latest version.
I would recommend that you update your gateway to the latest version, even though it is a cloud connection, each new version of the gateway usually fixes some of the known issues and bugs in the previous version. These fixes may directly address connection credential errors you are currently experiencing. New versions of the gateway may contain performance improvements that make the data connection and refresh process faster, more stable, and more compatible.
Also check your firewall for some recent rule changes where there are requests being blocked.
Clear your browser cache and reset your gateway connection to make sure that the authentication information, and the data source you are connecting to are correct. Sometimes network instability or server performance can also affect the successful execution of refresh results, and it is recommended that you check for these cases, although they are unlikely.
You mentioned that this does not happen when using Oauth to complete the SQL connection.
In my opinion, using Oauth is a viable alternative if the problem persists after the above version update and gateway connection configuration.
URL:
Troubleshoot gateways - Power BI | Microsoft LearnI hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,Anonymous .Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,Anonymous.
I'm very happy to see that your issue has been resolved. I judged that it might be that the Power BI Service itself was in some condition for some reason. But fortunately it has been resolved. You can mark your reply as a solution. Thank you very much for supporting Power BI.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
