Replication Error: “Login failed for user 'user account'. Reason: Attempting to use an NT account name with SQL Server Authentication.”



“Login failed for user 'user account'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: xx.xxx.x.xx]
Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent Server\Instance-Database-Database_Snap_Pub-1 failed. 
The replication agent had encountered an exception.  
Source: Replication  Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException  Exception Message:
 Login failed for user 'user account'.  Message Code: 18456”

One of my junior DBA was facing above error after setting Replication. He was trying seting up Replication first time in test server. So he was bit worried and without understanding complete error he called me in night to fix this.

After checking couple of things i saw error Which clearly says:

"Attempting to use an NT account name with SQL Server Authentication." 

Means he is using windows authentication account some where where he have to specify Sql server account. As i have already setup replication couple of times i know while setting up replication,  publisher \ Subscriber ask you for authentication either by impersonating process account or sql server login and DBA did mistake as shown below

Right click on Publisher property:


Click on Security Settings:



















And here In place of SQL server login he gave windows id\pwd

So we have to either give Sql server login  or select ablove option "By impersonating the process account"

This small solution fixed same and then we faced another issue mention here "Microsoft.SqlServer.Management.Smo.FailedOperationException Exception"

1 comment: