SQL Server Replication – Snapshot Agent Login Fails

connectivityreplicationsql serversql-server-2008-r2transactional-replication

I have setup TRANSACTIONAL REPLICATION from ServerA to ServerB

ServerA = Primary Server = SQL Server 2008 R2 Enterprise
ServerB = Secondary Server = SQL Server 2008 R2 Standard Edition.

I have used a Domain Account for Snapshot Agent and Log Reader Agent, called SQLAgent.

The initial snapshot is initialised successfully, from there its all down hill.

Error Log:

Login failed for user 'ServerName\SQLAgent'. Reason: Token-based
server access validation failed with an infrastructure error. Check
for previous errors. [CLIENT: ]

Replication Monitor:

Publication Status: OK
Agents: for both Agents Snapshot and Log Reader Status shows it is Running…..
and Last Run Property says Starting Agent...

As it is obvious from the error message the Login is failing to get access to SQL Server, So just for test purpose I gave this SQLAgent account sysadmin role. Yet the error persist.

All the material I can find online, suggests Giving more permissions to login will solve the issue. But if sysadmin role hasnt solved it, I have kind of ran out of ideas now.

Can someone please suggest what is that I am missing?

how can I fix it?

Best Answer

I have used a Domain Account for Snapshot Agent and Log Reader Agent, called SQLAgent.

If you check this answer:

The process could not connect to Subscriber

you will see that the SQLAgent account must have a login on your ServerB and must be a db_owner on your subscription database there.

It does not need to be a sysadmin on your ServerA.

It has though, to have sufficient permissions on your snapshot folder wherever you decide that should be.

Related Question