-- 了解透明数据加密 (TDE)
-- https://technet.microsoft.com/zh-cn/library/bb934049(v=sql.105).aspx
“透明数据加密”(TDE) 可对数据和日志文件执行实时 I/O 加密和解密。
这种加密使用数据库加密密钥 (DEK),该密钥存储在数据库引导记录中以供恢复时使用。
数据库文件的加密在页级执行。已加密数据库中的页在写入磁盘之前会进行加密,在读入内存时会进行解密。
使用TDE操作步骤:
1. 创建主密钥
2. 创建或获取由主密钥保护的证书
3. 创建数据库加密密钥并通过此证书保护该密钥
4. 将数据库设置为使用加密
-- 查询相关信息
select * from master.sys.key_encryptions
select * from master.sys.certificates
select * from sys.dm_database_encryption_keys where database_id=DB_ID('Temp')
select name,is_master_key_encrypted_by_server from sys.databases where is_master_key_encrypted_by_server=1
【测试一:分离和附加迁移】
use master;
go
--将相关信息删除
drop certificate Mycertificate;
go
drop master key;
go
-- 创建数据库主密钥
create master key encryption by password = N'Hello@MasterKey';
go
-- 创建以数据库主密钥加密的证书
create certificate Mycertificate with subject = 'Certificate to protect TDE key';
go
-- 备份证书
backup certificate Mycertificate
to file = N'D:\mycertificate.cer'
with private key (
file = N'D:\mycertificate_saleskey.pvk' ,
encryption by password = N'Hello@Mycertificate' );
go
use [Temp];
go
-- 创建用于以透明方式加密数据库的加密密钥
-- https://msdn.microsoft.com/zh-cn/library/bb677241(v=sql.105).aspx
create database encryption key
with
algorithm = aes_128
encryption by server certificate Mycertificate;
go
-- 启用数据库加密
-- https://msdn.microsoft.com/zh-cn/library/bb522682.aspx
alter database [Temp] set encryption on;
go
use master;
go
-- 分离数据库
exec master.dbo.sp_detach_db @dbname = N'Temp';
go
-- 移动数据文件到另一台服务器中:
-- 另一台服务器实例中:
use master;
go
-- 附加数据库(失败)
create database [Temp] on
( filename = N'C:\Database\Temp.mdf' ),
( filename = N'C:\Database\Temp_log.ldf' )
for attach ;
go
-- 创建新的数据库主密钥
create master key encryption by password = N'Hello@MasterKey';
go
-- 还原证书
create certificate Mycertificate
from file = N'C:\Software\mycertificate.cer'
with private key
(
file = N'C:\Software\mycertificate_saleskey.pvk' ,
decryption by password = N'Hello@Mycertificate'
);
go
-- 附加数据库(成功)
create database [Temp] on
( filename = N'C:\Database\Temp.mdf' ),
( filename = N'C:\Database\Temp_log.ldf' )
for attach ;
go
-- 删除测试信息
use master;
go
drop certificate Mycertificate;
go
drop master key;
go
【测试二:备份和恢复迁移】
-- 删除上次测试信息
use [Temp]
go
alter database [Temp] set encryption off;
go
drop database encryption key
go
use master;
go
drop certificate Mycertificate;
go
drop master key;
go
-- 创建数据库主密钥
create master key encryption by password = N'Hello@MasterKey';
go
-- 创建以数据库主密钥加密的证书
create certificate Mycertificate with subject = 'Certificate to protect TDE key';
go
-- 备份证书
backup certificate Mycertificate
to file = N'D:\mycertificate.cer'
with private key (
file = N'D:\mycertificate_saleskey.pvk' ,
encryption by password = N'Hello@Mycertificate' );
go
use [Temp];
go
-- 创建用于以透明方式加密数据库的加密密钥
create database encryption key
with
algorithm = aes_128
encryption by server certificate Mycertificate;
go
-- 启用数据库加密
alter database [Temp] set encryption on;
go
-- 备份数据库
backup database [Temp] to disk = N'D:\Temp.bak'
-- 拷贝mycertificate.cer、mycertificate_saleskey.pvk、Temp.bak到另一服务器
-- 在另一台服务器实例中:
use master;
go
-- 还原数据库(失败!)
RESTORE DATABASE [Temp]
FROM DISK = N'C:\Software\Temp.bak'
WITH FILE = 1,
MOVE N'Temp' TO N'C:\Database\Temp.mdf',
MOVE N'Temp_log' TO N'C:\Database\Temp_log.ldf',
NOUNLOAD, STATS = 10
/*
消息 33111,级别 16,状态 3,第 1 行
找不到指纹为 '0xC1E2F607576904047A0D739588B65B27D8878B92' 的服务器 证书。
消息 3013,级别 16,状态 1,第 1 行
RESTORE DATABASE 正在异常终止。
*/
-- 创建新的数据库主密钥
create master key encryption by password = N'Hello@MasterKey';
go
-- 还原证书
create certificate Mycertificate
from file = N'C:\Software\mycertificate.cer'
with private key
(
file = N'C:\Software\mycertificate_saleskey.pvk' ,
decryption by password = N'Hello@Mycertificate'
);
go
-- 还原数据库(成功)
RESTORE DATABASE [Temp]
FROM DISK = N'C:\Software\Temp.bak'
WITH FILE = 1,
MOVE N'Temp' TO N'C:\Database\Temp.mdf',
MOVE N'Temp_log' TO N'C:\Database\Temp_log.ldf',
NOUNLOAD, STATS = 10
-- 删除数据库加密
use [Temp];
go
alter database [Temp] set encryption off;
go
drop database encryption key
go
更多参考:将 TDE 保护的数据库移到其他 SQL Server