HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Why isn't database decryption working after failing over to the standby?

Submitted by: @import:stackexchange-dba··
0
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?

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

Context

StackExchange Database Administrators Q#317066, answer score: 2

Revisions (0)

No revisions yet.