SQL Server and Agent Accounts

[Maer]

Hi guys - I have two questions about accounts that sql server services
run under:

1) When I try to install the publisher, it fails to create the
distribution database because it says that login failed for NT
Authority\System (the SQL Server service was running as Local System). When
I added NT Authority\System (as sysadmin), everything worked fine. Then I
removed it and assigned a windows account to SQLServer service that was a
sysadmin, and it also worked fine. So my question is, why does SQLServer
service need to be running under an account that is a sysadmin?

2) I have create a merge replication with pull subscriptions. I noticed
that the SQL Agent can run under an account that is not in the publisher. So
when I synchronize does it use the account that is currently logged in?

Thank you for your help,
Maer

[Paul Ibison]

Maer,
a lot of the replication procedures can only be run by sysadmin. Fo rthe
second question, I'm not too sure what you mean. The account needs to have
access to the snapshot share and after that if you're using impersonation
it'll need to be in the PAL. If you're working on a schedule it'll use this
account.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Sophie Guo [MSFT]]

Hello,

For the first question, I suggest that you refer to the information in SQL
server Books Online:

"Security Considerations" topic:

---------------------------------------------

The SQL Server Agent service (SQLServerAgent) at the client should not use
the LocalSystem account. It needs to use a standard domain account. The
SQLAgent account is the security context under which the Snapshot Agent,
Merge Agent, and Distribution Agent are running by default.

...

You must be a SQL Server 2000 system administrator to enable the server for
replication. After replication is enabled, you do not need to be a SQL
Server 2000 system administrator to set up publications and subscriptions,
or to invoke or schedule the replication agents. You must be in the
db_owner role to create publications. Anyone who is added to the
publication access list (PAL) can create pull subscriptions to that
publication (but only to that publication).

---------------------------------------------

For the second question, refer to the "Agent Login Security" topic in SQL
server Books Online. For more information, refer to the following topics:

"Connecting to the Distributor"
"Replication Security"

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Maer]

Hi Guys - Thank you so much for your help. Let me clarify my questions:

1) I log in to the server as a sysadmin. What I have observed is that
it is not enough to be logged in as a sysadmin when running the replication
wizard, SQLServer service must run under a windows account that is also
sysadmin on the server (and if you the service is running under Local
System, then NT Authority\System must be sysadmin).
I would like to confirm that this is indeed a prerequisite.

2) Here is a scenario that I tested: I created a Windows account called
"Domain\ReplAccount" that has access to the snapshot folder on the server.
Then I set the SQL Agent account to run under Domain\ReplAccount. I did not
create a login for Domain\ReplAccount on the publisher.
Then I logged in to the subscriber using an account (Domain\Maer) that
is in the PAL on the publisher and synchronized. Everything worked fine.

My confusion is that I thought that Domain\ReplAccount had to be in the
PAL. Apparently when SQL Agent logs in to the publisher it uses Domain\Maer.

Is this correct? Is this what impersonation means?

Thank you,
Maer

"Paul Ibison" <Paul....@Pygmalion.Com> wrote in message
news:u%23EvkVh1...@TK2MSFTNGP09.phx.gbl...

[Sophie Guo [MSFT]]

Hello Maer,

The following information is for your reference:

1)

I tested the issue on my side. To test it, I set the following
configurations:

1. SQL server 2000 service startup account is the system account.
2. The SQL server agent startup account is a domain\user which is a local
administrator.
3. I log on the server locally as the domain\user, the "NT
authority\Network service" isn't a member of SQL server sysadmin.

I configure the local server as the Distributor, and then creating a
snapshot replication successfully. Therefore the result is to configure
Distributor, the user need to be a member of the sysadmin fixed server
role.

For your reference, when configuring Publishing and Distribution, SQL
server performs the following steps:

1. Execute sp_adddistributor at the server that will be the Distributor.
2. Execute sp_adddistributiondb at the Distributor to create a new
distribution database.
3. Execute sp_adddistpublisher at each server that will be a Publisher
using the Distributor.

Only members of the sysadmin fixed server role can execute
sp_adddistributor, sp_adddistributiondb, sp_adddistpublisher.

2)

Replication implements login security by requiring a user to have a valid
login account and password to connect to a Publisher, Distributor, or
Subscriber. Replication agents run under SQL Server Agent and use the
associated logins and passwords to connect to the various replication
objects and to perform their roles in the synchronization process.

On the Microsoft Windows 98 operating system, SQL Server Agent and the
replication agents run under the security account of the user logging on to
Windows. On the Microsoft Windows NT 4.0 and Windows 2000 operating system,
replication agents run under the login or security context of the
SQLServerAgent service. Each agent connects to one or more servers and must
have a valid login to complete the connection.

For more information, refer to the "Agent Login Security" topic in SQL
server Books Online:

Agent Login Security
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/rep
lsec_7it5.asp

------------------------
Applying a Snapshot
When applying a snapshot, the agents must have the following capabilities:

The Snapshot Agent connects to the publication database on the Publisher
and to the distribution database on the Distributor. The Snapshot Agent
also writes to the snapshot folder when storing the snapshot files.

------------------------

Publication Access Lists
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/rep
lsec_7cj2.asp

----------------------
When you create a publication, Microsoft SQL Server 2000 creates a
publication access list (PAL) for the publication. The PAL contains a list
of logins that are granted access to the publication. The logins included
in the PAL are members in the sysadmin fixed server role and the current
login.
----------------------