Power Query Sign in, Certificates and Troubleshooting

I worked on a Power Query Sign in issue recently that took a different spin from what I would have expected. Through the course of troubleshooting that issue, I actually learned a bit about how the sign in process works with Power Query, so I thought I would share some of those findings along with how to identify some of this yourself.

What was happening is when trying to Sign In on the Power Query tab we would get an error. The message now is actually worded a little more clear/helpful than it was at the time I first got the issue.

Problem Signing InA trust relationship with the service could not be established. Please make sure that your client is up-to-date, and that no proxy between the client and the service is changing certificates.

The original error we got was the following:

Problem Signing InThe service could not be contacted while signing in. This could be an issue with your network connection, or the service may be unavailable. Please contact your IT administrator if this issue persists

It was updated to include better information relating to the SSL/Certificate issues happening under the hoods.

Power Query Tracing

The Power Query Add-in has it’s own tracing mechanism. This is enabled by way of a registry key. This is our first step to see what is actually happening.

System.Security.Authentication.AuthenticationException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote certificate is invalid according to the validation procedure.

All of the traffic to the cloud services uses HTTPS/SSL. So, what URL was it trying to hit that had the certificate issue? The answer to that is in the log entry after the first error:

Fiddler can also show you some of the flow here, but not necessarily show you the problem with regards to the SSL piece. Another hint at what the URL is that we are hitting that’s causing a problem is the Service URL that’s in the Power Query Registry key.

"ServiceUrl"="https://de-users-preview.sqlazurelabs.com"

You can see the certificate if you browser to that URL in Internet Explorer.

The part that I’ve blacked out is the Certification Path Chain for the actual certificate. What is happening here is that if you are behind a Proxy, the Proxy can inject it’s Certificate to the Path. When that happens, we aren’t able to validate the certificate at that point. You will typically only see this in a corporate environment. For example, at Microsoft this happens (the blacked out part). So, that’s great for me as I could easily reproduce the issue. You can actually repro this on your own if you try to capture a trace with Fiddler for Power Query, as it will inject a Proxy as well and do something similar.

So, now that we know what the problem is, how to we get around it? There are two options. The first I would mention is more of a testing functionality, or last ditch effort. The second is the real workaround.

This does basically what it says. We skip the validation of the certificate for the site. This is great for testing, but I don’t recommend it as a permanent fix as it leaves you open from a certificate perspective and possible hijacking.

When you install Power Query, this registry key will be added and the 992AD44D7DCE298DE17E6F2F56A7B9CAA41DB93F value is added. This is the certificate Thumbprint for the actual cert used for https://de-users-preview.sqlazurelabs.com.

If your proxy inserts a different certificate, then you can use this registry key to list the thumbprint. You can add multiple thumbprints separated by a semi-colon (;).

To illustrate this, lets start up fiddler and see how this shows up now:

To get the thumbprint for the certificate, open up the root item. Go to the Details tab and down to Thumbprint.

If you have the need to enter a thumbprint for your environment and don’t want to do it manually, you could look at using Group Policy Objects or a login script. Check out this blog which talks about the two approaches.

UPDATE: 10/31/2014

With regards to the Tracing Registry key, you no longer have to add the registry key manually. If you update to the latest version of Power Query, go to the Options item on the Power Query Tab. Towards the bottom you will see Diagnostic Options. Just check Enable Power Query Tracing. You can also click on Open traces folder to go right to the location of where they are at. Just remember to turn it off when you are done.