SQL Server Replication Error - Cannot generate SSPI context

By:   |   Comments   |   Related: > Replication


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?

SQL Server Snapshot Replication Error

Here is the detail information for the Snapshot Agent.

SQL Server Snapshot Replication Error Details

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.

SQL Server Replication Log Reader Agent Error Details

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

SQL Server Agent Replication Job

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.

SQL Server Replication Publication Security

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.

SQL Server Replication Publication Security Details

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.

SQL Server Replication Running Successfully
Next Steps
  • Refer to these other replication tips to get familiar with more replication concepts.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Douglas Correa 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

Comments For This Article

get free sql tips
agree to terms