3

We added a new node (N2) to our SQL Server 2019 cluster. On existing N1, decryption queries works fine but when we failover to N2, decryption is failing. The database master key on N1 is encrypted by the service master key. Question is do I need to backup the service master key from N1 and restore it on N2 after failover in order for decryption to work on N2?

1

1 Answer 1

2

If you use transparent data encryption (TDE), the certificate or asymmetric key for creating and decrypting other keys must be the same on every server instance that hosts an availability replica for the availability group. For more information, see Move a TDE Protected Database to Another SQL Server.

AGs and TDE

So not the Service Master Key, but the certificate in master that protects the database encryption key:

enter image description here

See also Move a TDE Protected Database to Another SQL Server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.