Using an MVC API with Windows Authentication from VBA

Question

I have developed a MVC Application, which, for the purpose of this question only has one controller:

Public Function GetValue()
    Return User.Identity.Name
End Function

The application is to be used on an Intranet network, therefore, I have set it to 'Windows Authentication'

The aim is to query this application, through VBA.

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
With objHTTP
    .Open "GET", URL, False
    .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    .setRequestHeader "Content-type", "application/json"
    .setRequestHeader "data-type", "json"
    .send
    .WaitForResponse
    sResult = .ResponseText
End With
Debug.Print (sResult)

If I run the application locally (ie. debug on the computer that is running Excel), it works through Chrome (accessing localhost:xxxxx/api/name returns an xml file with my ActiveDirectory username).

The VBA routine works fine as well, and the Output window displays the XML I get in Chrome.

Now, if I publish the project to the IIS server, it still works through Chrome (accessing myserver/api/name returns an xml file with my ActiveDirectory username). However, when I run the VBA module, it returns an Error 401:

Error:401 - Unauthorized: Access is denied due to invalid credentials.

The fact that it works in browsers leads me to believe that server-side configuration is OK, and that I need to tweak something in my VBA.

I have to admit that I am a bit clueless at this point... Thank you for any leads you may give me :)

You should be using the WinHTTPRequest.5.1 library, this will allow you to tweak authentication settings allowing you to log on — SWa
– SWa, Commented Apr 3, 2017 at 10:10

Maxime · Accepted Answer · 2017-04-03 11:32:23Z

5

Thanks to @SWa comment, I solved this with a minor tweak to the function: Switching to WinHttpRequest and using setAutoLogonPolicy 0

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
With objHTTP
    .Open "GET", URL, False
    .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    .setRequestHeader "Content-type", "application/json"
    .setRequestHeader "data-type", "json"
    .setAutoLogonPolicy 0
    .send
    .WaitForResponse
    sResult = .ResponseText
End With
Debug.Print (sResult)

answered Apr 3, 2017 at 11:32

Maxime

1,3452 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

some_coder Over a year ago

This works like a charm! Every solution i found so far used MSXML2.ServerXMLHTTP with the Windows-User/Password in the Open method as parameter (which is just not user friendly because you have to ask the user to type it in or you have to store it in a text file). But this magical setAutoLogonPolicy(0) just solved that problem for me.

kevinpo Over a year ago

After doing this, I had to modify how I was referencing the response. Previously I had done: Set objXmlDoc = Server.CreateObject("MSXML.DOMDocument") and objXmlDoc.load(objHTTP.responseXML), but after this change I had to use objXmlDoc.loadXML(objHTTP.ResponseText)

Collectives™ on Stack Overflow

Using an MVC API with Windows Authentication from VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related