Windows authentication on Sharepoint

Question

---

Update:

OK, this is solved now. The code does work, but the permissions on the Sharepoint server have turned out to have gone awry. So that's a relief.

---

I'm using VBA to send a request from Excel to Sharepoint via its web services. This is on a corporate intranet, so my understanding is that Windows Authentication should just be happening in the background as long as I use my Windows login to access Sharepoint (which I do). I have checked that authentication mode is set to "Windows". But I still get an "Access denied" error when I try this.

Here's the XML I'm sending:

<?xml version="1.0" encoding="utf-8" ?>
    <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
      <soap12:Body>
        <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <listName>listname1</listName> 
          <updates>
            <Batch OnError='continue' ListVersion='1'>
            <Method ID='1' Cmd='Delete'>
                <Field Name='ID'>7582</Field>
            </Method>
            </Batch>
            </updates>
        </UpdateListItems>
      </soap12:Body>
    </soap12:Envelope>

I have tried sending the username and password in a Soap header, thus:

      <soap12:Header>
        <AuthHeader xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <UserName>user1</UserName>
          <Password>password1</Password>
        </AuthHeader>
      </soap12:Header>

Made no difference.

The return message Sharepoint is sending me does include some of the data it's not allowing me to delete, so I know I'm managing to connect to the right site. And I do have permission to delete from that site.

Here's the VBA I'm using to send the XML (copied from elsewhere on StackOverflow):

Dim xmlhtp As New MSXML2.XMLHTTP
Dim XMLDOC As New DOMDocument
Dim XMLstr As String
With xmlhtp
    .Open "POST", "https://sharepoint.address/_vti_bin/lists.asmx", False
    .setRequestHeader "Host", "sharepoint.address/site1823/Lists/HC Movement"
    .setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8"
    .send XMLstr

    XMLDOC.LoadXML .responseText
    MsgBox .responseText
End With

Replacing "HC Movement" with "HC%20Movement" makes no difference.

I don't care whether this happens seamlessly in the background: if users have to input their credentials, so be it. And if someone has a solution utterly unlike what I've got so far, that's fine too. Just need to be able to delete and add records to Sharepoint from Excel, and have been hitting a brick wall on it for days.

I'm pretty good with VBA but have never used web services before. Any help would be hugely appreciated.

Thanks.

Answer 1

With .Net you can pass the user's credentials, but I haven't found a way with VBA (yet). If you don't mind having your users input their username/password I would go that route for now. Create a userform with a textbox for the username and a masked textbox for the password. You can even pre-fill the username if you want:

Dim myUser As String
myUser = Environ("USERNAME")

Then pass the entered values to your web request. Change the .Open command to include the variables (myUsername and myPassword for example):

.Open "POST", "https://sharepoint.address/_vti_bin/lists.asmx", False, myUsername, myPassword

Create an error catch for an incorrect password and your done.

I'll keep looking for a way to pass the logged-in user's credentials (or if someone else has found a way...).