locked
Get data from web - with authentication - connection error RRS feed

  • Question

  • Hello everyone,

    I regularly used to connect to a website within Excel and pull down a data table. This website requires authentication to login. When I try to do this now I get an error:

    "Unable to connect.

    We encountered a problem while trying to connect.

    Details: "The credentials provided cannot be used for the Web source. Please update the credential type through a refresh or in the Data Source Settings dialogue to continue."

    This is is Excel 2016, 365. Does anyone else use this functionality and have it working okay? - Used to work fine in earlier versions where you had the web browser interface and could login directly. I was trying to think of a more generic login based site I could use as a test - I tried two sites but they're both quite specific (an internal crm and Atlassian's Jira).

    Thanks

    Monday, October 30, 2017 11:29 AM

All replies

  • Hi Rob-R,

    Do you mean when you try to use Power Query to connect to a website you will get a error and cannot get data from the website?

    And you want to know if you can get the way of get data from web like this:

    If my understanding correct?

    If yes, please Go to File- Options- Customize Ribbon- select All Commands- From Web(Legacy)- and add it to New group under Insert Tab:

    Please check if this is you need and let me know, I'm glad to help you.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, October 31, 2017 8:33 AM
  • I have the same problem. When I try to get data from web (Data -> Get & Transform Data -> From Web), which needs Windows Authentication, Excel refuses to get the data saying that credentials provided cannot be used for web source. When I try to put the same address in any browser, enter the same credentials, page is displayed correctly.

    Using Fiddler I can see that Excel is able to authenticate and connect to provided URL, correctly downloads the data several times, but then shows the above mentioned error message, which is absurd.

    The web page is standard html containing only 1 table, which I want to import.

    Emi Zhang's proposal to use legacy way of importing data from web is working correctly. But I cannot use this approach, because that way I am not able to import data into Data Model in Excel and connect with other data sources.

    Power BI Desktop behaves exactly the same. There is clearly something broken with getting data from web sites, which require authentication.

    Saturday, November 4, 2017 2:00 PM
  • I turned on tracing in Query Options in Excel and have copied errors and warnings below from resulting 2 log files. It seems all requests time out because of invalid credentials. However, I am 100% sure that those credentials are correct. As I said previously, the same page loads in web browsers, but not in Excel when using "From Web" (but using "From Web (Legacy)" works with the same credentials). There is clearly something wrong with authentication process in Excel.

    ---------- EXCEL.13520.2017-11-04T15-06-10-702867.log ----------

    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:17.0493964Z","Action":"PackagePartitionAnalysisInfo/SetPreviewValue","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Host.Document.Analysis.IncompleteFormulaAnalysisException, Microsoft.Mashup.Document, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Input task 'Source' is incomplete.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()\r\n at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"EXCEL","Pid":13520,"Tid":22,"Duration":"00:00:01.3939796"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3451607Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"11","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClassa`1.<AddHandler>b__8(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0014164"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3429856Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClassa`1.<AddHandler>b__8(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0036210"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3655604Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"11","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0002434"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3652089Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0006265"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3743880Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"11","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0003374"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3739089Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0008523"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3172348Z","Action":"PackagePartitionAnalysisInfo/SetPreviewValue","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0580535"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9458296Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"14","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClassa`1.<AddHandler>b__8(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0031596"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9438549Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClassa`1.<AddHandler>b__8(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)\r\n at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0051827"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9674712Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"14","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0002295"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9671715Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0005563"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9759954Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/TranslateCancelExceptions","HostProcessId":"13520","identity":null,"evaluationID":"14","cancelled":"False","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0003141"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9756161Z","Action":"RemoteDocumentEvaluator/RemoteEvaluation/HandleException","HostProcessId":"13520","identity":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0007492"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9014580Z","Action":"PackagePartitionAnalysisInfo/SetPreviewValue","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"EXCEL","Pid":13520,"Tid":9,"Duration":"00:00:00.0754312"}

    ---------- Microsoft.Mashup.Container.NetFX40.8784.2017-11-04T15-06-20-316842.log ----------

    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3218674Z","Action":"SimpleDocumentEvaluator/GetResult","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Runtime.HostResourcePermissionService.VerifyPermissionAndGetCredentials(IEngineHost hostEnvironment, IResource origin, IResource resource, IDataSourceLocation location)\r\n at Microsoft.Mashup.Engine1.Library.Http.HttpServices.VerifyPermissionAndGetCredentials(IEngineHost host, IResource resource, Boolean requireWebKey, ResourceCredentialCollection& credentials)\r\n at Microsoft.Mashup.Engine1.Library.Web.HttpRequest.VerifyPermissionAndGetCredentials(ResourceCredentialCollection& credentials)\r\n at Microsoft.Mashup.Engine1.Library.Web.WebModule.WebContentsFunctionValue.TypedInvoke(TextValue url, Value options)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Runtime.FunctionValue.MetaTypeFunctionValue.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Runtime.SerializationApplyingModule.SerializableFunctionValue.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)\r\n at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(Value key)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)\r\n at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame0& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.ListValue.get_Item(Value key)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.List.ElementWithListCheckFunctionValue.Invoke(Value collection, Value key)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)\r\n at Microsoft.Mashup.Engine1.Language.F","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0079236"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3174405Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()\r\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass2.<BeginGetResult>b__0(EvaluationResult2`1 result)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0130451"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:20.3305694Z","Action":"RemotePreviewValueSource/RunStub","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Exception of type 'Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException' was thrown.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClassb.<>c__DisplayClassd.<OnBeginGetPreviewValueSource>b__a()\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"cc103e3d-f95f-432a-a462-d419a1c6a1ea","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0009344"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9037234Z","Action":"Browser/ThreadCreate","HostProcessId":"13520","RequestUrl":"https://my.web.com/ApplicationPages/GetUserInfoForExport.aspx","Text":null,"Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Html.Browser.CheckSupported(ResourceCredentialCollection credentials)\r\n at Microsoft.Mashup.Engine1.Library.Html.Browser.LoadDocument(NoUIWebBrowser browser)\r\n at Microsoft.Mashup.Engine1.Library.Html.Browser.GetDocument(NoUIWebBrowser browser)\r\n at Microsoft.Mashup.Engine1.Library.Html.Browser.ThreadCreate()\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":8,"Duration":"00:00:00.0141536"}
    DataMashup.Trace Error: 24579 : {"Start":"2017-11-04T15:06:52.9034529Z","Action":"Engine/IO/Browser/GetDomFromUri","HostProcessId":"13520","RequestUri":"https://my.web.com/ApplicationPages/GetUserInfoForExport.aspx","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Html.Browser..ctor(IEngineHost host, String text, Request request)\r\n at Microsoft.Mashup.Engine1.Library.Html.Browser.GetDomFromRequest(IEngineHost host, Request request)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0160573"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9227564Z","Action":"SimpleDocumentEvaluator/GetResult","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Html.Browser..ctor(IEngineHost host, String text, Request request)\r\n at Microsoft.Mashup.Engine1.Library.Html.Browser.GetDomFromRequest(IEngineHost host, Request request)\r\n at Microsoft.Mashup.Engine1.Library.Html.HtmlModule.PageFunctionValue.TypedInvoke(Value html)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue1`2.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Runtime.FunctionValue.MetaTypeFunctionValue.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Runtime.SerializationApplyingModule.SerializableFunctionValue.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)\r\n at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(Value key)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)\r\n at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)\r\n at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame0& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.ListValue.get_Item(Value key)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.List.ElementWithListCheckFunctionValue.Invoke(Value collection, Value key)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)\r\n at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.Instruction.ExecuteCondition(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.ExecuteCondition(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.IfInstruction.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(Value frame)\r\n a","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0080028"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.8981133Z","Action":"SimpleDocumentEvaluator/GetResult<IPreviewValueSource>","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()\r\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass2.<BeginGetResult>b__0(EvaluationResult2`1 result)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0331320"}
    DataMashup.Trace Warning: 24579 : {"Start":"2017-11-04T15:06:52.9313332Z","Action":"RemotePreviewValueSource/RunStub","HostProcessId":"13520","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine.Interface.InvalidResourceCredentialsException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The Web.Page function does not support this authentication type.\r\nStackTrace:\n at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClassb.<>c__DisplayClassd.<OnBeginGetPreviewValueSource>b__a()\r\n at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass5.<RunStub>b__3()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\n\r\n","ProductVersion":"2.51.4885.261 (PQ-CY17SU10)","ActivityId":"2a60d8e0-3e0e-490b-b67d-bcbbb9861417","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8784,"Tid":1,"Duration":"00:00:00.0009167"}


    Saturday, November 4, 2017 3:32 PM
  • Thanks, that's really interesting, it certainly seems to tally with my experiences when comparing the two methods.

    Is the above sufficient as a 'bug report' then?!

    Thanks

    Tuesday, November 7, 2017 9:25 AM
  • This has to be a problem that needs to be reported. I am getting the same error when using the GetData from Text/CSV option. I paste the URL into the Text Import folder and select open, here is where I get the authenticate problem. Based on the info above, I would report it but don't know how. If you tell me then I will do it. Also, does anyone know if this has something to do with stupid Verizon chaning policies now?
    Wednesday, December 20, 2017 4:11 PM
  • The newer experience uses Power Query for retrieving the web page.  Specifically the From Web experience is using the M function Web.Page.  Web.Page uses IE to read the webpage, and IE does not give the ability to compartmentalize cookies, which could present a security risk of leaked cookies.  To prevent the security risk Basic Auth and OAuth are prohibited with Web.Page.

    The team knows that this is painful and is actively working on a solution to provide a better experience.

    The possible current workarounds are:

    1. Use the Web.Contents M Function instead.  Note that Web.Contents does not process any javascript, and will only be usable for static pages.

    2. Use the "From Web (Legacy)" option mentioned earlier in the thread (File -> Options -> Data -> "Show legacy import wizards"  and check the "From Web (Legacy)" option. 

    A "Legacy Wizards" entry will show in the "Get Data" menu.

    Tuesday, March 13, 2018 7:49 PM
  • Jeff,

    Thanks for this clarification.

    We use the legacy process because we require http-basic authentication for the web service providing the data. This is a web service specifically written (by us) to provide the data specifically for processing by Excel.

    Additionally, because our software providing this data can be deployed on customer's own systems, and our customers have enough trouble understanding the concepts behind what we're doing to achieve this we provide a "template" .xlsx file to provide the charts and beautification for the data and into which we can inject the URL for their deployment of the system (together with pertinent parameters) by extracting the xml portions, updating the URL and rebuilding the .xlsx content programmatically, outside Excel.

    We can continue to use the legacy web wizard, but before that is dropped (if ever) we would need to be able to achieve this using the method that replaces it.

    We are not tied to http-basic authentication, but would need technical details on the replacement in order to write our own service to replace it.

    The new PowerQuery approach creates an "impenetrable" .xml object in the .xlsx archive. We would either need details on how to update the URL in that, or an alternative approach to specifying the URL.

    I am happy to discuss this further - together with any solutions you're considering.

    Thank you,
    Tim

    Wednesday, April 25, 2018 10:31 AM
  • Hey Tim, thanks for reaching out.  The good news is that we are working on a solution that would allow basic auth over HTTP, and there are no plans to remove the legacy web wizard.

    With regard to your question about replacing the URL, consider using the VBA automation interfaces for queries.  

    https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

    With it you can look at the M text of a query, replace the URL, and store the new M text.

    -jeff

    Wednesday, April 25, 2018 4:05 PM
  • Jeff,

    Thanks for acknowledging how "painful" it is that the Web.Page M function is unable work with online html pages that are protected by password.  Any indication of when this serious shortcoming may be alleviated?

    In the meantime, I'm curious about your suggestion #1 above.  Suggestion #2 doesn't seem workable in my situation given that I'd like to be able to pull the html table of interest into Power Query's Data Model, along with other similar html pages stored under password.

    Your suggestion #1 seems to indicate that the Web.Contents M function is a less capable but possibly sufficient substitute in certain circumstances for the Web.Page function.  Could you point me to any references that would help me explore this possibility?  So far, I've been able to use Lines.FromBinary to be able to actually see the html in a list, but it now seems apparent that I'll have to "reinvent the wheel" and come up with my own parsing routine to actually turn the html into a usable table in an automated fashion.  I'd have to admit I'm a bit of a novice when it comes to Power Query's M functions, so am I missing something easy here?

    On the other hand, I can manually download the password protected html file, manually open it with Excel, and … voila … it's a usable table.  Remind me again why Web.Page can't seem to automatically do what I just did manually?  Sorry … just had to vent some frustration there.

    Thanks in advance for any suggestions you may be able to provide.

    Kevin

    Friday, June 29, 2018 4:37 PM
  • Kevin,

    Can you try wrapping the Web.Contents in Text.FromBinary and passing that into Web.Page.  When you have Web.Contents nested directly inside of Web.Page, Web.Page picks up some metadata from Web.Contents, and makes the http call itself instead of letting Web.Contents make the call. The Text.FromBinary should interrupt the flow of metadata and get you what you need.

    So you should end up with something like this.

    = Web.Page(Text.FromBinary(Web.Contents("http://microsoft.com")))

    Thanks,

    -jeff

    Tuesday, July 3, 2018 5:36 PM