sp_redirect_publisher uses the account credential used when the Publisher was added to the Remote distributor and stored in msdb.dbo.MSdistpublishers. So if a SQL authentication account was used such as 'sa' or 'distributor_admin,' replication may fail when the password expired or the account doesn't exist.
For example, if we execute the following, the login column should be blank
select * from msdb.dbo.MSdistpublishers
To fix it run the following
exec [sys].[sp_changedistpublisher] 'host\instance', 'security_mode', '1'
exec [sys].[sp_changedistpublisher] 'host\instance', 'security_mode', '1'
Now the login column will be blank
Otherwise we will get these errors when the password expired
Unable to query the redirected server '
,' for original publisher '\' and publisher database '' to determine the name of the remote server; Error 18456, Error message 'Error 18456, Level 14, State 1, Message: Login failed for user 'sa'.'.'.
Unable to query the redirected server ',' for original publisher '\' and publisher database '' to determine the name of the remote server; Error 18456, Error message 'Error 18456, Level 14, State 1, Message: Login failed for user 'distributor_admin'.'.'.
