Distributor SQLSERVERAgent Account

What account should the SqlServerAgent run under at the distributor
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
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
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?

> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.

distribution cleanup cannot clean up snapshot folder

We are using SQL Server 2005 SP2 to do transactional replication.

We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.

I have been getting an error message when we run the distribution clean up job.

Executed as user: PROD\sqladmin. Could not remove directory '\\Tes01box\Repldata\unc\qabox01_DB01_TO_ORACLE\20070905104896\'. Check the security context of xp_cmdshell

I have dropped the publication and recreated which is what appears to have caused the error.

From

http://technet.microsoft.com/en-us/library/ms151151.aspx

Note:

If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.

The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.

Finally my questions:

Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?

If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?

open up your distribution clean up task job, and in the job step properties do the following:

setuser 'sqlrepadmin'
GO
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0, @.max_distretention = 72
GO|||

I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains.

What an amazingly simple and elegant solution. I feel silly that I did not think of this.

Thank you Hilary.