sql server replication: We have two servers, we have nor dropped the replication
between the two, and rebuilt it from scratch.

It successfully creates the snapshot - no problem. However
when I start the Distribution agent to activate the
snapshot and fill the tables etc, it gets so far into
running the automatically genereated scripts - then fails
with an 'access denied' error. It doesnt tell you where
the error is, or what on. Can someone give me some
pointers on which security settings I should be looking
for?

Hi Carl,

Since all snapshot files come from the same directory, it seems strange that
only a subset of the files can be accessed from the Distribution Agent. Can
you check and see if the "accessed denied" error comes directly from the OS?
If that fails to provide any useful clue, I do have the following
speculations (albeit slightly on the wild side) to offer:

1) Did you enable the "UseInprocLoader" option (via the distribution agent
profile or the command line in the job step) by any chance? If this option
is enabled, the "BULK INSERT" T-SQL statement is used to perform the BCP-in
operation which would in turn require that the SQL Server service account
(in addition to the Distribution Agent\SQL Server Agent account) to have
read access to the generated BCP files. The lack of SQL Server privilege in
this case would neatly explain why you are seeing the "access denied" error
for just some snapshot files.
2) Certain background processes\applications such as virus scanners and
backup programs may lock out files from time to time so you may want to
check whether such programs are to blame.
3) If the neither of the first two items matches your situation, you would
need to manually check the snapshot file\share ACL&access attributes.

Raymond

This posting is provided "as is" with no warranties and confers no rights.


[quoted text, click to view]