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"
    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?


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>" & _

    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

    ' Get the Assertion node from the xml
    xmlAssertion = xml.ChildNodes.Item(0).ChildNodes.Item(1).ChildNodes.Item(0).ChildNodes.Item(2).ChildNodes.Item(0).xml

    ' Set the SXML to be sent
    xmlSXML = "<?xml version='1.0' encoding='UTF-8'?><S:Envelope xmlns:S='http://www.w3.org/2003/05/soap-envelope' xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'" & _
                " xmlns:wsp='http://schemas.xmlsoap.org/ws/2004/09/policy' xmlns:wsu='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' " & _
                " xmlns:wsa='http://www.w3.org/2005/08/addressing' xmlns:wst='http://schemas.xmlsoap.org/ws/2005/02/trust'><S:Header>" & _
                " <wsa:Action S:mustUnderstand='1'>http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</wsa:Action><wsa:To S:mustUnderstand='1'>https://login.microsoftonline.com/rst2.srf</wsa:To>" & _
                " <ps:AuthInfo xmlns:ps='http://schemas.microsoft.com/LiveID/SoapServices/v1' Id='PPAuthInfo'><ps:BinaryVersion>5</ps:BinaryVersion><ps:HostingApp>Managed IDCRL</ps:HostingApp></ps:AuthInfo>" & _
                " <wsse:Security>{0}</wsse:Security></S:Header><S:Body><wst:RequestSecurityToken xmlns:wst='http://schemas.xmlsoap.org/ws/2005/02/trust' Id='RST0'>" & _
                " <wst:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</wst:RequestType><wsp:AppliesTo><wsa:EndpointReference><wsa:Address>{1}</wsa:Address></wsa:EndpointReference></wsp:AppliesTo>" & _
                " <wsp:PolicyReference URI='MBI'></wsp:PolicyReference></wst:RequestSecurityToken></S:Body></S:Envelope>"

    xmlSXML = Replace(xmlSXML, "{0}", xmlAssertion) ' Replace the first part with the xml Assertion node
    xmlSXML = Replace(xmlSXML, "{1}", "sharepoint.com") ' Replace the Domain
    xmlSXML = Replace(xmlSXML, "'", """") ' Replace single quotes for double quotes

    ' Set the STS Auth URL
    StsAuthURL = "https://login.microsoftonline.com/rst2.srf"

    ' Send a POST request to the STS Auth URL with the xml
    With h
        .Open "POST", StsAuthURL, False
        .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 (xmlSXML) ' Send the xml
        response = .responseText ' Get the response
    End With

    Dim xmlBinaryCookie As New MSXML2.DOMDocument60
    xmlBinaryCookie.LoadXML (response) ' Convert the response to xml

    ' Get the BinaryToken and concatenet with BPOSIDCRL
    BinaryToken = "BPOSIDCRL " + xmlBinaryCookie.ChildNodes.Item(1).ChildNodes(1).ChildNodes.Item(0).ChildNodes.Item(3).ChildNodes.Item(0).Text

    ' Set the ID CRL Endpoint
    idCrlEndpoint = "https://{your tenant}.sharepoint.com/_vti_bin/idcrl.svc/"

    ' Send a GET request to the endpoint with headers
    With h
        .Open "GET", idCrlEndpoint, 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.