SQLSERVER2012透明加密（TDE ）问题

浏览数：28 / 时间：2015年06月12日

最近需要用到加密功能，找到了TDE，生成证书才发现安装的SQL版本不是企业版，目前似乎只有SQL2008以上的企业版才支持，可以升级吗，试了下自动升级，OK 提示成功，再安装依然提示版本不对select @@version 才发现版本没有变。

重装吧，找到控制面板卸载2012 ，奇怪竟然卸载不了了。我把sql组建都卸载了可以数据库依然能用，怪哉，是升级的原因？

重做系统吧

等待中........

系统做好先安装SQL2012 企业版

OK 这下可以生成证书了，加密也顺利成功，下面的是加密过程

USE master;
GO
--查看master数据库是否被加密
--SELECT name,is_master_key_encrypted_by_server FROM sys.databases; --1表示加密
--每一个数据库只能有一个数据库主密钥
--删除旧的密钥证书
drop CERTIFICATE MyServerCert
Drop master Key 
GO
--在master数据库中创建一个master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd‘;
--查看master数据库下的密钥信息
--SELECT * FROM sys.symmetric_keys
--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate‘;

--IF DB_ID(‘TDEDemo‘) IS NOT NULL
-- DROP DATABASE TDEDemo
go
USE demo1
GO 
--创建数据库加密key，使用MyServerCert这个证书加密
--Drop DATABASE ENCRYPTION KEY
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
go
--执行上语句以后出现：
/*
Warning: The certificate used for encrypting the database encryption key has not been backed up. 
You should immediately back up the certificate and the private key associated with the certificate. 
If the certificate ever becomes unavailable or if you must restore or attach the database on another server, 
you must have backups of both the certificate and the private key or you will not be able to open the database.
*/
/*
警告: 用于对数据库加密密钥进行加密的证书尚未备份。应当立即备份该证书以及与该证书关联的私钥。
如果该证书不可用，或者您必须在另一台服务器上还原或附加数据库，则必须对该证书和私钥均进行备份，否则将无法打开该数据库。
*/

--提示你，立刻备份证书;这里备份证书,不比制定加密私钥的对称密钥了.因为他的密钥是通过master数据库的主数据库密钥加密了.
--相应的，我们备份一下数据库主密钥(master)

USE master;
GO
--如果没有启用主密钥的自动解密功能
--OPEN MASTER KEY DECRYPTION BY PASSWORD =‘P@ssw0rd‘;
BACKUP MASTER KEY TO FILE=‘D:\Cers\master_key‘ ENCRYPTION BY PASSWORD=N‘P@ssw0rd‘--备份主数据库密钥DMK
BACKUP CERTIFICATE MyServerCert TO FILE = ‘D:\Cers\master_cer‘ --备份数据库证书
    WITH PRIVATE KEY ( 
    FILE = ‘D:\Cers\master_cert.pvk‘ , --自动生成pvk
    ENCRYPTION BY PASSWORD = ‘P@ssw0rd‘ );
GO 

--生产环境下，设置成单用户在运行加密
--ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master
GO
BACKUP DATABASE demo1 TO DISK=N‘D:\demo1.bak‘ 

/*
经测试 OFF ON 有时会提示失败
消息 33122，级别 16，状态 1，第 1 行
此命令要求数据库 ‘TDEDemo‘ 上有数据库加密扫描。但是，自上次挂起日志备份的加密扫描以来，数据库已经更改。请提取一个日志备份，然后重试该命令。
消息 5069，级别 16，状态 1，第 1 行
ALTER DATABASE 语句失败。
*/
--查看数据库是否被加密  encryption_state:3 TDE加密了
SELECT DB_NAME(database_id) as dbname,encryption_state FROM sys.dm_database_encryption_keys;

--备份成功以后，开启TDE 加密 
ALTER DATABASE demo1 SET ENCRYPTION ON
--关闭TDE 加密
ALTER DATABASE demo1 SET ENCRYPTION OFF;

--设置多用户访问
--ALTER DATABASE dbname SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

奇怪的是我多次开启TDE ON OFF 后过一阵子会出现下列错误，出现该错误后无法开启或关闭TDE，重建数据库才好，不知道是什么原因

消息 33122，级别 16，状态 2，第 1 行
此命令要求数据库 ‘demo1‘ 上有数据库加密扫描。但是，自上次挂起日志备份的加密扫描以来，数据库已经更改。请提取一个日志备份，然后重试该命令。
消息 5069，级别 16，状态 1，第 1 行
ALTER DATABASE 语句失败。

加密以后是还原的问题，如果服务器出现问题还原到其他机器是经常用到的，经测试发现还原到非企业版是无法正常使用的，也就是必须是企业版才能支持被TDE保护的数据库（不知道理解的对不对），以下是还原代码

USE master;
go
--查看master数据库是否被加密
--SELECT name,is_master_key_encrypted_by_server FROM sys.databases; --1表示加密
--GO
--如果存在删除master数据库下的主数据库密钥
--DROP MASTER KEY;
/**
--网上有说用该方法恢复主密钥的 但经测试该方法 无法正常的恢复主密钥 不知道是否我的版本问题 测试版本SQL2012 企业版
RESTORE MASTER KEY 
FROM FILE = ‘D:\Cers\master_key‘ 
DECRYPTION BY PASSWORD = ‘P@ssw0rd‘ 
ENCRYPTION BY PASSWORD = ‘P@ssw0rd‘;
**/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd‘;--创建主密钥 可以和要恢复的不同 
GO
--创建证书
--drop CERTIFICATE MyServerCert
CREATE CERTIFICATE MyServerCert 
    FROM FILE = ‘D:\Cers\master_cer‘ 
    WITH PRIVATE KEY (FILE = ‘D:\Cers\master_cert.pvk‘, 
    DECRYPTION BY PASSWORD = ‘P@ssw0rd‘);--和要恢复的密钥相同PWD 其他文件要保持路径一致
GO 

---数据库还原操作
--如果数据库逻辑文件名无法确定可以使用下面的语句进行查看那
--restore filelistonly from DISK=N‘D:\Cers\Demo1.bak‘
--如果物理路径不一致可以使用move语句进行修改
RESTORE DATABASE demo
FROM DISK=N‘D:\Cers\Demo1.bak‘
with move ‘demo1‘     to ‘d:\data\demo.mdf‘
    ,move ‘demo1_log‘ to ‘d:\data\demo.ldf‘
GO