[转载]SQL Server 2008 安全性——透明数据加密(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

—执行上语句以后出现：
/*
Warning: The certificate used for encrypting the database encryption key has not been backed up.

—相应的，我 们也备份一下数据库主密钥(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