/*
Title:TDE加密
Author:浪客
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise
Description:请在非生产环境下测试
*/

USE [master];
GO

--查看master数据库是否被加密
SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

--创建master数据库下的主数据库密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'浪客!@#$%^&*()0A';

--查看master数据库下的密钥信息
SELECT * FROM sys.symmetric_keys;

--创建证书用来保护 数据库加密密钥 (DEK)
CREATE CERTIFICATE master_server_cert WITH SUBJECT = N'Master Protect DEK Certificate';

IF DB_ID('db_encryption_test') IS NOT NULL
    DROP DATABASE db_encryption_test

--创建测试数据库
CREATE DATABASE  db_encryption_test;
GO

USE db_encryption_test;

--创建由master_server_cert保护的DEK 数据库加密密钥 （对称密钥）
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE master_server_cert;
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数据库的主数据库密钥加密了.
USE master;
BACKUP CERTIFICATE master_server_cert TO FILE = 'D:\MSSQL\Certificate\master_server_cert.cer' 
    WITH PRIVATE KEY ( 
    FILE = 'D:\MSSQL\Certificate\master_server_cert.pvk' , 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A' );

--相应的，我们也备份一下数据库主密钥(master)
USE master;
--如果没有启用主密钥的自动解密功能
--OPEN MASTER KEY DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
BACKUP MASTER KEY TO FILE = 'D:\MSSQL\MasterKey\master.cer' 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
GO 

--生产环境下，设置成单用户在运行加密
ALTER DATABASE db_encryption_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--备份成功以后，开启TDE 加密
ALTER DATABASE db_encryption_test SET ENCRYPTION ON;
GO

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

--查看db_encryption_test数据库是否被加密  encryption_state:3 TDE加密了
SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;
/*
发现tempdb也被加密了。MSDN解释是：如果实例中有一个数据库启用了TDE加密，那么tempdb也被加密
*/

--接下来,找另外一台机器或者实例来测试,如果数据文件被盗走了,防止附加的测试.
USE master;
EXEC sp_detach_db N'db_encryption_test';
GO

--将文件QQ发到了另外的机器,我同事 CL .


USE master;
--我先在他机器还原了MASTER KEY (他原机器master库无master key)
RESTORE MASTER KEY 
    FROM FILE = 'C:\Users\Administrator\Desktop\master.cer' 
    DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A' 
    ENCRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';
GO

--如果没有自动加密
OPEN MASTER KEY DECRYPTION BY PASSWORD=N'浪客!@#$%^&*()0A';
--创建证书
CREATE CERTIFICATE master_server_cert 
    FROM FILE = 'C:\Users\Administrator\Desktop\master_server_cert.cer' 
    WITH PRIVATE KEY (FILE = 'C:\Users\Administrator\Desktop\master_server_cert.pvk', 
    DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A');
GO 
--附加数据库
CREATE DATABASE db_encryption_test 
ON PRIMARY 
(
    FILENAME=N'C:\Users\Administrator\Desktop\db_encryption_test.mdf'
)
LOG ON 
(
    FILENAME=N'C:\Users\Administrator\Desktop\db_encryption_test_log.ldf'
)
FOR  ATTACH ;
GO

--测试成功,GG,GL

--关闭数据库联接
CLOSE MASTER KEY