Transactional Replication SQL agent job failes to generate the snapshot agent due to required privilege is not held by the client

Eben Earnest 41 Reputation points
2021-09-08T08:29:09.467+00:00

Hello,

SQL Edition and version : 2016 & ENT
Windows Edition : win 2019

we are stuck at an specific issue where by the Replication log reader agent job that tries to run the replication snapshot agent is failing with below error

we are using dedicated windows service accounts to configure the replication & the accounts used are provided with SYSADMIN rights on the database in addition to db_owner rights on Publisher and distribution database

Executed as user: ENT\XXXXXXX. A required privilege is not held by the client. The step failed.

since sysadmin is the super user rights we aren't certain if this is still an database privilege issue that stalls or since we are using windows 2019 /SQL 2016 advanced versions if there are any thing in additional to be provided on privilege area

Tried several blogs to find an solution but we are stumped we have given this application windows account the rights on local security policy for "Replace an process level token" , tried to run the SQL service under local system account and make it run under the windows service account and all to no avail its not working, limelight is the snapshot gets working when run under the SQL agent service windows account , other than the sysadmin rights for this SQL agent account the only difference we see is the SQL agent account has been added under local security policy

Allow log on locally
Lock pages in memory
Log on as a service
Perform Volume maintenance tasks

Ensured with systems engineer that application windows service account also has rights to Read and Write access to the Snapshot folder

& this same application service account is able to work well with same level of rights on DB on SQL2014/Windows 2012R2 DB server.

Command used :

-Publisher [XXXXXX] -PublisherDB [XXXXX] -Distributor [XXXXX] -DistributorSecurityMode 1 -Continuous -EncryptionLevel 1

as per msdn we see as in below so feel this couldnt be an TLS issue as the same command works when run under SQL agent service account but not under this application service account
Specifies that TLS is used, but the agent does not verify that the TLS/SSL server certificate is signed by a trusted issuer.

Let me know what we are missing any help would be valuable

Regards and Wishes
Eben

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,696 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Eben Earnest 41 Reputation points
    2021-09-08T09:45:28.09+00:00

    Just to add the credentials and the proxy are all set for the application account perfect & the SQL agent job also lists the proxy created on drop down too.. xp_cmd shell is also enabled

    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-09-08T12:34:29.103+00:00

    That error is a SQL Agent error, not really related to replication.

    The error is always related to rights of the SQL Agent service account, or the proxy not being setup correctly.

    If you are running proxy accounts, you must setup proxy for all the "Replication" proxies for this to work properly.

    Please see:
    https://www.sqlservercentral.com/articles/fixing-%E2%80%98a-required-privilege-is-not-held-by-client%E2%80%99-error

  3. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-09T06:27:43.053+00:00

    Hi @Eben Earnest ,

    This problem occurs because the Windows Service Control Manager cannot grant the required permissions to run agent jobs to the new domain account.

    To resolve the problem, follow these steps:
    -Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
    -Stop and then start the SQL Server Agent service.
    -Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
    -Stop and then start the SQL Server Agent service.
    You can also reset the password of the SQL Server Agent service account in SQL Server Configuration Manager.

    For more information, please see
    SQL Server Agent jobs may fail after you change the SQL Server Agent service startup account by using the Windows Service Control Manager.

    Best regards,
    Seeya

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments