Migrating or Recovering a TDE protected Database

存储架构 2018-02-15

When encrypting a database with Transparent Data Encryption(TDE), a vital consideration is to make sure we are prepared for the scenario when something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?

In the ordinary recovery scenario, we would make sure that we have appropriate backups of our database, and that they (or copies of them) are stored off the server itself so that we can access them in case of a failure.

If you have followed the instructions in Setting up Transparent Data Encryption (TDE)
then you will also have a backup of the certificate and private key used to protect the database encryption key used by TDE e.g:

BACKUP CERTIFICATE MyTDECert   
TO FILE = 'C:TestMyTDECert'  
WITH PRIVATE KEY   
(  
    FILE = 'C:TestMyTDECert_PrivateKeyFile',  
    ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7'  
);  
GO  

You need to make sure that these are also stored securely off the server and that you have kept the password you used somewhere you can access it – but not so accessible that unauthorised users can get it otherwise you are defeating the object of TDE somewhat.

In summary you need:

  • The database backup file
  • The backup of the certificate
  • The backup of the private key
  • The password used to encrypt the private key

Armed with those objects, you are equipped to restore your database to another SQL Instance.

Working on the new SQL instance, the steps are straightforward.

Create a Database Master Key if one doesn’t exist

USE MASTER;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';

Note that this will be a new and unique database master key, it will not be the same as the one you had on your old instance – and you don’t need to use the same password to protect it.

Restore the Certificate and Private Key

On the new SQL instance you need to restore the certificate and private key into the master database:

USE MASTER;

CREATE CERTIFICATE MyTDECert
FROM FILE = 'C:TestMyTDECert.cer'
WITH PRIVATE KEY 
( 
   FILE = 'C:TestMyTDECert_PrivateKeyFile.pvk',
   DECRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7' 
);

This will decrypt your key using the password supplied, and then re-encrypt it using the database master key you created. Then the certificate and its key will be stored in the master database on your new SQL instance.

If you’ve done something wrong, it’s entirely possible you may get an error at this stage, commonly:

Msg 15208, Level 16, State 6, Line 56

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

If you’re confident that all details specified are correct, and that the certificate and private key were backed up properly, then the most likely issue is that the current SQL instance doesn’t have access to the file path you’ve placed the files in.

Restore the Database

Once you’ve completed the previous steps you are ready to restore the database(s) from the backup(s). You do that as you would restore any other database. Potentially as simple as:

RESTORE DATABASE TestTDE FROM DISK = 'C:TestTestTDE.bak';

Then you’ll find you can access your database and view data without any issues. At this point you can celebrate – you are done.

You only get a problem if you haven’t set up the certificate and key correctly, or you have the wrong one:

Msg 33111, Level 16, State 3, Line 2

Cannot find server certificate with thumbprint ‘0x682C8797633B9AD8875967502861CCAE33ECAD66’.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

So what do I do if I can’t restore the certificate?

Of course you’re never going to run into this problem because you’ve followed all the instructions carefully, and you’ve made sure you have your certificate and key backups – and the password used to protect them.

Let’s say for the sake of argument though that you’ve taken ownership of an environment that hasn’t been so carefully managed. You’ve had a server failure and there are no certificate and key backups – or they exist but no-one knows the password.

Is your data lost forever? Or rather is it now so safe that no-one can access it – even those who are supposed to? Don’t panic just yet, we’ll look at a technique you may be able to use to recover your data in my next blog post.

Other articles on TDE:

What is Transparent Data Encryption?

Setting up Transparent Data Encryption (TDE)

Encrypting an existing database with TDE

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

SQLServerCentral

责编内容by:SQLServerCentral (源链)。感谢您的支持!

您可能感兴趣的

Event sourcing vs CRUD Event sourcing is gaining more and more attention. This is partly due to the increasing interest in domain-driven design (DDD) and CQRS, to which eve...
Android—Room 数据库迁移(Migration) Android-Room数据库(介绍) Android-Room数据库-多表查询(Relationships) Android—Room自定义类型(TypeConverter) 前言 如果大家看...
Youtube 4k video not list from youtube Youtube videos not listed in the app purchase It's possible in youtube to add videos called unlisted videos, so no one can searc...
Java高并发秒杀系统【观后总结】 项目简介 在慕课网上发现了一个JavaWeb项目,内容讲的是高并发秒杀,觉得挺有意思的,就进去学习了一番。 记录在该项目中学到了什么玩意.. 该项目源码对应的gitHub地址(由观看其视频的人编写,并非视频源代码): https://github.com/codingXiaxw/sec...
事务与一致性:刚性or柔性? 在高并发场景下,分布式储存和处理已经是常用手段。但分布式的结构势必会带来“不一致”的麻烦问题,而事务正是解决这一问题而引入的一种概念和方案。我们常把它当做并发操作的基本单位。 从MySQL事务说起(刚性事务) 提到事务,脑海里第一个反应当然是数据库里的Transaction了。紧接着...