How do you update replication secrets in a database?

Question

I have a Dev server running SQL Server 2019 Enterprise configured for replication. I patched the server from SQL 2019 CU 27 to CU 32. After restoring the database being used for replication, I ran a script to re-establish the replication.

When I ran the sp_addsubscription step it threw the warning below:

WARNING: The database 'distribution' does not contain database master key. Create a database master key and then update all replication secrets in this database. For more information, see https://aka.ms/sql-tr-dmk-warning-troubleshooting.

I understand how to create a master key, but I am not clear regarding the phrase "update all replication secrets in this database". I tried the link in the error message, but the page it goes to does not provide any guidance regarding the warning.

Answer 1

I guess that they want you to create a master key and re-enter all accounts and their passwords, so they will be store encrypted. If you trawl around distribution, you may find them unencrypted somewhere.

Interesting, I recently set up replication on SQL 2022 CU18, and there is no master key in the distribution database. I did not notice any warnings, but then again I used the wizard. (I only set up replication for a lab test, to help with another forum question.)