question

George-6692 avatar image
0 Votes"
George-6692 asked erinding-msft commented

Excel Web Query failing, likely update related

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"
84946-fromweb.jpg


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.
84948-unable-to-open.jpg


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.
84935-f5.jpg

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?


office-excel-itpro
fromweb.jpg (17.6 KiB)
unable-to-open.jpg (29.2 KiB)
f5.jpg (107.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erinding-msft avatar image
0 Votes"
erinding-msft answered

Hi @George-6692

From the perspective of Office, please check the below.

What's the version of your Office?
If it is not the latest version, please update it to the latest version to check this issue.

Can you insert this URL as a hyperlink to Excel file and open it successfully?

What's the browser are you using?
Please change the default browser.

You could also try to create a new blank .xlsx file, then go to Data>From Web and enter a URL that is out of the "target web server" to see if there is the error.

Besides, is there any third-party security software installed on your computer before this issue occurs?
Please turn it off temporarily to have a check.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

George-6692 avatar image
0 Votes"
George-6692 answered erinding-msft commented

@erinding-msft Thanks for the reply. I appreciate the attempt to help.

What's the version of your Office?

The version of Excel is 16.0.5134.1000
Unfortunately I have no ability to install or remove updates on this laptop, because it's centrally managed.

Can you insert this URL as a hyperlink to Excel file and open it successfully?

If I try one of the URLs as a hyperlink I receive the same error message shown in the second image. "Unable to open...."

What's the browser are you using? Please change the default browser.

The browser would be whatever is built into excel. I believe that's some hybrid of internet explorer. I changed my system default from internet explorer to Edge. Made sure all browsers and Excel were closed and then retried. There was no change in behavior. Still getting the error message.

You could also try to create a new blank .xlsx file, then go to Data>From Web and enter a URL that is out of the "target web server" to see if there is the error.

I had previously done this with the same website, but tried three different website that prompts for the user certificate. They all had the same problem. I tried a website that doesn't require a user certificate and it works correctly.

Besides, is there any third-party security software installed on your computer before this issue occurs? Please turn it off temporarily to have a check.

The computers are centrally managed and locked down pretty tightly, there are multiple third-party security tools running on the computer and I have no ability to turn them off. I checked the logs for the ones I can, and I don't see any of them reporting that they are blocking the connections. Since the connection will work temporarily if I click the Here link to start a new session. Or if I File Open the excel file from the web server I doubt they're the issue. The issue seems to be with the way Excel is handling the initial hand shake with the web server.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @George-6692
Thanks for your reply.
Based on your description, your issue seems to be more related to digital certificate of the target web server.
Since we do not have the same test environment, it's suggested to open a ticket with Microsoft Support to help review your problem and test remotely.
If you have any solutions or updates, you could also share in this thread.
Thanks for your understanding.

0 Votes 0 ·

Thanks. I appreciate the attempt to help.

I understand your point about not having the same environment. I knew it was a long shot posting here. There can't be too many people using excel to pull a table from a web server that requires a client certificate to be presented and that have an F5 proxy in the middle.


0 Votes 0 ·

Thanks for you understanding.
Hope your issue could be resolved soon. :)

0 Votes 0 ·