We have observed a number of case where we use sql server replication on a clustered instance of sql server and when we try to failover from Node 1 to Node 2 the Log Reader agent may fail to start with the following error
Error messages:
The process could not execute ‘sp_MSpub_adjust_identity’ on ‘CRPSCSMSQ69V1PUB’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: <<http://help/MSSQL_REPL20011>>
An error occurred during decryption. (Source: MSSQLServer, Error number: 15466)
Get help: <<http://help/15466>>
The process could not execute ‘sp_MSpub_adjust_identity’ on ‘CRPSCSMSQ69V1PUB’. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: <<http://help/MSSQL_REPL22037>>
As seen above the root cause of the issue is the error
“An error occurred during decryption”
Which mean when the sql server instance failover to Node 2 it is not able to decrypt and run the stored procedure sp_MSpub_adjust_identity and hence it is not able to execute the stored procedure sp_MSpub_adjust_identity which is causing he Log Reader to fail on Node 2
It was not able to decrypt the stored procedure the Service Master Key on Node 2 was different then the Service Master Key on Node 1 . So one of the way to resolve the above error is to backup the service master key from Node 1 and restore the same on Node 2
BACKUP SERVICE MASTER KEY TO FILE = ‘path_to_file’ ENCRYPTION BY PASSWORD = ‘password’
RESTORE SERVICE MASTER KEY FROM FILE = ‘path_to_file’
DECRYPTION BY PASSWORD = ‘password’ [FORCE]
Another resolution can be to regenerate the service master key on Node 2 with Force option but in case when you fail back to Node 1 the Log Reager agent will fail again with the same error.
ALTER SERVICE MASTER KEY REGENERATE FORCE
However we regenerate the Service Master Key the Linked servers on the server can fail and we may have recreate the Linked servers or change the password for the Linked servers which can tedious and undesirable
The cause of the above issue occurs when we change the service account of the clustered instance of sql server using the services console. For a clustered instance of the sql server we recommend to change the service account using the sql server configuration manager because it performs a number of operation in the background which takes care of the service master key when you failover to Node 2.
So to resolve the above error we need to change the Service Account to another account using the Configuration Manager on Node 1 and try to failover on Node 2 and check whether the Log Reader Agent worked fine.
We can then change the Service Account back to the original account using the Configuration manager and try to failover again and the replication should work fine.