SharePoint API and VBA - Access Denied

Question

I'm trying to retrieve items from a SharePoint list using the SharePoint Web API. I'm coding in VBA, because a need to display the information in a Word document. However I'm getting the Access Denied error

{"error":{"code":"-2147024891, System.UnauthorizedAccessException","message":{"lang":"en-US","value":"Access denied. You do not have permission to perform this action or access this resource."}}}

There is a bit of the code

Dim request As New MSXML2.XMLHTTP60
With request
    .Open" GET","https://<mySharePoint>/_api/web/lists/GetByTitle('<listName>')/items", False
    .setRequestHeader "Accept", "application/json;odata=verbose"
    .send
    JSON = .responseText
End With

TextBox1.Text = JSON

I'm using an UserForm control of the VBA just for now to see the response.

I've read that I might need an access token, but I'm not sure if I really need it. Just to clarify. I'm not out of the company's domain, here we use Office 365, so Office World is connected to SharePoint, and I'm logged to SharePoint in the browser. Thus, I don't understand why do I still need a token. If I really need one can someone explain the process to get it using VBA?

Answer 1

I've found the solution for my question... so I hope this helps someone.

Answering my own question, yes we need to send a Token to be able to use the API. The approach to get this token is to retrieve the authentication cookie (SPOIDCRL cookie).

To do that I followed a PowerShell example and converted to VBA code. https://blogs.technet.microsoft.com/sharepointdevelopersupport/2018/02/07/sharepoint-online-active-authentication/

I did some changes as the PowerShell script was very generic, and I was aiming to use only Integrated authorization, which means that I don't need to provide any user name or password.

The following link was very helpful to understand the process to get the cookie, but it uses the username and password to get the cookie. http://www.sharepoint-insight.com/2016/03/21/step-by-step-sharepoint-online-authentiaction-process-via-http-protocol/

Bellow there is a function that I created to get the cookie as string. Bear in mind that the code is not perfect as doesn't have any error handling and could be refactored. The code is not been render correctly due to the comments. However if you copy to a VBA editor you might see it correctly.

    Private Function Authorization() As String
    ' Set the STS URL
    stsURL = "https://[get this URL using the GetUserRealm.srf endpoint]/adfs/services/trust/2005/windowstransport"
    ' Generates a new GUID using a Module (You can use any code to generate a GUID from the internet)
    myGUID = GenerateGUID
    ' Set the Realm
    realm = "urn:federation:MicrosoftOnline"
    ' Set the STS XML for Integrated requests
    xmlSTSIntegrated = "<?xml version='1.0' encoding='UTF-8'?><s:Envelope xmlns:s='http://www.w3.org/2003/05/soap-envelope' xmlns:a='http://www.w3.org/2005/08/addressing'>" & _
                "<s:Header><a:Action s:mustUnderstand='1'>http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</a:Action><a:MessageID>urn:uuid:{0}</a:MessageID>" & _
                "<a:ReplyTo><a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address></a:ReplyTo><a:To s:mustUnderstand='1'>{1}</a:To></s:Header>" & _
                "<s:Body><t:RequestSecurityToken xmlns:t='http://schemas.xmlsoap.org/ws/2005/02/trust'><wsp:AppliesTo xmlns:wsp='http://schemas.xmlsoap.org/ws/2004/09/policy'>" & _
                "<wsa:EndpointReference xmlns:wsa='http://www.w3.org/2005/08/addressing'><wsa:Address>{2}</wsa:Address></wsa:EndpointReference></wsp:AppliesTo>" & _
                "<t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey</t:KeyType><t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</t:RequestType>" & _
                "</t:RequestSecurityToken></s:Body></s:Envelope>"
                
    xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{0}", myGUID) ' Replace the first spot with the GUID ID
    xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{1}", stsURL) ' Replace the second with the STS URL
    xmlSTSIntegrated = Replace(xmlSTSIntegrated, "{2}", realm) ' Replace the third with the real
    xmlSTSIntegrated = Replace(xmlSTSIntegrated, "'", """") ' Replace single quote for double quotes
    
    
    Dim h As New WinHttpRequest ' Create a new WinHttpRequest Object
    
    ' Send a POST request to the STS URL with the XML in body
    With h
        .Open "POST", stsURL, False
        '.setRequestHeader "UserAgent", "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; Touch; rv:11.0) like Gecko"
        .setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8" ' Required! - as the XML is encapsulated in SOAP
        .SetAutoLogonPolicy (AutoLogonPolicy_Always) ' Required! - If this is omitted it will return 401 error - Unauthorized
        .Send (xmlSTSIntegrated) ' Send the xml
        response = .responseText ' Get the response
    End With
        
    Dim xml As New MSXML2.DOMDocument60
    xml.LoadXML (response) ' Convert the response as XML

    
    ' Send a GET request to the endpoint with headers
    With h
        .Open "GET", False
        .setRequestHeader "Authorization", BinaryToken ' Set the Authorization header with the Binary Token
        .setRequestHeader "X-IDCRL_ACCEPTED", "t" ' Set the X-IDCRL_ACCEPTED
        .SetAutoLogonPolicy (AutoLogonPolicy_Always) ' Required! - If this is omitted it will return 401 error - Unauthorized
        .Send ' Send the request
        cookie = .getResponseHeader("Set-Cookie") ' Get the cookie
        .abort ' Abort the WinHTTP connection to avoid problems
    End With
    
    Authorization = cookie ' Return the cookie
End Function