Replication Agent fails with “An error occurred during decryption.”
SQL Server Escalation Services
While helping a customer on a Replication issue, we encountered an very unusual error message.
An error occurred during decryption. There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_distributor'. Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)
There are some key words in this error ('distributor_admin', 'repl_distributor') which lead us down the path of failure in the Linked Server used by Replication Agent to connect back to the Distributor in order to update Agent status. For testing, we tried to recreate the same linked server. The sp_addlinkedserver command failed with the same error. This confirms where the agent was failing, but now why.
CREATE LOGIN [distributor_admin] WITH PASSWORD=N'xyz', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON EXEC sys.sp_addsrvrolemember @loginame = N'distributor_admin', @rolename = N'sysadmin' EXEC master.dbo.sp_addlinkedserver @server = N'repl_distributor', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'repl_distributor',@useself=N'False',@locallogin=NULL,@rmtuser=N'distributor_admin',@rmtpassword=N'xyz'
Further investigation uncovered the SQL Agent Services was running under a Domain Account. From the SQL Errorlog we discovered a “Service Principal Name (SPN)” could not be created. We know the SPN is used for delegation to allow one domain user to Impersonate another domain user using Kerberos Authentication.
Our solution was to log on to the Distributor using the same Logon account as the SQL Agent and SQL Service account. We also used the same SQL Agent Service Account across all 3 servers (publisher, distributor, subscriber) used in Replication. While not the most secure, it did simplify authentication and allow Replication Agent to connect without unexpected errors.