Answered by:
Can Power Query be updated to use TLS Version 1.2?

Question
-
am using power query in Excel to connect to a site over HTTPS and bring down the data. They recently made this announcement and change;"Effective January 4th 2016 we will only support TLS 1.2. ". This change appears to have broken my excel power query. The error I see in excel now is : "DataSource.Error: The request was aborted: Could not create SSL/TLS secure channel."
I found a site to check TLS version (Hows my ssl dot com) executing this test thru power query indeed reports that Excel is using TLS Version 1.0
Is there a way to upgrade excel to TLS Version to 1.2?
TIA
MarkWednesday, January 6, 2016 4:32 PM
Answers
-
Well the registry thing should still work. Another reference to this approach can be found at
http://stackoverflow.com/questions/28286086/default-securityprotocol-in-net-4-5.
It does require that you have at least .NET 4.5 installed. If you're on Windows 8 or 10, that would be true but if you're on Windows 7 you may still be using .NET 4.0.
- Proposed as answer by Michael Amadi Saturday, January 16, 2016 12:19 PM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, January 18, 2016 6:34 AM
Thursday, January 7, 2016 2:37 PM -
This change has been made in the source code and is already shipping with Power BI Desktop, and I believe it should be in the next release of Power Query. Until then, you can enable TLS 1.2 in the Windows Registry.
To enable TLS 1.2, it is possible to set the SchUseStrongCrypto DWORD value in the following two registry keys to 1, creating them if they don't exist: "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319" and "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319". Those registry keys, however, will enable TLS 1.2 in all installed .NET 4.0 applications on that system.
- Proposed as answer by Curt Hagenlocher Wednesday, January 6, 2016 5:08 PM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, January 18, 2016 6:34 AM
Wednesday, January 6, 2016 5:08 PM
All replies
-
This change has been made in the source code and is already shipping with Power BI Desktop, and I believe it should be in the next release of Power Query. Until then, you can enable TLS 1.2 in the Windows Registry.
To enable TLS 1.2, it is possible to set the SchUseStrongCrypto DWORD value in the following two registry keys to 1, creating them if they don't exist: "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319" and "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319". Those registry keys, however, will enable TLS 1.2 in all installed .NET 4.0 applications on that system.
- Proposed as answer by Curt Hagenlocher Wednesday, January 6, 2016 5:08 PM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, January 18, 2016 6:34 AM
Wednesday, January 6, 2016 5:08 PM -
Did not seem to work, Added both Keys, rebooted still reports TLS 1.0 from Excel.
Downloading Power BI Desktop now.
Thanks
Wednesday, January 6, 2016 5:59 PM -
Neither of these recommendations work. Installed both x64 and then x86 versions of BI Desktop. Excel still reports TLS 1.0.
When is the next release for Power query scheduled?
Mark
Wednesday, January 6, 2016 9:15 PM -
The code in PBI Desktop would not affect the behavior of Excel. If you attempt to connect from inside PBI Desktop instead of Excel, does it work for you?
I'm afraid I don't know when the next release of Power Query happens.
Thursday, January 7, 2016 12:15 AM -
Thanks yes it does work from inside PBI Desktop. I really do not want to rebuild all the queries from the multiple spreadsheets. If there was a way to import the queries and applied steps from excel this might be doable. I don't have time to rebuild each query that's why I was hoping updating Excel would be possible.
Thanks for your help.
Mark
Thursday, January 7, 2016 2:48 AM -
I had hoped installing PBI Desktop would update the system.
I do not see a way to execute a macro in PBI Desktop. In Excel Power Query I have macros that I execute against the data to download images and rename the image and save to our Dropbox.
Unless I am missing something......
Mark
Thursday, January 7, 2016 2:58 AM -
Well the registry thing should still work. Another reference to this approach can be found at
http://stackoverflow.com/questions/28286086/default-securityprotocol-in-net-4-5.
It does require that you have at least .NET 4.5 installed. If you're on Windows 8 or 10, that would be true but if you're on Windows 7 you may still be using .NET 4.0.
- Proposed as answer by Michael Amadi Saturday, January 16, 2016 12:19 PM
- Marked as answer by Ed Price - MSFTMicrosoft employee Monday, January 18, 2016 6:34 AM
Thursday, January 7, 2016 2:37 PM -
Thanks for you help I reset the registry keys and it seems to be working.
Your last link got me straightened out.
Thanks again
Mark
Thursday, January 7, 2016 6:32 PM -
Thanks for this we had an issue using Excel power query with Dynamics 365 for Operations (D365,AX7). When trying to authenticate to the oData feed using an organizational account we were getting the error "The underlying connection was closed: An unexpected error occurred on a send."
A bit of research suggested that this was a TLS issue and some further digging brought me to you answer. Adding the reg keys suggested fixed the issue. Thanks again!
- Edited by David Cornall Wednesday, April 5, 2017 10:33 AM
Wednesday, April 5, 2017 10:23 AM