After patching our 3-node production environment, we lost the credential store entries

Question

1. What is the best way to restore the Credentials Store entries from a backup?

2.How can the DMK become disassociated with the SMK?

3. How did the control table entries disappear?  Is there a history log for control entries?
4. The fact that the control entries were missing…does it impact the DMK->SMK association?
5. We need to write a policy report job to let DBAs know if these control entries are missing AND if the DMK-SMK association is broken.   What queries can we run?

Answer 1

The general issue here is that the DMK is protected by the SMK at the server level. That SMK is protected by DPAPI at the OS level, and those entries are owned by the service account.
So the most common prolbem is that the service account changes for some reason, and the new account cannot access the old DPAPI entries.
What you can do to avoid the issues in the first place, is to backup the service master key (https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver16).
After completing the actions that caused the chain to break, you can restore the service master key to the server (https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/restore-the-service-master-key?view=sql-server-ver16).
To view either service or database master keys that are present, you can query sys.symmetric_keys.