3

I have written a VBA plugin for Excel, which provides login/logout functionality to a server.

The way I have implemented it is to send a request to a URL on the server, which checks if the user has a session, if it does not, the server sends a 401 on the header response (unauthorised). The 401 response makes Excel automatically pop up the 'windows style' login modal (this comes for free with Excel I guess). I enter my credentials, and the server authenticates the user, and a session is created.

My problem is now when I try to logout. The server destroys the session as expected. If the user now clicks login again, Excel sends a HEAD request to the server, the server responds with a 401, and Excel then automatically sends the same username and password credentials that were entered by the user before. The user is now logged on again. Just to be clear - this is NOT server side caching, this is Excel resending login credentials, using Basic Authentication in the HTTP Header, after receiving a 401 response.

This is not the expected behaviour, I would not like Excel to store a copy of the credentials, and reuse them time after time. If the user logs out, I would like this to be permanent, until they decide to log in again at which time they should re-enter their credentials.

The problem I have is telling Excel/Windows/VBA? to clear whatever it is keeping, wherever it is keeping it. My problem is, I don't really know what the choreography is on the Excel/VBA side, it's functionality that just seemed to come for free, so I do not know where this is stored.

For what it's worth, here is my VBA

 Dim req As MSXML2.XMLHTTP60
 Set req = New MSXML2.XMLHTTP60
 req.Open "GET", url, False
 req.send
Phil
  • 1,877
  • 4
  • 26
  • 47
Agent96
  • 405
  • 1
  • 3
  • 16
  • There are no credentials in that request code? – SierraOscar Aug 26 '15 at 09:19
  • No. Excel automatically puts them in the HTTP Authentication header after the user fills in the details on the 'Windows Security' modal that pops up. This modal comes 'for free' when Excel receives the 401 response. There is a 'Remember my credentials' checkbox on this modal, which I do not check. – Agent96 Aug 26 '15 at 09:22
  • Ah okay, I get what you mean now. I don't think this is actually Excel - I think this is a system event, not an application event so I don't think you're going to find an answer through VBA. AFAIK Excel doesn't have the facility to store credentials, this is handled by the OS. – SierraOscar Aug 26 '15 at 09:27
  • Yeah, I wasn't sure it was Excel either, more of a Windows thing. The credentials are lost if I close Excel and reopen. I was hoping I could somehow clear this manually using VBA. It just seems ridiculous to not provide the user with this windows authentication feature, without providing the ability to clear authentication credentials. – Agent96 Aug 26 '15 at 09:57
  • I experienced a similar thing not too long ago using SharePoint and VBA. There may be a windows API that could be used or perhaps use windows command line via VBA? Sorry I can't be of more help! :) – SierraOscar Aug 26 '15 at 10:02

1 Answers1

0

From this answer:

myURL = "http://my.domain.com/myscript.cgi"
Dim oHttp As New MSXML2.XMLHTTP
oHttp.Open "POST", myURL, False
oHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded'"
oHttp.setRequestHeader(“Cache-Control”, “no-cache”);
oHttp.setRequestHeader(“Pragma”, “no-cache”);
oHttp.setRequestHeader(“If-Modified-Since”, “Sat, 1 Jan 2000 00:00:00 GMT”);
oHttp.setRequestHeader "Authorization","Basic " & Base64EncodedUsernamePassword
oHttp.send "PostArg1=PostArg1Value"
Result = oHttp.responseText
Community
  • 1
  • 1
Roxton
  • 161
  • 7