SQL Server Replication Error - Cannot generate SSPI context

By: Douglas Correa   |   Updated: 2016-09-27   |   Comments   |   Related: > Replication

Problem

After setting up SQL Server Transactional Replication via scripts, replication didn't work and showed the following error: "Target principal name is incorrect. Cannot generate SSPI context". What causes this issue and how do I fix it?

Here is the detail information for the Snapshot Agent.

Solution

The Snapshot Agent error doesn't include much information, but when you look at the Log Reader Agent error message you can see the error is "The logon attempt failed." as you can see below. That means the job is trying to execute, but doesn't have permission.

The first thing I did was look at the owner of the job (distributor_admin), but this looked okay.

The second thing I looked as was the Agent Security in the Publication Properties for both the Snapshot Agent and the Log Reader Agent.  As you can see below both were using the SQL Server Agent account which is not best security practice to follow.

In the security settings you can see the Snapshot Agent was set to use the SQL Server Agent account and the Connect to the Publisher setting was set to impersonate this account  To fix this I changed the Run under the following Windows account settings to use a Windows Account that had the necessary permissions such as rights to create the BCP files on disk.

After the change I stopped both agents and started them again. I ran the Snapshot Agent first and then the Log Reader Agent.  As you can see below the Snapshot Agent completed successfully and the Log Reader Agent is running.

Next Steps

• Refer to these other replication tips to get familiar with more replication concepts.

About the author

Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips