Oauth2 gateway bugs affecting datasets not dataflows (WHY IN 2024)

[dbeavon3]

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:

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-tshoot#limitations-and-co...

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!

[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)	Unknown

Notice 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.

[Anonymous]

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.

[dbeavon3]

Hi @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?

[lbendlin]

My money is on "when the last P SKU contracts have expired and everybody is on F SKUs"

[dbeavon3]

After two months of waiting for Microsoft to provide support to Mindtree, they finally came back with the following response.  It covers the mid-stream oauth-refresh bugs, and the deprecation of GEN 1 dataflows as well.

Quote:

Yes, the Gen 1 Dataflows will be deprecated but unfortunately, we don’t have an ETA for it. However, this is one of the reasons why the PG has discontinued “mid-stream token refresh” in the Dataflows Gen1.

... 

midstream token refresh is completely discontinued from Dataflow Gen 1 and the product team will no longer support it. This is a product limitation when using Dataflows Gen 1.

If there was any doubt, Power BI customers should begin abandoning GEN1 dataflows for the more expensive GEN2 variety.  I have found the costs to be substantially more... this is primarily because GEN2 dataflows will also charge for (1) the passage of time, and (2) the use of the self-hosted on-premise gateway.  When accounting for those things it leads to far more CU consumption than before.  With GEN1 dataflows we seemed to be paying only for the actual compute (vcores) that is used in our premium capacity.   But with GEN2 they have found other meters to to use, in order to rapidly decrement our CU's.