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

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

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

--测试成功

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