Basic HTTP Authentication from VBA

Quick examples get it now

Basic Authentication over Http is implemented as an option in the cBrowser class. It took me a while to track this down, but I eventually found out how to and wish to acknowledge http://pastie.org/1192157 for the final story on how to do this

What to download

All the examples contain all the classes needed for them to work and all projects can be found here. 

If you just want the main utility classes used throughout this site, in the Downloads section cDataSet.xlsm includes everything you need including some examples. 

What is Http Authentication?

This is a standard way , supported by all browsers, that a username and password can be supplied to a web site that needs it. The provided credentials are encoded using Base64, but this can be easily decoded, so it is not a particularly secure method – even though it is very common. 

Why do we need it here?

Although most rest Apis use a developer key or some other method, some sites require basic http authentication. In order for the Rest to Excel library to be able to access these sites, I had to build basic authentication into the cBrowser class. 

Specify userName and password

The cRest class now has a couple of addition arguments to the .init() method that allow username and password to specified. If these are present, then the rest session will commence with an authorization attempt. Here’s an example calling a library entry that needs a username and password.

Public Sub testneedsPass()
    Dim cr As cRest
    Set cr = restQuery("needsPass", "needsPass", _
    , , , , , True, , , True, , "someuser", "somepassword")
                 
End Sub

The code

Actually, although this was hard to track down, the code is rather trivial – the section in blue is all that was needed to enable it. In summary, and encoded version of the user and password combination is inserted into the http request header to accomplish the authentication.

ublic Function httpGET(fn As String, _
        Optional authUser As String = vbNullString, _
        Optional authPass As String = vbNullString) As String
    pHtml = fn
    Dim oHttp As Object
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    Call oHttp.Open("GET", pHtml, False)
    If (authUser <> vbNullString) Then
    ' need to do basic authentication
    ' acknowledgement to http://pastie.org/1192157
        oHttp.SetRequestHeader "Content-Type", "application/json"
        oHttp.SetRequestHeader "Accept", "application/json"
        oHttp.SetRequestHeader "Authorization", "Basic " + _
            Base64Encode(authUser + ":" + authPass)
    End If
    Call oHttp.Send("")
    httpGET = oHttp.ResponseText
    Set oHttp = Nothing
End Function

Base64 encoding

I also needed a Base64 encoding algorithm which I found from the same source

Function Base64Encode(sText)
    Dim oXML, oNode
    Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
    Set oNode = oXML.createElement("base64")
    oNode.DataType = "bin.base64"
    oNode.nodeTypedValue = Stream_StringToBinary(sText)
    Base64Encode = oNode.Text
    Set oNode = Nothing
    Set oXML = Nothing
End Function


'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.WriteText Text

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeBinary

  'Ignore first two bytes - sign of
  BinaryStream.Position = 0

  'Open the stream And get binary data from the object
  Stream_StringToBinary = BinaryStream.Read

  Set BinaryStream = Nothing
End Function

'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeBinary

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.Write Binary

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And get binary data from the object
  Stream_BinaryToString = BinaryStream.ReadText
  Set BinaryStream = Nothing
End Function

Take a look at Rest to Excel library  for more on this topic