7

I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.

There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.

I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.

Is enabling TDE across an existing mirrored SQL Server setup possible?

Improve this question
2
  • Was there any error when restoring the SMK?
    – Sean Gallardy
    Dec 25, 2014 at 14:25
  • Did you also try the 2 steps mentioned by Richard Moulton in your second link? He said: For mirroring to work for me, the following extra two statements needed to be executed immediately after the Master Key was restored on the Mirror site: OPEN MASTER KEY DECRYPTION BY PASSWORD = '<strong password>'; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    – RLF
    Dec 25, 2014 at 15:00

3 Answers 3

Reset to default
1
+100

From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.

Improve this answer
1

If we understand encryption hierarchy, we can easily deduce that how to enable TDB without any interruption for a DB with DB mirroring.

  1. Check master key on both principle and mirroring servers.
  2. On principle server, if there is master key, we do nothing on this step, if not, create master key.
  3. On the principle server, create certificate protected by the master key.
  4. On the principle server, backup certificate, and then copy the certificate to mirroring server.
  5. On the mirroring server, if there is not master key, create it.
  6. On the mirroring server, restore certificate.
  7. On the principle server, create database encryption key for DB that will be enabled TDE.
  8. On the principle server, set encryption on.
Improve this answer
1

Actually, you can. I've tried this on SQL Server 2016 SP1, but I think it works for previous versions as well. The steps are described here . Remember that the mirror database will not show that encryption is enabled (but it is) until you do the fail over.

Improve this answer

Your Answer

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