• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

SQL 2008 Transactional replication and agent accounts

I'm having problem setting up transactional replication.
Once I set up publication, it seems that it generates snapshot file successfully. But then when I create subscription from another server, both publication and subscription in Replication monitor shows 'Retrying failed command'.
I think this is something has to do with the accounts I selected when I create publication and subscription.

What I did is, I created a new SQL account, then gave db_owner and publication for the database and make it as sys_admin then I set up publication with it.

Then on subscriber, I created another SQL account, the same name I created in publisher and gave same permissions for login and database mappings.

When I open Repl mon, it shows the account I use still can't make snapshot agent to go alive. I wonder how it successfully created the initial snapshot, then.

What are the proper account setup for replication agents on both publication and subscriber?
1 Solution
crcsupportAuthor Commented:
I'm trying to make transactional replication from SQL 2008 STD to SQL 2012 Express. There's
Carl TawnSystems and Integration DeveloperCommented:
The specifics of the security requirements for each agent involved in replication are documented here: http://technet.microsoft.com/en-us/library/ms151868(v=sql.105).aspx

But basically they generally need to be Windows accounts, rather than SQL accounts, have at least db_owner privileges, and the subscriber will also need read permissions on the directory where the snapshot resides.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now