Microsoft

Recently I've been debugging a SQL Server 2005 replication setup, where the "Distribution clean up" job for the distributor database was failing. The error message in the agent history was:

Executed as user: <sqlagent_serviceuser>. Could not remove directory <UNC_path_to_snapshot_directory>.
Check the security context of xp_cmdshell and close other processes that may be accessing the directory.
[SQLSTATE 42000] (Error 20015).  The step failed.

The “<sqlagent_serviceuser>” was the Windows user that the SQL Server Agent service was running under, while the “<UNC_path_to_snapshot_directory>” was the network path to the directory where snapshots where created.

Looking around on some discussion forums, I found the same message reported several times but without any real solution; at least no solution that would work when I tried to apply it to this particular setup. Most answers to the problem focused on the xp_cmdshell procedure mentioned in the error, and how it would require certain configuration at the server end.

However, one answer on the MSDN forums did give a useful clue — it pointed to the SQL Books Online article about Securing the Snapshot Folder (which I should've read right away, really). In a note, this page reads:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. [...]

It's the SQL Server service account reference which is essential: even though the SQL Agent service account may be the one mentioned in the error message, the actual account that is used to try and delete the snapshot directories is the SQL Server service account instead. Giving that service account the necessary access rights fixed the problem; as in the Task Scheduler case, accurate error reporting on Microsoft's side might go a long way towards people disliking their software less.