none
Unexpected Error When Contacting Power Query Service

    Question

  • Hi Team,

    Having been blown away with an online demo of Power Query, I've just installed version 1.5.3296.2082 but I can't get the Online Search to work ("An unexpected error occurred when contacting the Power Query service").

    I've tried the workaround suggested in an earlier post (I am behind a corporate proxy), creating the file Microsoft.Mashup.Container.exe.config in \Program Files\Microsoft Power Query for Excel\bin, but it doesn't seem to work for me.

    Any pointers appreciated.

    Regards,

    Graham.

    Thursday, October 10, 2013 9:02 AM

Answers

All replies

  • This is probably caused by being behind a corporate proxy. There are two ways to fix this:

     

    1) Set ServiceCertificateValidationDisabled to 1

    The easiest way is to set a DWORD value called ServiceCertificateValidationDisabled in the registry (under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel). If that value is 1, Power Query will skip the certificate validation check which makes sure that the certificate was issued by Microsoft. The downside to this method is that it can open you to some attacks (look up the attack on DigiNotar for an example), so if you do not fully trust your network you should use the second method.

    2) Add the certificate thumbprint to ServiceCertificateThumbprints

    You can add the certificate thumbprint the proxy uses to the ServiceCertificateThumbprints value in the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel). You can add the value to the default one by putting a semicolon after the default value and adding your own.

    To get the thumbprint:

    1. Go to the Power Query page in IE.  For your version, this would be https://de-users.sqlazure.com/ .
    2. Right-click on the page and select "Properties"
    3. Click on certificates
    4. Go to the "Certification Path" tab. The proxy's certificate is probably the one at the top.
    5. Click on that certificate and choose View Certificate
    6. Go to details and look at the Thumbprint value.  This value (with no spaces) is the one you want to add to the ServiceCertificateThumbprints value.

    Note: If you are using a 32-bit version of Power Query on a 64-bit operation system, then the registry values may be located under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel)

    Thanks.

    -Alejandro Lopez-Lago (MSFT)


    Edit: Changed " If you are using the preview, " to "For your version, this would be https://de-users.sqlazure.com/" .
    Thursday, October 10, 2013 10:01 PM
  • Hi Alejandro,

    Thanks for the quick response.

    I tried the first option, but it doesn't seem to make any difference. As far as the second option is concerned, www.de-users.sqlazure.com (which is reflected in the ServiceUrl registry entry) gives me a 404.

    Regards,

    Graham.

    Friday, October 11, 2013 11:22 AM
  • You can still get the certificate used by the server on the 404 page. However, if the first option did not work, the second option should not work either. This appears to be a different issue. Would you be able to send a frown on this issue?

    Thanks.

    -Alejandro Lopez-Lago (MSFT)

    Wednesday, October 16, 2013 1:21 AM
  • I am able to use online search as long as I am not going through the corporate proxy.

    Adding https://de-users.sqlazure.com/ to the proxy list does not seem to help.

    I am pretty confident that this error is related to the proxy.

    “An unexpected error occurred when contacting the Power Query service”

    I need to provide our IT department with a solution.  Any new ideas?

    Thanks,
    -Phil

    Wednesday, October 23, 2013 3:08 PM
  • I tested the Power Query service on my home network and everything worked fine.

    At work, I asked our IT department to add the following sites to the Proxy safe list and now all is well and I am able to query public cloud data as well as Azure Marketplace data:

    https://api.datamarket.azure.com
    http://publicdata.clouddatahub.net
    https://de-users.sqlazure.com

    Thursday, October 24, 2013 2:53 PM