The picture is not entirely complete here, but I made a lot of progress with this as an open (now closed) troubleshooting ticket with Microsoft.

The short summary is that using a Microsoft whitepaper (linked to below), I could get Windows XP SP2 workstations to work properly opening an Explorer View to a SharePoint 2007 farm’s document library, I had a bunch of other problems to surmount when trying to get a Windows Server 2003 SP2 system to do the same. The rest of this post is about other methods for troubleshooting and fixing the situation.

Situation:

• SharePoint Server in EXTDOMAIN (our extranet domain) named sharepointbi.mycompany.net (MOSS 2007 running on Windows Server 2003 x64 SP2)
• SQL Server cluster node (SQL Server 2005 active node running on Windows Server 2003 x64 SP2 – named: sqlolap.mycompany.net)  running an ETL job finishes up by attempting to use xcopy from a filesystem share to a document library at http://sharepointbi.mycompany.net/site/dl/ (UNC path: \\sharepointbi.mycompany.net\site\dl\) using WebDAV.
• Various Windows XP SP2 workstations (e.g. named: mcy20m68f.mycompany.com – yes, in another domain) also mapped a drive or file copy operation via WebDEV to \\sharepointbi.mycompany.net\site\dl\
• xcopy/share mapping operations from the SQL Server seemingly worked for a couple of days and then consistently failed.
• xcopy/share mapping operations from the Windows XP SP2 worksations mostly worked.

Troubleshooting:

We spent a day or so under the mistaken impression that the network protocol at issue was SMB. It’s not. When you’re talking about SharePoint, you’re talking about WebDAV or possibly Web Folders (but only if you install an add-on) for file system-like operations against its resources. Generally when you’re using a UNC path that uses a SharePoint-hosted resource as a file system-like resource, your computer is accessing SharePoint via WebDAV.

It took some time to establish that the Understanding and Troubleshooting SharePoint Explorer View whitepaper DOES apply to SharePoint 2007 (the publication date sort of indicated it might not), but I guess the WebDAV infrastructure of the SharePoint product line hasn’t changed substantially since SharePoint 2003. I don’t know for sure, but this seems like a logical guess.

After I went through that whitepaper and applied it to both the problem SQL Server we were working with and the recalcitrant (not always connecting immediately on the first try) workstations, I got all the workstations working properly. In some cases, what in the whitepaper looks like an optional setting – e.g. like putting the target SharePoint server in your Trusted Sites in your Internet Options to avoid getting prompted for login – was vital. Some machines seem not to automatically provide credentials during the WebDAV connection and the connection fails.

This still left the SQL Cluster node (Windows Server 2003 SP2 machine), which was still consistently failing. The final fix ended up being a mixture of things found and not found in the whitepaper I linked to above:

Server settings (one time settings for the box):

• Hosts file entry.
  In our case the SharePoint Farm and our network configuration channels and redirects regular traffic going through a front end load balancer through SSL. The load balancer chooses among several web front ends for incoming traffic. WebDAV doesn’t support any port other than port 80. The hosts file entry points the client machine (sqlolap.mycompany.net) to a particular web front end server in the SharePoint farm directly, via its accessible back-end port, avoiding the SSL redirect and the load balancing.
  Example: The back-end NIC for your chosen WFE in the farm has an IP address of 10.3.1.100 and the URL to the resource you’re trying to map is http://sharepointbi.mycompany.net/site/dl/. Your hosts file entry on the client machine attempting the WebDAV connection should look like this:
  10.3.1.100     sharepointbi.mycompany.net
• Verify that the Web Client service is running properly on the client system. It should have an Automatic startup and be Started in the Services display for the server.
  Also note that if you are running Windows Server 2008, you’ll also need to install the Desktop Experience Feature on your system or the Web Client service won’t be available for you to start or stop.
• Change the order of Network Providers for the system so that the “Web Client Network” provider is at the top instead of the bottom of the list.
  This was necessary because for some reason the server that was acting as the client in our setup would have its SMB negotiations fail and simply wouldn’t fail down to the Web Client Network provider like it was supposed to. Microsoft support and I were not able to determine why. It’s an open question whether this makes anything else break and my Microsoft resources are looking into it. I’ll update here if anything’s found.
  Anyhow, you make this change by opening up the client’s Network Connections dialogue in Control Panel. From there, go to the Advanced drop down menu and choose Advanced Settings… This pops up a dialogue where you can choose the Provider Order tab. There you can move the Web Client Network provider up to the top of the Network Providers list.

User Profile settings (Internet Options settings that carry with each user profile):

• Disable the proxy or set an exception to proxying for the target SharePoint Server (though we had less consistent luck with this).
• Add the target SharePoint Server to the Trusted Sites group in the Security Tab. Uncheck the “Require server verification (https:) for all sites in this zone” checkbox since you won’t be using port 443. During troubleshooting I put two entries in here:
  • http://sharepointbi.mycompany.net
  • sharepointbi.mycompany.net
• Check the Security level for the Trusted sites zone. You can set it to Default Level (which s/b Low for this content zone), which should work, but the seminal setting in the whole thing was the User Authentication setting at the end of Security Settings for the Trusted zone, which should be “Automatic logon with the current username and password”.

Next steps:

• Verify that your WebDAV connection is working. You should be able to do this with any windows operation that allows you to open the SharePoint document library as a filesystem resource. My favorite methods were:
  • Windows Explorer
    Just open up Windows Explorer and sock the SharePoint document library as a UNC path typed into the Location bar. (e.g. \\sharepointbi.mycompany.net\site\dl\)
    I found that even with the site in Trusted sites and Authentication set to always logon with current credentials, the Server 2003 client prompted me for manual entry of credentials anyway.
  • net use
    Use net use to map a drive with the UNC path (\\sharepointbi.mycompany.net\site\dl\).
    e.g.: net use * \\sharepointbi.mycompany.net\site\dl\
    Then remove same by drive letter (e.g. if the letter your system assigns is Z:):
    net use z: /delete
  • xcopy
    Use xcopy to copy a file from the UNC path (\\sharepointbi.mycompany.net\site\dl\) to some temporary folder. Verify that the file copies and you know WebDAV is working.
    e.g. xcopy \\sharepointbi.mycompany.net\site\dl\test.txt .
• Make sure that changing the Network Provider order isn’t doing any long term damage. Will update this if I get word from my Microsoft support folks.
• Test the inclusion of the Web Folders add-on (KB 907306) that Microsoft suggested on that client machine.