patternsqlMinor
Why isn't database decryption working after failing over to the standby?
Viewed 0 times
afterwhythefailingworkingdecryptiondatabasestandbyisnover
Problem
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?
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?
Solution
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:
See also Move a TDE Protected Database to Another SQL Server
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:
See also Move a TDE Protected Database to Another SQL Server
Context
StackExchange Database Administrators Q#317066, answer score: 2
Revisions (0)
No revisions yet.