2

I want to send a POST request to a Java servlet with VBA using the Kerberos ticket from the Windows Log In for authentication and then retrieve a JSON response from the servlet. Can I achieve this without using an InternetExplorer object, e.g. using WinHttpRequest?

Sebastian
  • 83
  • 1
  • 2
  • 6

1 Answers1

4

WinHttpRequest is not an IE object. The above described usecase works perfectly as long as you set up to relax the security permissions. I have implemented a proof of concept last year: Excel, VBA, REST, Tomcat, SPNEGO authentication.

Stub code:

Dim winHttp As Object
Set winHttp = CreateObject("WinHttp.WinHttpRequest.5.1")

winHttp.SetAutoLogonPolicy (0)
winHttp.Open "GET", "http://..."
winHttp.send

Dim success As Boolean
success = winHttp.waitForResponse(5)
If Not success Then
    Debug.Print "DOWNLOAD FAILED!"
    Exit Sub
End If

Dim responseText As String
responseText = winHttp.responseText
Michael-O
  • 18,123
  • 6
  • 55
  • 121
  • Thanks for the reply! Can the relaxing of the security permissions be done in VBA? Can you please post some of the relevant VBA code from your proof of concept? – Sebastian Jun 17 '14 at 11:25
  • Thanks again! I won't have the opportunity to test your code until Monday, I'll let you know whether it works by then. – Sebastian Jun 18 '14 at 12:00
  • @user1829001, I'd be glad to hear your results. – Michael-O Jun 18 '14 at 12:46
  • It appears the code doesn't work in my case. I get a response from the server which says "Your browser does not support SPNEGO/Kerberos authentication. Alternative authentication required.". – Sebastian Jun 23 '14 at 18:32
  • @user1829001, that should not be the case. You can and must do the following, inspect the traffic with Fiddler and Wireshark and see wether the framework does send a ticket at all. That code works here very good. Does it work with another client? I wouldn't share not working code. – Michael-O Jun 23 '14 at 20:21
  • It works when I use IE respectively the InternetExplorer.Navigate() function in VBA. I'll check the traffic as you suggested. – Sebastian Jun 24 '14 at 08:19
  • Fiddler tells me that IE sends a Cookie/Login to the server consisting of items "CTSESSION", "ssoLang", "TCSESSIONID" and "wt_eid". I guess I need to first retrieve this data in VBA and then send it as a cookie as well. Any ideas how to do this? – Sebastian Jun 24 '14 at 09:34
  • @user1829001, that is not what I wanted to know. At first request from VBA, the server has to challange the client with `WWW-Authenticate: Negotiate`. Please recheck. – Michael-O Jun 24 '14 at 10:24
  • 1
    I got the solution: actually it works exactly like in your code, the only missing was winHttp.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" – Sebastian Jun 24 '14 at 10:24
  • Has anyone managed to get this to work with Kerberos? I can't see how to get the Kerberos ticket using the example code mentioned above? – jcwhall Feb 07 '20 at 13:32
  • @jcwhall There is nothing to do. The underlying C/C++ code will do for you by default. – Michael-O Feb 14 '20 at 14:17