I have an Excel 2016 XLSM file with multiple web queries in it. This file is used by multiple people and has been working for about a year now. Everyone's copy of the excel file stopped working recently, and we noticed that everyone's office 2016 had received multiple updates. I have about 60 listed that were installed on the same day.
The issue appears to be in how Excel is trying to negotiate the TLS connection to the web server.
The excel file has six queries that were created by using the Data Tab -> "From Web"
There's one query that is set to refresh when the excel file is opened. We get prompted to select our certificate and after the user selects their certificate they receive the following error: "Unable to open https://xxxxxxxxxxxxx. The Internet site reports that the item you requested could not be found. (HTTP/1.0 404)" The xxxxx is to obfuscate the server name.
If we try and create a new "From Web" request, we'll sometimes get a message that the F5 has refused the connection because "Access policy evaluation is already in progress for your current session." In rare cases it'll work, but then still not work within the excel file.
Clicking the "here" link to create a new session will allow the results to show in the "New Web Query" screen. In some rare cases we can then go and use the existing queries in the excel file, but in most cases we'll continue to receive the prompt for our certificate and then the message saying "Unable to open"
The target web server is not publicly accessible and requires that the user present a digital certificate. Every attempt to refresh the data will reprompt for the user certificate and then fail.
I've placed a copy of the excel file on the web server and if I open the excel file by going to File -> Open -> Browse in Excel and using the URL I'll receive the prompt for the user certificate and the excel file will open and work properly. There are no further prompts for the user's certificate.
If I take a brand new excel XLSX file and do Data Tab -> From Web. Enter the URL, select my cert it will normally show the proper response. I can then click import to add it to the worksheet. It will then reprompt for the user certificate, at that point it's 50/50 on whether it then throws the error or pulls the correct information. If I then save that file, close excel, reopen it and then click Refresh All. It'll prompt for my user certificate and then throw the error message.
I have no way to know if changes were made to the F5, but I know updates were made to all of our Excel installations. When I first started looking into this issue I thought I saw a thread from someone with a similar problem and they mentioned it was after an Office Update, but I've been unable to find that original thread.
I saw another thread about adding the root certificate and I've verified that the correct root certificate is in the "Trusted Root Certification". That same thread also suggested going to Excel > Data > Get Data > Query Options > Global > Security and untick "Enable certificate revocation check", but I don't see such an option in Excel 2016.
I also saw another thread suggesting to set "SchUseStrongCrypto"=dword:00000001 in two registry locations. I tried that with no success.
Not related to this issue, but I can't use "Get & Transform" because of an issue it has with certificate based authentication and TLS. It's been a year, but I remember digging into why that wasn't working and finding a thread where someone explained the bug.
Is anyone else seeing this issue with web queries, or have a suggestion for how to fix it?