2

I'd be extremely grateful for any kind of help that may help me resolving the problem.

From Excel VBA code I need to download & parse CSV file from HTTPS site https://redmine.itransition.com/. I try to use WinHTTP to get the file. However, I can't understand why authentication does not work. Here is the piece of related code:

TargetURL = "https://redmine.itransition.com/projects/pmct/time_entries.csv"
Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
HTTPReq.Option(4) = 13056 ' WinHttpRequestOption_SslErrorIgnoreFlags 13056: ignore all err, 0: accept no err
HTTPReq.Open "GET", TargetURL, False
HTTPReq.SetCredentials "UN", "PW", 0
HTTPReq.send

returns the following response (only certain strings are listed):

Content-Type: text/html; charset=utf-8
Status: 406
X-Runtime: 5

However, if I send "Cookie" string from Firefox cookie after successful manual authentication using

HTTPReq.setRequestHeader "Cookie", SetCookieString
HTTPReq.send

I easily get the expected file. Of course I'm not happy with such solution, and want to perform true WinHTTP authentication. However, I can't understand what's wrong or what I miss in my code. Most likely I have to use .SetClientCertificate method, but this is unclear for me - which cert is required?

Or, being more general: which WinHTTP methods or functions I should use for debugging to find out which step is blocking / incorrect and prevents me from correct authentication? I spent 2 weeks seeking through MSDN and various resources, but still have no solution.

Thanks in advance for your suggestions!

amartine
  • 819
  • 1
  • 10
  • 24
Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • Perhaps that may help: I need to authenticate Redmine instance (see http://www.redmine.org/ for more), ver. 1.0.1 (quite old). – Peter L. Jan 06 '13 at 18:10
  • The 406 HTTP status code doesn't relate to authentication - you should get a 401 or 407 code if authentication was needed. 406 is - "406 Not Acceptable: The resource identified by the request is only capable of generating response entities which have content characteristics not acceptable according to the accept headers sent in the request" - see http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html – barrowc Jan 07 '13 at 01:32
  • @barrowc thanks for your highlight! Indeed 406 does not relate to auth at all - under my context it means just that I receive response different from 200 / OK, which I get when use cookie string from browser. And one more thanx for the useful link! – Peter L. Jan 08 '13 at 13:08

3 Answers3

6

The above @Alex K. response was exactly what I was looking for soooo long! With the help of Firebug and MSDN I finished with 3 requests:

  • GET request to collect authenticity_token data from login page using RegEx
  • POST request to authenticate & collect required Cookie string from response
  • GET request to finally obtain my beloved CSV

The following piece of code which is working as expected:

Set RegX_AuthToken = CreateObject("VBScript.RegExp")
' Below Pattern w/o double-quotes encoded: (?:input name="authenticity_token" type="hidden" value=")(.*)(?:")
RegX_AuthToken.Pattern = "(?:input name=" & Chr(34) & "authenticity_token" & Chr(34) & " type=" & Chr(34) & "hidden" & Chr(34) & " value=" & Chr(34) & ")(.*)(?:" & Chr(34) & ")"
RegX_AuthToken.IgnoreCase = True
RegX_AuthToken.Global = True

TargetURL = "https://redmine.itransition.com/login"

Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
HTTPReq.Open "GET", TargetURL, False
HTTPReq.Send

Set Token_Match = RegX_AuthToken.Execute(HTTPReq.ResponseText)
AuthToken = Token_Match.Item(0).SubMatches.Item(0)

PostData = "authenticity_token=" & AuthToken & "&back_url=https://redmine.itransition.com/" & "&username=" & UN & "&password=" & PW & "&login=Login »"

HTTPReq.Open "POST", TargetURL, False
HTTPReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
HTTPReq.Send (PostData)

SetCookieString = HTTPReq.GetResponseHeader("Set-Cookie")

TargetURL = "https://redmine.itransition.com/projects/pmct/time_entries.csv"
HTTPReq.Open "GET", TargetURL, False
HTTPReq.setRequestHeader "Cookie", SetCookieString
HTTPReq.Send

The following URL was helpful in building POST request: http://tkang.blogspot.com/2010/09/sending-http-post-request-with-vba.html

You need to load that page with no credentials, grab what looks like the volatile field authenticity_token from the generated form & post that along with username & password to /login.

Alex K. - thanks again for the brilliant suggestion! (:

Peter L.
  • 7,276
  • 5
  • 34
  • 53
3

The logon at https://redmine.itransition.com/ is just an HTML form that posts a username & password to a script at /login.

This is not compatible with SetCredentials which is designed for server based authentication schemes like basic/digest/ntlm.

You need to load that page with no credentials, grab what looks like the volatile field authenticity_token from the generated form & post that along with username & password to /login.

If its a session based system it will response with the set-cookie header + data you need to use in subsequent request.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
0

And one more thing (in addition to the above solution) one should be aware of in case of using POST requests similar to the above code: for some obscure reason I still got once in 4-5 times (or even more) the hated 406 response from the website, which means in my case auth is NOT complete. After hours of step-by-step debugging I happily caught the cause: auth token value may have + signs, and from analyzing an arrow of several dozens auth tokens / response codes I discovered that +-containing tokens exactly match 406 codes.

The solution became pretty obvious: safely URL-encode +es for the PostData. With the help of http://www.blooberry.com/indexdot/html/topics/urlencoding.htm I finally came up to the following:

PostData = "authenticity_token=" & Replace(AuthToken, "+", "%2B", vbTextCompare) & _
    "&back_url=https://redmine.itransition.com/projects/" & Trim(RedmineProject) & _
    "/time_entries" & "&username=" & UN & "&password=" & PW & "&login=Login »"

+es are replaced by %2Bs, and this was that - no more 406s!)

The other special chars do not matter in my case, but the lesson was learned. Hope this will save several hours of life for someone else!

Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • i know this is an old post, but need your help in downloading images from a secure site **https://secure.brandbank.com/login.aspx?cookieCheck=true**. I have my login username and password, and tried various means to download it. I can manually download the image, after logging, from e.g. **https://productlibrary.brandbank.com/image/gallerylarge/3520198** but dont know how to download it using the example you shared on authenticity_token. How do i write the regex for it and also find its authenticity token? FYI, the **3520198** is the name of the image. – sifar Mar 18 '17 at 18:23
  • how did you figure out the right `PostData` and `AuthToken.Pattern`, respectively? I trying to download documents from a secure site but it does not expose anything in the URL string. All what I get when I want to download is [401](https://msdn.microsoft.com/en-us/library/ms767625(v=vs.85).aspx) + `authorisation token missing`. I put the `ResponseText` and `GetAllResponseHeaders()` of the login page on [pastebin](https://pastebin.com/jhhdL4UC). I cannot find anything related to authorisation token. – Stefan Feb 02 '18 at 16:34