Have a excel spreadsheet which loads VSP photometry using the api json route for an individual star - this has worked fine for the last 2 years but today it is returning access denied (code 80070005) - the same request entered into firefox web browser returns the details as expected. Any thoughts or ideas greatly appreciated.
I am using excel 2007 (yes I know but it works - or at least it did up to now!) under Windows 10 and the vba code looks like
Dim hReq As Object: Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", sUrl, False
.Send
End With
Dim sJson As String: sJson = hReq.ResponseText
the failure occurs at .Send
packet capture shows the firefox having no problem but the excel dialog shows an encrypt alert before the aavso server closing the connection.
firefox appears to be using TLS 1.3 and excel TLS 1.2 - if that makes any sense. I am guessing that the new server is forcing this and it might be causing the failure?
Regards
George
If the url you are using looks like: http://www.aavso.org/apps/vsp/api/chart/2164EAF/?format=xml
you should switch to https://app.aavso.org/vsp/api/chart/2164EAF/?format=json note the subtle change
The former is automatically mapped to the later, but your VBA code may not be handling the redirect gracefully.
George
SGEO
Hello George
Tried a number of different options and pasting the url into firefox web browser works fine.
The packet capture shows that the excel request is using TLS 1.2 which is the legacy protocol - not surprising with the age of the underlying (BUT STABLE!) software
the firefox uses 1.3 TLS - been a while since I work with this level but I think each is negotiated at the start of conversation and the API server might be rejecting TLS 1.2 which is still perfectly valid?
Regards
George
George,
I checked with our tech team and he responded:
TLS 1.2 isn’t legacy – TLS 1.0 and 1.1 are.
TLS 1.2 is the current, TLS 1.3 is the future spec that was adopted not toooo long ago.
In any case the server supports all 4 of the protocols: https://www.ssllabs.com/ssltest/analyze.html?d=www.aavso.org
So I don’t think that’s the problem. If they’re getting ‘access denied’ that means they’re able to at least establish a connection.
Can you send a copy of the code or spreadsheet to SGEO@GASilvis.net. We'll look into this further.
Thanks to George who spotted the url for the api has changed and although the server redirects to the new url for the api requests this might not work with older code.
in my case the url had to be changed from
https://www.aavso.org/apps/vsp/api/chart/?format=json&.....
to
https://app.aavso.org/vsp/api/chart/?format=json&...
And it now works
Thank you again George Silvas for your fast and brilliant support
George F