5

I want to interface with a REST API of a website (in EXCEL VBA) that requires authentication , using either a digital certificate (.PFX file) (NTLM authentication), or using the Windows Domain authentication (Kerberos & Negotiate Authentication). The latter is preferred, but I don't know how to do either, and I was hoping someone could share code on how to do either of these authentication. I can't use other methods of authentication (basic for example) because it's not supported.

Currently I am using VBA-Web (github:VBA-tools:VBA-Web), and I have managed to interface with the api, but in a very weird way. I tracked the network calls from Chrome to the website, and noticed that it adds the cookies to the header of every call. Hence in my requests, I add:

webrequest.SetHeader "Cookie", "server.com.au=35558896564.55846.54545; SDASESSION=AQISFCwMasdffczd6afASFVHfgfgsdf%2BG35FsE%3D%40AAJTSwAJSDFSDTkMw%3D%3D%23; amlbcookie=07; JSESSIONID=000f:19pcqj2d1; server2.com.au=484566584.454584.41545"

However, this is obviously annoying and not user-friendly - as I have to find the cookies every time I want to use my excel vba code.

Furthermore, after tracking the network calls of the authentication in Chrome, I noticed that it goes through various redirections, collecting all the cookies as it gets redirected. During one particular call, it does a "negotiate" authentication.

VBA-Web seems to be based off WinHTTP, so I have looked around and I was really lost in how to interface with WinHTTP (or WinINet, etc.) to do these authentication. I've looked at the setCredentials method Link, but I'm not sure what the username and password would be for NTLM or Negotiate?

Alternatively, I've tried to use the digital certificate instead, but since I have a PFX file, I have no idea how to use it directly. I've seen Link but I'm not entirely sure where my certificate is stored after installing the PFX. I choose Personal during the installation, so does that mean it's in LOCAL_MACHINE\Personal ? More importantly I don't know what the subject name is.

The Microsoft documentation is either in C++ or JScript, so that doesn't help me either.

So If I'm able to authenticate and get the cookies, then the rest should be fine. How would I do this with any of the two methods of authentication, in Excel, using VBA?

P.S I hope my question is clear. Feel free to ask for clarification. All the answers I found reverts to Basic Authentication but I can't do that. I would share with you the website I'm trying to interface with, but it's on the intranet.

Goldtrallion
  • 51
  • 1
  • 4

1 Answers1

1

You can definitely do NTLM authentication from VBA using WinHttp.WinHttpRequest -- you have to add Microsoft WinHTTP Services as a reference to your VBA project.

it would be exactly the same as using the powershell script, except that maybe the credential cache doesn't exist. No problem, just use SetAutoLogonPolicy, SetCredentials, or SetClientCertificate (CURRENT_USER or LOCAL_MACHINE are the only valid locations!) -- See this -- e.g. SetClientCertificate("CURRENT_USER\Personal")

Dim auth As New WinHttp.WinHttpRequest

With auth   
.Open "GET", "https://xxx.yyy", False
.setRequestHeader "Accept", "application/json" ' this is only an example, you probably don't need to set this header
.setRequestHeader <Whatever Header You Want>, <Header Value>
.SetAutoLogonPolicy(AutoLogonPolicy_Always) ' WARNING: Only use this if you trust the host you're trying to login to
.SetClientCertificate("CURRENT_USER\Personal") ' This will use the first certificate in the "CURRENT_USER\Personal" store, this might just work as is if you have a managed domain account.
.send
cookie = .getResponseHeader("Set-Cookie")
End With

Some hosts seem to redirect you and you have to do the authentication there, no problem, you just manually(!!!) follow the redirects. Make sure to disable automatic redirects.

Dim auth As New WinHttp.WinHttpRequest

With auth   
    .Open "GET", "https://xxx.yyy", False
    .setRequestHeader "Accept", "application/json" ' this is only an example, you probably don't need to set this header
    .setRequestHeader <Whatever Header You Want>, <Header Value>
    .SetAutoLogonPolicy(AutoLogonPolicy_Always) ' WARNING: Only use this if you trust the host you're trying to login to
    .SetClientCertificate("CURRENT_USER\Personal") ' This will use the first certificate in the "CURRENT_USER\Personal" store, this might just work as is if you have a managed domain account.
    .Option(WinHttpRequestOption_EnableRedirects) = False
    .send

    While .Status = "302" ' got redirected
        redirectLocation = .getResponseHeader("Location")
        ' Here you play the same game above until you get a proper session cookie
    Wend

End With

Note that after every redirect, you might want to save any response cookie that you get, because in some cases subsequent http requests have to use that cookie.

The easiest thing to do to check how a particular host authenticates with ntlm is to just use developer tools in your browser and monitor the network, you'll see all the requests that you need.

P.S. In most cases using a certificate will not produce a cookie, so just follow the same procedure and you don't need to save a cookie if you have a valid certificate.

aldente
  • 125
  • 7