none
VBA and authentication sharepoint RRS feed

  • Question

  • Hello,

    I'm currently working on a project with access 2010, the application will need to download files stored on sharepoint. I managed to do this by using web services (SOAP requests). That only work if I already opened a web browser on the sharepoint site.
    In the long run, the application will use an administrative user account, and will need to authenticate itself.

    To my understanding, I need to use the Authentication service / Login method. I did so by using the XMLHttp object (WinHttp does not work, guess for security reasons).
    For testing purpose I use my own windows login and password but get a "PasswordNotMatch" response.

    My questions:
    1) What's wrong with user login and password ? any specific format required ? encryption ?
    2) I'm supposed to retrieve a cookie upon successfull authentication, how do I get it ?
    3) How do I send the cookie in my futur SOAP requests ?

    Here is the code I wrote so far:

    Private Const C_strHost As String = "MySharePointServer"
    Private Const C_strPassword as string = "MyPassword"
    
    Public Function Login(Optional ByRef strResponse As String) As Long
    On Error GoTo Error
        Const strSoapAction As String = "http://schemas.microsoft.com/sharepoint/soap/Login"
        Const strContentType As String = "text/xml; charset=utf-8"
        Const strCommand As String = "_vti_bin/authentication.asmx"
        Dim oDoc As MSXML2.DOMDocument
        Dim oProcess As MSXML2.IXMLDOMProcessingInstruction
        Dim oNode As MSXML2.IXMLDOMElement
        Dim oNewNode As MSXML2.IXMLDOMElement
        Dim oHttp As MSXML2.XMLHttp
        Dim strUrl As String
        
        If (C_strHost <> vbNullString) Then
                '// Create XML document for SOAP request
            Set oDoc = New MSXML2.DOMDocument
            Set oProcess = oDoc.createProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
            Call oDoc.appendChild(oProcess)
            Set oNewNode = oDoc.createElement("soap:Envelope")
            Set oNode = oDoc.appendChild(oNewNode)
            Set oNewNode = oDoc.createElement("soap:Body")
            Set oNode = oNode.appendChild(oNewNode)
            Set oNewNode = oDoc.createElement("Login")
            Set oNode = oNode.appendChild(oNewNode)
            Set oNewNode = oDoc.createElement("username")
            oNewNode.text = enviiron("USERNAME")
            Call oNode.appendChild(oNewNode)
            Set oNewNode = oDoc.createElement("password")
            oNewNode.text = C_strPassword
            Call oNode.appendChild(oNewNode)
            Set oNewNode = Nothing
            
                '// Update attributes
            Set oNode = oDoc.getElementsByTagName("soap:Envelope").nextNode()
            Call oNode.setAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance")
            Call oNode.setAttribute("xmlns:xsd", "http://www.w3.org/2001/XMLSchema")
            Call oNode.setAttribute("xmlns:soap", "http://schemas.xmlsoap.org/soap/envelope/")
            
            Set oNode = oDoc.getElementsByTagName("Login").nextNode()
            Call oNode.setAttribute("xmlns", "http://schemas.microsoft.com/sharepoint/soap/")
            Set oNode = Nothing
            
                '// Connections parameters
            strUrl = "https://" & C_strHost & "/" & strCommand
            
                '// Open connection
            Set oHttp = CreateObject("MSXML2.XMLHttp")
            Call oHttp.Open("POST", strUrl, False)
            Call oHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
            Call oHttp.setRequestHeader("Content-Length", CStr(Len(oDoc.XML)))
            Call oHttp.setRequestHeader("SOAPAction", strSoapAction)
            Call oHttp.send(oDoc.XML)
            Set oDoc = Nothing
            If (oHttp.status = 200) Then
                strResponse = oHttp.responseText
            Else
                strResponse = oHttp.statusText
            End If
            Login = oHttp.status
            Set oHttp = Nothing
        Else
            Login = 0
        End If
    Exit Function
    Error:
        If Not (oNewNode Is Nothing) Then
            Set oNewNode = Nothing
        End If
        If Not (oNode Is Nothing) Then
            Set oNode = Nothing
        End If
        If Not (oDoc Is Nothing) Then
            Set oDoc = Nothing
        End If
        If Not (oHttp Is Nothing) Then
            Set oHttp = Nothing
        End If
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End Function

    Thanks for your time.

    PS: I have no control over my workstation's environnement, installing add-ins or other softwares is not an option.

    Monday, January 19, 2015 9:21 AM