How to pass authentication credentials in VBA

Question

I'm trying to write a VBA macro that would pass my credentails to an address and fetch some content (REST API for JIRA), but I'm having some difficulties converting my code from java to VBA. Currently, this is my java code:

        String username = "myUser";
        String password = "myPassword";

        String authString = username + ":" + password;
        byte[] authEncBytes = Base64.encodeBase64(authString.getBytes());
        String authStringEnc = new String(authEncBytes);
        System.out.println("Base64 encoded auth string: " + authStringEnc);

        URL url = new URL(address);
        URLConnection urlConnection = url.openConnection();
        urlConnection.setRequestProperty("Authorization", "Basic "
                + authStringEnc);
        InputStream is = urlConnection.getInputStream();
        InputStreamReader isr = new InputStreamReader(is);

And I'm trying to convert this to VBA, I'm not entirely sure how to handle it, or if there's some library that would facilitate this.

Answer 1

For Basic Authentication you can simply:

Dim response As String

With CreateObject("Microsoft.XMLHTTP")
  .Open "GET", address, false, username, password
  .Send
  response = .responseText
End With

Msgbox response

Answer 2

Use the "Authorization" request header. The “Authorization” request header requires an encrypted string for username and password.

.setRequestHeader "Authorization", "Basic " & EncodeBase64

So here JiraService being an XMLHTTP object, something like this will authenticate, where EncodeBase64 is a function which returns encrypted string.

Public Function isAuthorized() As Boolean
With JiraService
    .Open "POST", sURL & "/rest/api/2/issue/", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Authorization", "Basic " & EncodeBase64
    .send ""
    If .Status <> 401 Then
        isAuthorized = True
    Else
        isAuthorized = False
    End If
End With

Set JiraService = Nothing
End Function

You can check out a complete VBA example here

Answer 3

Sub test()
 Dim user As String
 Dim pwd As String
 Dim path As String
 user = ""
 pwd = ""
 path = ""
 Debug.Print httpGET(path, user, pwd)
End Sub

Public 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 " + _
            EncodeBase64(authUser + ":" + authPass)
    End If
    Call oHttp.Send("")
    httpGET = oHttp.ResponseText
    Set oHttp = Nothing
End Function


Function EncodeBase64(text As String) As String


  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = Application.Clean(objNode.text)

  Set objNode = Nothing
  Set objXML = Nothing
End Function