Msg 21892, Level 16, State 1

ecently I posted Repltalk a walkthrough setting up Publisher, Distributor, and Subscriber each in AlwaysOn Availability groups. In this series of postings, I’m going to break then, show you what went wrong, and how to fix it.
Let’s start with error message when attempting to validate a redirected publisher.

USE distribution;
GO

DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
   @original_publisher = 'pub1',
   @publisher_db = 'AW',
   @redirected_publisher = @redirected_publisher output;

SELECT @redirected_publisher
GO

Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60 [Batch Start Line 3] Unable to query sys.availability_replicas at the availability group primary associated with virtual network name ‘PubAGListener’ for the server names of the member replicas: error = 18456, error message = Error 18456, Level 14, State 1, Message: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’..’,

This occurs when sp_hadr_validate_replica_hosts_as_publishers creates a dynamic linked server to validate the failover publishers and you’re not connected directly to the Distributor or Distributor Availability Group Listener. For example, you’ve made a Remote Desktop Connection to the Publisher, then used SSMS to query the Distributor. Now through that remote connection validate_replica is attempting to make a linked server back to validate replica. This temporary linked server is unable to pick up the user’s security credential leading to login failed message.

The Fix:

Resolution, open RDP connection directly on the Distributor, or Primary, if distributor role is in an Availability Group, the Distribution Primary when executing publisher redirection check.

Chris Skorlinski
Microsoft SQL Server Escalation Services