Forum Discussion
Memorable MemberOauth2 gateway bugs affecting datasets not dataflows (WHY IN 2024)
Somehow the gateway team has implemented some of the most frustrating oauth code I have ever worked with. The premium workspaces are allowed to run PQ for five hours, yet the oauth code in the gateway will often cause import operations to fail in only an hour or so. Oauth tokens aren't properly managed by the underlying environment. What makes this more frustrating is that the PBI desktop may work fine, but when you go to publish a report to a gateway, we discover all these underlying bugs in the way that the oauth mechanism works on the gateway.
Notice the following admission of the problem, for example:
OAuth2 credentials, the gateway currently doesn't support refreshing tokens automatically when access tokens expire
There is a ray of hope now in 2024.
https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem-faq
Dataflows specifically were recently updated to support token refresh, so they are no longer subject to the 1 hour timeout.
Question - Is anyone familiar with that FAQ? Does anyone know when stuff was "recently updated"? Does anyone know why they fixed dataflows and not datasets?
Any info would be much appreciated.
NOTE:
For years I've been struggling to understand why the oauth2 implementation in the Power BI gateway is so buggy. Part of the problem is that errors aren't surfaced in an obvious way, and the logs don't always indicate when the oauth2 bugs are happening. Instead of making these issues obvious to users, the service just tries to suppress the presence of the bugs by running "retry logic" over and over again, and eventually the refresh operations fail with a timeout error, instead of alerting the customers to an oauth2 bug. It is extremely frustrating!
8 Replies
Hi dbeavon3
As you have learned from the official documentation, for semantic model, when using OAuth2 credentials, the gateway currently doesn't support refreshing tokens automatically when access tokens expire (one hour after the refresh started).
You can submit suggestions at Ideas (powerbi.com) for what new capabilities you’d like to see in the future.
References to “recently updated” in the documentation refer to the April 2024 release of on-premises data gateway. Please refer to On-premises data gateway April 2024 release | Microsoft Power BI Blog | Microsoft Power BI
Follow this link for current support for monthly updates to the on-premise data gateways: Currently supported monthly updates to the on-premises data gateways | Microsoft Learn
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dbeavon3Hi Anonymous
I found the text you referred to in April 2024:... continued ..
"
Hour long refreshes
The on-premises data gateway now supports refreshes longer than one hour running on Dataflows. Refreshes longer than an hour can now refresh tokens mid-stream and continue to run on Dataflows.
"Question - Do you know if there is a conceptual reason (on principle) why this team keeps introducing these mismatching features in datasets vs dataflows? It is very frustrating from a user perspective to have to remember what variety of PQ works in the context of a dataset or a dataflow. In the past, I was under the assumption that dataflows were the ones that were inferior, and were not on the leading edge when it comes to new features and innovations. (An example is "custom connectors" which work fine in datasets but are disallowed in dataflows).
Will it take a lot longer before this bug fix is introduced into datasets, in addition to dataflows? Is it going to be months or years? Almost half a year has passed, and I'm guessing they don't have it very high on their priority list. It almost seems like they are abandoning the existing PQ mechanism in datasets, and are planning to replace it with whatever mechanism they use for dataflows or dataflow gen 2.
This is a very bumpy ride, from a customer perspective. You are basically telling me I have to start re-engineering all my ETL's to run PQ from dataflows, if I anticipate that the refresh times will occasionally last longer than an hour. Can you see why customers don't want to worry about whether their PQ may run for 65 min instead of 45 min?
- dbeavon3
An update from me. The story changed again in the October release. Supposedly they fixed datasets/models to refresh their oauth tokens as well.
The docs were re-written again to talk about the post-October behavior of oauth tokens (2024):
... in my experience the GEN1 dataflows still don't refresh their oauth tokens after an hour, despite what the docs say above. Notice in the highlighted section that they identify other types of PBI items that aren't able to refresh tokens. Its not clear why the GEN1 dataflows are getting second-hand treatment in terms of the oauth refresh behavior, or why the docs seem to ignore the existence of GEN1 dataflows altogether.
Does anyone know how long it will take for Microsoft to abandon GEN1 dataflows?
- dbeavon3
One thing that is particularly frustrating about the dataset oauth2 bug is the inconsistency with dataflows. I've been trying to attach a debugger to find out why the datasets don't properly refresh their oauth2 tokens.
One curious thing I've found with a debugger is that the mashup engine knows that it needs a new oauth2 token after an hour has passed, yet it can't manage to get one. It spends two minutes trying, and keeps giving up. Below is a callstack showing the point of code where the mashup engine spends lots of time, for every round-trip to an AAD kind of connector (... this starts happening once an hour has passed):System.Core.dll!System.IO.Pipes.PipeStream.ReadFileNative(Microsoft.Win32.SafeHandles.SafePipeHandle handle, byte[] buffer, int offset, int count, System.Threading.NativeOverlapped* overlapped, out int hr) Unknown System.Core.dll!System.IO.Pipes.PipeStream.ReadCore(byte[] buffer, int offset, int count) Unknown > mscorlib.dll!System.IO.BinaryReader.FillBuffer(int numBytes) Line 748 C# mscorlib.dll!System.IO.BinaryReader.ReadInt32() Line 281 C# Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.MessageSerializer.Deserialize(System.IO.BinaryReader reader) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.StreamMessenger.Read() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.ChannelMessenger.Read(Microsoft.Mashup.Evaluator.ChannelMessenger.MessageChannel channel) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.ChannelMessenger.MessageChannel.Read() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor<Microsoft.Mashup.EngineHost.Services.RemoteCredentialServiceFactory.CredentialResponseMessage>(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.EngineHost.Services.RemoteCredentialServiceFactory.Proxy.RefreshCredential(Microsoft.Mashup.Engine.Interface.IResource resource, bool forceRefresh) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Runtime.Extensibility.InvocationEngineHost.DataSourceInvocationHost.RefreshCredential(Microsoft.Mashup.Engine.Interface.IResource resource, bool forceRefresh) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.OAuthCredentialExtensions.RefreshTokenAsNeeded(Microsoft.Mashup.Engine.Interface.OAuthCredential credential, Microsoft.Mashup.Engine.Interface.IEngineHost engineHost, Microsoft.Mashup.Engine.Interface.IResource resource, bool forceRefresh) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.HttpResourceCredentialDispatcher.WebRequestCredentialDispatcher.ApplyOAuthCredential(Microsoft.Mashup.Engine.Interface.OAuthCredential credential, bool useBase64Encoding) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.HttpResourceCredentialDispatcher.WebRequestCredentialDispatcher.Apply(Microsoft.Mashup.Engine.Interface.OAuthCredential credential) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.HttpServices.ApplyCredentials(Microsoft.Mashup.Engine1.Library.Http.MashupHttpWebRequest httpRequest, string resourceKind, System.Uri requestUri, Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials, Microsoft.Mashup.Engine.Interface.IEngineHost hostEnvironment, string oAuthResource) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.HttpRequest.ApplyCredentialsToRequest(System.Net.WebRequest webRequest, Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.Request.CreateWebRequest(System.Uri uri, Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.Request.CreateResponse(Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.Request.GetResponseCore(Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.RetryPolicy.Execute<System.__Canon>(Microsoft.Mashup.Engine.Interface.IEngineHost host, Microsoft.Mashup.Engine.Interface.IResource resource, System.Func<System.__Canon> func, Microsoft.Mashup.Engine1.Library.Common.Tracer tracer) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Http.Request.GetResponse(Microsoft.Mashup.Engine.Interface.ResourceCredentialCollection credentials, Microsoft.Mashup.Engine1.Library.Http.Request.SecurityExceptionCreator securityExceptionCreator, bool tokenRefreshed) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.GetResponse(Microsoft.Mashup.Engine1.Library.Http.Request request) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.OpenStream() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.AsBytes.get() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Runtime.Library.Binary.BufferFunctionValue.TypedInvoke(Microsoft.Mashup.Engine1.Runtime.Value value) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue1<Microsoft.Mashup.Engine1.Runtime.Value, Microsoft.Mashup.Engine1.Runtime.Value>.Invoke(Microsoft.Mashup.Engine1.Runtime.Value arg0) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(ref Microsoft.Mashup.Engine1.Language.MembersFrame1 frame) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Microsoft.Mashup.Engine1.Runtime.Value arg0) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(Microsoft.Mashup.Engine1.Runtime.Value frame) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.Instruction.Execute(Microsoft.Mashup.Engine1.Runtime.Value frame, Microsoft.Mashup.Engine1.Language.Instruction[] instructions) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(Microsoft.Mashup.Engine1.Runtime.Value frame) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(Microsoft.Mashup.Engine1.Runtime.Value frame) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.RuntimeFunctionValueN.Invoke(Microsoft.Mashup.Engine1.Runtime.Value[] args) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Runtime.TransformValueReference.Value.get() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Runtime.RecordValue.ReferenceArrayRecordValue.this[int].get(int index) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.Query.ExpandListColumnQuery.GetRowsets() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.Query.ExpandListColumnQuery.GetRows() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine1.Language.Query.SelectRowsQuery.SelectRowsEnumerable.SelectRowsEnumerator.MoveNext() Unknown Microsoft.MashupEngine.dll!Microsoft.OleDb.Serialization.DataReaderPageReader.ObjectPage.Read(System.Data.IDataReader reader, int rowCount) Unknown Microsoft.MashupEngine.dll!Microsoft.OleDb.Serialization.DataReaderPageReader.Read(Microsoft.OleDb.Serialization.DataReaderPageReader.ObjectPage page) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.Read(Microsoft.Data.Serialization.IPage page) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.RemotePageReader.RunStub.AnonymousMethod__0() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(string entryName, Microsoft.Mashup.Engine.Interface.IEngineHost engineHost, Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, System.Action action) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.RemotePageReader.RunStub(Microsoft.Mashup.Engine.Interface.IEngineHost engineHost, Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, System.Func<Microsoft.Data.Serialization.IPageReader> getPageReader) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult.AnonymousMethod__0() Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(Microsoft.Mashup.Engine.Interface.Tracing.IHostTrace trace, Microsoft.Mashup.Engine.Interface.IEngineHost engineHost, Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, System.Action action) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult<Microsoft.Mashup.Engine.Interface.IDataReaderSource>(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.BeginGetResultMessage message, System.Action<Microsoft.Mashup.Evaluator.Interface.EvaluationResult2<Microsoft.Mashup.Engine.Interface.IDataReaderSource>> action) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetDataReaderSource(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.BeginGetDataReaderSourceMessage message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(Microsoft.Mashup.Evaluator.Interface.IMessageChannel baseChannel, Microsoft.Mashup.Evaluator.ChannelMessenger.MessageWithUnknownChannel messageWithUnknownChannel) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(Microsoft.Mashup.Evaluator.Interface.IMessageChannel channel, Microsoft.Mashup.Evaluator.Interface.Message message) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.EvaluationHost.Run() Unknown Microsoft.Mashup.Container.NetFX45.exe!Microsoft.Mashup.Container.EvaluationContainerMain.Run(object args) Unknown Microsoft.MashupEngine.dll!Microsoft.Mashup.Evaluator.SafeThread2.CreateAction.AnonymousMethod__0(object o) Unknown Microsoft.Mashup.Container.NetFX45.exe!Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(string[] args) Unknown Microsoft.Mashup.Container.NetFX45.exe!Microsoft.Mashup.Container.EvaluationContainerMain.Main(string[] args) UnknownNotice that the stack spends a lot of time waiting for a response within the method:
OAuthCredentialExtensions.RefreshTokenAsNeeded
Would sure be nice if the dataset would refresh the token as needed!
I know that Microsoft probably had the best of intentions when it fixed the oauth behavior of dataflows. But having done so in dataflows, they need to also do the same for datasets. Else this just has the appearance of a silly bug.
It is a massive problem when PQ is not compatible or portable between datasets and dataflows. Users waste massive amounts of time flailing about with issues like this one!
- dbeavon3
Here is the callstack when the datasets are misbehaving and are failing to refresh their oauth tokens:
This represents the point in the code where all my problems seem to come from. I'm also trying to reach out to Mindtree support about this but have had very little engagement as-of now.
