sqlserver 中使用透明数据加密(Transparent Data Encryption)加密数据库

在现实环境中，我们会遇到数据库备份磁盘丢失或者被盗的情况，此时恶意破坏方只需还原或者附加数据库即可浏览数据。接下来我将介绍透明数据加密(Transparent Data Encryption)的方法来保护你的数据库。如果数据A，使用TDE加密之后，即时恶意破坏方有了备份磁盘，也不能还原或者附加，这是因为数据库使用了被证书保护的加密数据密钥。

       1.什么是TDE

       透明数据库加密可对数据和日志文件执行实时I/O加密和解密.这种加密使用数据库加密密钥(Data Encrtyption Key),DEK是使用存储在服务器的master数据库中的证书保护的对称密码,或者是由EKM模块保护的非对称密钥.使用(Advanced Encryption Standard)或者3DES(Data Encryption Standard)加密算法来加密数据,并且无需更改现有应用程序.

       2.如何使用TDE

       1.创建主密钥

       2.创建或者获取由该密钥保护的证书

       3.创建数据库加密密钥并且由该证书保护该密钥

       4.将数据库设置为可加密

       下面通实例对TEST DB进行TDE加密

 

--Create a master key 

USE master 

GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '~qaz123[]\'; 

GO 

--Create a certificate 

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; 

GO 

--Create a database encryption key and protect it by the certificate 

USE TEST 

GO 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_128 

ENCRYPTION BY SERVER CERTIFICATE MyServerCert; 

GO 

--Set the database to use encryption 

USE TEST 

ALTER DATABASE TEST 

SET ENCRYPTION ON; 

GO 

当你完成上面的操作之后，接下来验证下加密数据的详细信息

 

SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys 

此时你会发现除了TEST DB 外还有tempdb DB，这是因为只有你对任一数据库加密的时候，也会自动的为tempdb 加密

       3.查看TDE是否起作用

       在另外一台Server上面使用bak文件来还原TEST数据库

 

RESTORE DATABASE TEST  

FROM DISK = 'E:\数据库\测试数据库\TEST.bak' 

WITH MOVE 'TEST' TO 'E:\\数据库\测试数据库\TEST.mdf', 

MOVE 'TEST_log' TO 'E:\数据库\测试数据库\TEST.ldf', 

NOUNLOAD, REPLACE 

执行完上面的代码之后

Message如下

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0x739E848FD2ECB7D945D39B7836BCD65B55D75D43'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

你使用附加数据库结果也是一样的，怎么解决这个问题了，使用添加同样的证书在这台Server

在添加该证书到这台Server之前，先在以前的Server上面备份证书到文件(其实这个操作我在添加证书的时候已经做啦)

代码如下

 

--backup certificate nad primary key to file 

USE master 

GO 

BACKUP CERTIFICATE MyServerCert TO FILE = 'D:\Test DB\Test\MyServerCert.cert' 

WITH PRIVATE KEY (   

FILE = 'D:\Test DB\Test\MyServerCertPrivate.key', 

ENCRYPTION BY PASSWORD = '~qaz123[]\'); 

GO 

备份成功之后，使用备份成功的证书，在目标Server上面创建证书

 

USE master 

GO 

CREATE CERTIFICATE MyServerCert 

FROM FILE = 'E:\学习\数据库\测试数据库\MyServerCert.cert' 

WITH PRIVATE KEY ( 

FILE = 'E:\学习\数据库\测试数据库\MyServerCertPrivate.key' 

, DECRYPTION BY PASSWORD = '~qaz123[]\') 

GO 

创建成功,查询下证书是否创建成功

 

SELECT * FROM sys.certificates where name = 'MyServerCert' 

现在再次还原数据库

 

RESTORE DATABASE TEST  

FROM DISK = 'E:\数据库\测试数据库\TEST.bak' 

WITH MOVE 'TEST' TO 'E:\\数据库\测试数据库\TEST.mdf', 

MOVE 'TEST_log' TO 'E:\数据库\测试数据库\TEST.ldf', 

NOUNLOAD, REPLACE 

此时数据库还原成功，大功告成!、