Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

SQL Server Replication Error - Cannot generate SSPI context


By:   |   Read Comments   |   Related Tips: More > 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?

SQL Server Snapshot Replication Error

Here is the detail information for the Snapshot Agent.

SQL Server Snapshot Replication Error Details
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.

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.


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools