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.
64 Bit machine with 32 bit Office[HKEY_CURRENT_USER\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_CURRENT_USER\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.
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:
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.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttps://twitter.com/awsaxton