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.

image

Problem Signing In
A 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 In
The 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.

64 Bit machine with 32 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel]
"TracingEnabled"=dword:00000001

32 Bit machine with 32 bit Office or 64 Bit machine with 64 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel]
"TracingEnabled"=dword:00000001

The trace location will be in your user folder under the following path:

C:\Users\<user>\AppData\Local\Microsoft\Power Query\Traces

There can be a lot of data in this file, but we want to look for something that shows an Error.  Like the following:

DataMashup.Trace Error: 24579 : {"Start":"2014-07-29T19:14:38.5365720Z","Action":"ClientRequest/TryExecute","Exception":"Exception:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.\r\nStackTrace:\n   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)\r\n   at Microsoft.Mashup.Host.Document.Client.ClientRequest.TryExecute(Response& response, RequestException& requestException)\r\n\r\nInnerException\r\nException:\r\nExceptionType: 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.\r\nStackTrace:\n   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.PooledStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)\r\n\r\n\r\n\r\n\r\n","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Host.Models.RequestException, Microsoft.Mashup.Client.Models, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.\r\nStackTrace:\n\r\n\r\nInnerException\r\nException:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.\r\nStackTrace:\n   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)\r\n   at Microsoft.Mashup.Host.Document.Client.ClientRequest.TryExecute(Response& response, RequestException& requestException)\r\n\r\nInnerException\r\nException:\r\nExceptionType: 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.\r\nStackTrace:\n   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.PooledStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)\r\n\r\n\r\n\r\n\r\n\r\n\r\n","RequestId":"c1572bb1-5961-4a20-91d3-1b1a80014534","Result":false,"ProductVersion":"2.13.3688.241 (ReleaseClient_V2)","Process":"excel","Pid":14452,"Tid":1,"Duration":"00:00:00.2502943"}

And then followed by:

DataMashup.Trace Warning: 24579 : {"Start":"2014-07-29T19:14:31.4023956Z","Action":"LoginManager/LogIn","SignInUrl":"https://login.windows.net/common/oauth2/authorize?client_id=a672d62c-fc7b-4e81-a576-e60dc46e951d&response_type=code&redirect_uri=https%3a%2f%2fde-users-preview.sqlazurelabs.com%2faccount%2freply%2f&resource=0000001b-0000-0000-c000-000000000000","ReplyUrl":"https://de-users-preview.sqlazurelabs.com/account/reply/","ResultUrl":"https://de-users-preview.sqlazurelabs.com/account/reply/?code=AAABAAAAvPM1KaPlrEqdFSBzjqfTGBrmTO5hXMPoE-zL20KoP-0DBaLfaVGG08Eb2_Po5vozpzA17zQJZsXTYLJQ0phab_G-d202MPT-xjFJhx3MuxE8tNol1t15zTPY4611oM0oPR_AqheDZ4GiHrKGDYE3bb6thTH-Qb6KJG7yUYtXe1YREA7RC2gzSMSWhYllipEe1FCgEjW2tr_CGGeoggsNPRxzv7-8JNIyge9NvBdrxwhJwiLVfxMqBUECf6SHAbYa6T0EAve4kM5Z1smvX10yEFiBzPS-SpPQzky-OMrIN3UT1eZLzT-ILFJNhahMFITb126am_m82wmDLSC-uzyBlPNlZAfZ2BypVEOx9AFIrIrhm3s-5UdAYk2Imt7vs_0hZkUoIAa_pcw7vnfS05hQc23d4rUl0T56C6timihxS88LDtydW0gBoU88QI1AU3wVHnmdmq8Esegk6z9nIcWNUwI-r1YeGTgOQE4pusRIg_KDO8BPme9MozSXXuz4EnpFdJSxuQYBaPBdqCU-LnmlkJhmhYt48sgjA_9PGM8JE1OhW704BbRdO64LK5DWhLx1IAA&session_state=03b7c13c-cf56-4cdd-8cb0-a10dd6189d8b","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Host.Document.UnauthorizedException, Microsoft.Mashup.Document, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: A 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.\r\nStackTrace:\n   at Microsoft.Mashup.Host.Document.Client.ClientAccessManager.Authorize(String accesstoken, Guid tenantId)\r\n   at Microsoft.Mashup.Host.Document.Client.ClientAccessManager.GetClientAccessOnSignIn(Uri resultUrl)\r\n   at Microsoft.Mashup.Client.ClientShared.LoginManager.LogIn(IWin32Window owner, HostContext hostContext)\r\n\r\nInnerException\r\nException:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.\r\nStackTrace:\n   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)\r\n   at Microsoft.Mashup.Host.Document.Client.ClientRequest.TryExecute(Response& response, RequestException& requestException)\r\n\r\nInnerException\r\nException:\r\nExceptionType: 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.\r\nStackTrace:\n   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.PooledStream.EndWrite(IAsyncResult asyncResult)\r\n   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)\r\n\r\n\r\n\r\n\r\n\r\n\r\n","ProductVersion":"2.13.3688.241 (ReleaseClient_V2)","Process":"excel","Pid":14452,"Tid":1,"Duration":"00:00:12.4698896"}

The underlying Exception from these two entries is the following:

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:

DataMashup.Trace Information: 24579 : {"Start":"2014-07-29T19:14:38.5365282Z","Action":"ClientRequest/TryGetClientResponse","RequestUri":"https://72f988bf-86f1-41af-91ab-2d7cd011db47.de-users-preview.sqlazurelabs.com/Account/Authorize?api-version=2014-03","Result":false,"ProductVersion":"2.13.3688.241 (ReleaseClient_V2)","Process":"excel","Pid":14452,"Tid":1,"Duration":"00:00:00.2504725"}

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.

image

image

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.

ServiceCertificateValidationDisabled

64 Bit machine with 32 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel]
ServiceCertificateValidationDisabled := 1

32 Bit machine with 32 bit Office or 64 Bit machine with 64 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel]
ServiceCertificateValidationDisabled := 1

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.

ServiceCertificateThumbprints

64 Bit machine with 32 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel]
ServiceCertificateThumbprints := 992AD44D7DCE298DE17E6F2F56A7B9CAA41DB93F

32 Bit machine with 32 bit Office or 64 Bit machine with 64 bit Office
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power Query for Excel]
ServiceCertificateThumbprints := 992AD44D7DCE298DE17E6F2F56A7B9CAA41DB93F

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:

image

image

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

image

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.

 

 

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton