SQLServer的TDE加密

TDE的主要作用是防止数据库备份或数据文件被偷了以后，偷数据库备份或文件的人在没有数据加密密钥的情况下是无法恢复或附加数据库的。

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:MSSQLCertificatemaster_server_cert.cer'
WITH PRIVATE KEY (
FILE = 'D:MSSQLCertificatemaster_server_cert.pvk' ,
ENCRYPTION BY PASSWORD = '^&*()0A';

--相应的，我们也备份一下数据库主密钥(master)
USE master;
--如果没有启用主密钥的自动解密功能
--OPEN MASTER KEY DECRYPTION BY PASSWORD = '^&*()0A';
BACKUP MASTER KEY TO FILE = 'D:MSSQLMasterKeymaster.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:UsersAdministratorDesktopmaster.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:UsersAdministratorDesktopmaster_server_cert.cer'
WITH PRIVATE KEY (FILE = 'C:UsersAdministratorDesktopmaster_server_cert.pvk',
DECRYPTION BY PASSWORD = '^&*()0A';
GO
--附加数据库
CREATE DATABASE db_encryption_test
ON PRIMARY
(
FILENAME=N'C:UsersAdministratorDesktopdb_encryption_test.mdf'
)
LOG ON
(
FILENAME=N'C:UsersAdministratorDesktopdb_encryption_test_log.ldf'
)
FOR ATTACH ;
GO

--测试成功

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