SQL Server 2008 安全性——透明数据加密(TDE)
SQL Server 2008 安全性——透明数据加密(TDE)
在SQL Server 2005的安全性来说，做的不够好，比如EKM这种可扩展的外部密钥管理就不支持，包括对数据库文件本身的加密。
SQL Server 2008中增加了透明数据加密TDE，TDE使用DEK 数据库加密密钥对数据文件进行加密，包括IO操作，加入内存等都是存在加密和解密操作的。DEK本身是受master数据库下的证书保护的，当然也支持EKM模块包含。我们对数据文件可以使用标准的DES 和AES加密。
在某些特定场合，比如我们的服务器发生盗窃行为的时候，一些关键性数据库不被恶意附加挖掘其中的价值数据，除了使用硬件级别的文件格式加密。SQL Server 用TDE来保护文件安全。
我主要通过T-SQL 脚本的形式描述 整个加密以及分离、附加数据库的流程。
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
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
通常我们使用某种新特性的时候，还会关注他对SQL Server其他组件的影响，包括复制服务以及数据库镜像以及日志传送。复制是通过标志事务日志传送标识日志的方式同步数据，当时通过bcp生成的快照文件，不会采用TDE加密数据，这种存在传输信道的 我们通常使用OS的IpSec，SSL,TSL方式来加密连接，这种在以后的文章会向大家提到。而数据镜像和日志通过传送日志的方式，所以传输过程中的日志文件是加密的。
/Files/bhtfg538/MSSQL/Security/Encryption/encry_1.txt
