SQLAgent Service receiveing tran replication can start with local system account

SQLAgent Service receiveing tran replication can start with local system account

Post by Tari » Sat, 20 Jul 2002 16:40:08



Hi

Running on Win 2000 and MS SQL 2000,

I am setting up a push transactional replication with no
data and schema initialisation from say server X to server
Y. Replication is global and with no trust relationships
available. Also, I could not start servers involved using
domain accounts or match local user accounts that start
agent services across all servers.

My testing went as follows:
-To start with Server x SQL Agent Service was setup to
start as Local System Account and connect to server as sa.
Log Reader Agent and Distribution Agent failed with (The
process could not connect to server/Login failed for user)

- Then I changed SQL Agent Service in Server X to start
using local user account member of Users Group. The
service was unable to start.

- Then I changed SQL Agent Service in Server X to start
using local user account member of Local Admin group but
with no access to SQL Server. Log Reader Agent and
Distribution Agent failed with (The process could not
connect to server/Login failed for user)

- Then I gave the local user access to database in SQL
Server X. Now replication is working with no problems.

- Server Y still starts using local system account.

Although SQL Agent is setup to connect with a valid user
(sa). It connects using the user used to start

SQL Agent service (Start-up account in the General Tab)
and not using details of SQL Server Connection (in the
Connection Tab of 'SQL Server Agent Properties).

My conclusion is :
To push a transactional replication, with no data and
schema initialisation, from say server X to server Y:
1- Server X SQL Agent Service needs to start using Local
or Domain User. The account needs to be member of Win
local administrator group and with sufficient access to
the Primary SQL Server. It does not, however, need access
to the Replicate SQL Server, providing when setting up the
subscription an account with permission to connect to the
replicate server was chosen.
2- SQL Server Agent service in the replicate server (only
receiving push transactional replication) can start using
any method including local system account.

Any feedback. I noticed that Microsoft suggest that SQL
Agent service in both servers should either start using
domain account or the same local account.

Any feedback, would the above fail at a later stage or
affect my replication in any way? Would the method above
work with MS SQL 7? Am I messing something?

Comments highly appreciated.

Tariq

 
 
 

1. Local System Account or Domain Account For SQL Server Service

For SQL Server 2000 Enterprise Edition on Windows 2000 Advanced
Server, should we use the local system account to run the SQL Server
Service, or a Windows domain account?  I understand some of the
benefits of using a domain account for the agent e.g. SQL Mail,
although this is not supported for SQL Server clusters, but am
clueless when it comes to the server service.

Also, are there some security issues with using the local system
account?  Can a stored procedure writer use xp_cmd to do something
nasty, like format C:\ ?  If so, what kind of privs do you need to
give to your domain account?

Thanks in advance for sharing your knowledge!

Ken

2. fpw2.6 Desktop picture moves up when Dos Command used. ????

3. confusion between local system account and this account in services log on as

4. Is table based on DTD creating tool available?

5. Service Account vs. Local System Account

6. Error having two ForwardOnly recordsets open

7. odbc version

8. Local Admin group for SQLAGENT account?

9. Change SQLAgent Service Account

10. Service Account vs. System Account

11. Start SQLAgent Service from Code

12. SQLAGENT service shutdown and can't start ...