Re: Question about "Distribution clean up: distribution" Job

I am guessing (again) that the SQL Server Agent service account is not what
xp_cmdshell running under in the cleanup scenario, another suspect will be
the SQL Server service account. In any case, you should be able to find out
exactly which security account is doing the delete using the filemon.exe
tool from sysinternals.com.

"Ken Powers" <KenPowers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@xxxxxxxxxxxxxxxx
You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still
receiving
the error even after I granted the service account "Full Control" over the
share.

"Raymond Mak [MSFT]" wrote:

Hi Ken,

I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the
SQLServerAgent
service account Full Control rights on it.

-Raymond

"Ken Powers" <KenPowers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@xxxxxxxxxxxxxxxx
It is SQL Server 2005 and xp_cmdshell was already enabled.

"Hilary Cotter" wrote:

Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing
this
directory.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Ken Powers" <KenPowers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@xxxxxxxxxxxxxxxx
Can the manner in which you remove a publication effect the ability
of
this
job to run?

I had been experiencing errors with this job. The error message was
as
follows:

"Executed as user: <SQLServerAgentDomainAccount>. Could not remove
directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGDAILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes
that
may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The
step
failed."

I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same
error,
I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.

Then I went into the job step and copied the command it was
attempting
to
run. This is the command:

"EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0,
@max_distretention = 72"

I pasted this command into a query window and attempted to run it
under
my
own login, which is a member of the sysadmin role and is also a
member
of
the
Local Administrators group. It still could not run.

The folders the command was trying to delete were used for
Publications
that
no longer existed. So I went ahead and deleted the folders
manually.
Now
the command ran successfully.

I am thinking something went wrong during the process of removing
the
publications and subscriptions. Anyone have any idea what might
have
happened?








.

Relevant Pages

  • Re: Question about "Distribution clean up: distribution" Job
    ... the Agent service account had not been granted ... experimented with running the job as a user with sysadmin rights. ... Then I went into the job step and copied the command it was attempting ... The folders the command was trying to delete were used for Publications ...
    (microsoft.public.sqlserver.replication)
  • RE: Upgrade from 2005 to 2008: Invalid Credentials
    ... it seems that this issue was related to NETWORK SERVICE account for SSIS service could not be validated on DC. ... Please first go to your Services pane, double click your SQL Server Integration Services, switch to the Log On tab to check if the service account is NETWORK SERVICE account. ... Install SQL Server 2008 from the Command Prompt ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.setup)
  • Re: SPN Requirement
    ... The service account for SQL Server needs to be a domain ... account to allow Write Public Information rights but I'd ... The SQL Network Interface library could not register the Service Principal ... Name for the SQL Server service. ...
    (microsoft.public.sqlserver.security)
  • Re: Question about "Distribution clean up: distribution" Job
    ... I granted the SQL Server Service Account "Full Control" over ... the Snapshot Folder and the job has been running successfully ever since. ... Then I went into the job step and copied the command it was ...
    (microsoft.public.sqlserver.replication)
  • Re: xp_cmdshell failed to execute because current security context
    ... INFO: databasename: xp_cmdshell failed to execute because ... SQL Server running under has appropriate privilege. ... Server service account does not have all the relevant permissions not the sql ... ontario, canada ...
    (microsoft.public.sqlserver.server)