It's all about the answers!

Ask a question

How login to Rational Quality Manager from Microsoft Excel VBA application?


Dmitry A. Lesin (24826996) | asked Sep 18 '17, 3:19 p.m.

Hello!
I need to import some data from IBM Rational Quality Manager (RQM) v6.0.4 to Microsoft Excel. I want to use Reportable API for that. But I can not login to RQM from Excel. I have spent a lot of time, and there's no success!
If in detail, the problem is next. There's a small VBA code:

Public Function jazzLogin_UNIT()
    Call JazzLogin(getFullServerURI, "jts", "jts")
End Function

Public Function JazzLogin(url, userid, password) As Object

    ' User data
    Dim userdata As String
    userdata = "j_username=" & userid & "&" & "j_password=" & password
   
    ' URIs
    Dim requestURI1 As String, requestURI2 As String
    requestURI1 = "https://jazz.server.com:9443/qm/authenticated/identity"
    requestURI2 = "https://jazz.server.com:9443/qm/authenticated/j_security_check"
    requestURI2 = requestURI2 & "?" & userdata

    ' HTTP service object
    Dim httpService1 As New WinHttp.WinHttpRequest, httpService2 As New WinHttp.WinHttpRequest

    ' Check that form-based authetication is used
    httpService1.Open "GET", requestURI1
    httpService1.Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 13056 ' insecure
    httpService1.send
    Debug.Print "[GET]: " & requestURI1
    Debug.Print "[HEADERS 1] " & httpService1.GetAllResponseHeaders()
    Debug.Print "[STATUS 1] " & httpService1.statusText
   
    httpService2.Open "POST", requestURI2
    httpService2.Option(WinHttpRequestOption_SslErrorIgnoreFlags) = 13056
    httpService2.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=utf-8"
    httpService2.send
    Debug.Print "[POST]: " & requestURI2
    Debug.Print "[HEADERS 2] " & httpService2.GetAllResponseHeaders()
    Debug.Print "[STATUS 2] " & httpService2.statusText

    Set JazzLogin = httpService2


End Function
If I execute "jazzLogin_UNIT" procedure then I have next in the console:

[GET]: https://jazz.server.com:9443/qm/authenticated/identity
[HEADERS 1] Cache-Control: no-cache="set-cookie, set-cookie2"
Date: Mon, 18 Sep 2017 19:04:33 GMT
Content-Length: 1982
Content-Type: text/html; charset=UTF-8
Content-Language: en-US
Expires: Thu, 01 Dec 1994 16:00:00 GMT
Set-Cookie: JazzFormAuth=Form; Path=/qm; Secure
X-Powered-By: Servlet/3.0
X-com-ibm-team-repository-web-auth-msg: authrequired


[STATUS 1] OK
[POST]: https://jazz.server.com:9443/qm/authenticated/j_security_check?j_username=jts&j_password=jts
[HEADERS 2] Connection: Close
Date: Mon, 18 Sep 2017 19:04:33 GMT
Content-Length: 757
Content-Type: text/html;UTF-8
Content-Language: en-US
X-Powered-By: Servlet/3.0


[STATUS 2] Bad Request

I understand that VBA isn't very popular amonst the developers who works with Java applications but the code is very primitive. May be somebody could give me an advice where I'm wrong?
It must be mentioned that there's no problem with credentials. I can login with jts/jts both in Jazz authetication form and in curl utility!
Thank you very much in advance!

2 answers



permanent link
Donald Nong (14.5k614) | answered Sep 19 '17, 2:19 a.m.

You use two separate WinHttp.WinHttpRequest objects for the two requests. How do you reuse the cookies when you send the second request? If you have experience with using cURL for this exercise, you should notice that the cookies.txt file is used to store and retrieve the cookies. I suspect the reason you get the "bad request" error is missing cookies (in particular JSESSIONID).


Comments
Dmitry A. Lesin commented Sep 19 '17, 11:02 a.m. | edited Sep 19 '17, 11:12 a.m.

I changed all to a single http object but the result is the same. But I believe you are right that the problem is in the manner how WinHttp.WinHttpRequest transfers the session data.
By the way, I detected the same behavior if I use RESTClient or Poster Firefox add-ings to send the requests manually. Similarly, With these tools, I can not login to RQM getting the same errors.
Do you know if there's a way to pass form-based authentication from Poster or similar tools but without preliminary connect to RQM in a browser?


Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.