Connect to Excel sheet in office 365 group through On-Premise Gateway

[dramus]

I can connect successfully using PBI Desktop, but when I try to add the datasource to our On-Premise gateway it fails.

Logging on to the machine hosting the gateway, I can successfully use the URL to access the spreadsheet in a browser.

The error that is returned is Invalid credentials. Now, I know I'm putting in the right password, But what format should the username be? name@company.com, company\name, or just name?

Looking at the logfile for the gateway, I see the following:

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_InvalidConnectionCredentials
Reason=<pi>AccessForbidden</pi>
DataSource=<pi>Web/https://cfpwood.sharepoint.com/sites/cabinotchsalesgroup/Shared%20Documents/Budgets/Sales_Budget_Sum...>
DataSourceReference=
DataSourceOrigin=
DataSourceReferenceOrigin= --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupCredentialException:<pi>Microsoft.Data.Mashup.MashupCredentialException (0x80004005): The credentials provided for the Web source are invalid. (Source at https://cfpwood.sharepoint.com/sites/cabinotchsalesgroup/Shared%20Documents/Budgets/Sales_Budget_Sum....)
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()</pi>

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
InnerType=MashupCredentialException
InnerMessage=<pi>The credentials provided for the Web source are invalid. (Source at https://cfpwood.sharepoint.com/sites/cabinotchsalesgroup/Shared%20Documents/Budgets/Sales_Budget_Sum...>
InnerToString=<pi>Microsoft.Data.Mashup.MashupCredentialException (0x80004005): The credentials provided for the Web source are invalid. (Source at https://cfpwood.sharepoint.com/sites/cabinotchsalesgroup/Shared%20Documents/Budgets/Sales_Budget_Sum....)
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()</pi>
InnerCallStack=   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()
  ([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:)
   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.StartEvaluationAndGetResultSource[T](Int32 timeout)
   at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
   at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()
   --- End of inner exception stack trace ---
  ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessCredentialException.StackTrace:)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.<TestConnectionAsync>d__19.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.DbFullConnectionString.<TestConnectionAsync>d__23.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_0.<<TestDataSourceConnection>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<TestDataSourceConnection>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass5_0.<<EncryptCredentialsWithTestDataSourceConnection>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<EncryptCredentialsWithTestDataSourceConnection>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<DispatchImpl>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<Dispatch>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<DeserializeImpl>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<>c__DisplayClass8_0.<<Deserialize>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<Deserialize>d__8.MoveNext()

[v-haibl-msft]

@dramus

Are you getting data from excel sheet in office 365 group according to https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-onedrive-business-links/?

If yes, you do not need to configure on-premises gateway for it. You can connect directly after edit the web credential in Power BI Service.

Best Regards,
Herbert

[dramus]

Unfortunately the dataset is also connected to an on-prem Oracle dataset, which requires the use of the gateway.

[v-haibl-msft · Accepted solution]

@dramus

Currently, on-premise data gateway doesn't support OAuth2 based authentication. We are not able to add the online data sources in data gateway as well as on-premise data sources, so that we can't configure this kind of combined dataset to use data gateway. The workaround is to use on-premise data gateway (personal mode).

PG have started working on the data gateway to fix this issue. See: Combine enterprise gateway and online data sources.

Best Regards,
Herbert

[dramus]

And that is what I finally figured out. Now I just have to wait.

[julieTE]

I had quite the same issue with a report linking to an excel file on a group Onedrive, and when actualizing the report on the webservice, I didn't get the Oauth option.

Thanks to this post I got that I was copying the link of the file the wrong way, it really is important to get the link by opening the file and getting to "Information", the link is slightly different.