Permissions issues with new transactional replication job

Question

Post reply

Permissions issues with new transactional replication job

tarr94

SSCommitted

Points: 1684
More actions
March 10, 2022 at 5:37 pm

#3997118
Hello,
I recently attempted to set up a new transactional replication process. Unfortunately, I haven't had any success so far running the snapshot agent. When the job kicks off, I quickly get this error:
"Executed as user [Domain\UserName]. A required privilege is not held by the client. The step failed."
Here's what I've done so far to try and troubleshoot this.
1. Confirmed that the user from the error message is a db_owner for both the database that is serving as the publisher and for the distribution database.
2. The user from the error message has read/write/modify permissions to the drive where the replication files will be stored (as well as all other drives on the server)
3. Confirmed that the Snapshot Agent and Log Reader Agent are both the user from the error message, and that they connect to the publisher by impersonating the process account.
4. Tried going into SQL Server Configuration Manager, turned off the SQL Server and SQL Server Agent services, toggled the "Log On As" to the local accounts, restarted the services, turned them back off, reset the "Log On As" to the original log on account, then started the services back up. This seems to be a common solution for people running into this problem, but it also seems to be more commonly applied to cases where all of the SQL agent jobs are throwing the permissions error, not exclusively the snapshot agent job (as is the case here).
Is there anything else I could be missing here? Your help is greatly appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

Have never used transactional replication to be honest.

but heres a document from Microsoft, take a look at it.

SQL Server Agent jobs may fail after you change the SQL Server Agent service startup account by using the Windows Service Control Manager

SSC link

Fixing ‘A required Privilege is not held by client’ Error

Regards,

tarr94 SSCommitted Points: 1684 More actions · Answer 2

Thanks Alejandro, but I already tried their suggested solution (see #4 above). Also, I never actually changed the SQL Server Agent service startup account, so I'm not sure it would have applied anyway.

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

What account is the snapshot SQL Agent job running as? It needs to be sa.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

tarr94 SSCommitted Points: 1684 More actions · Answer 4

Hi Michael, all of the SQL agent jobs associated with replication are running as sa.

tarr94 SSCommitted Points: 1684 More actions · Answer 5

Still struggling to get this working, still hoping for any other suggestions.

I've tried giving the AD account that runs the job db_owner permissions on all DBs just to see if lack of DB permissions had something to do with it, but no dice.

I've also made sure that the account that runs the SQL Server Agent jobs has access to the file share where the replication files go (the AD account that runs replication already has read/write/modify access). I'm running out of ideas.