SQL Server 2008引入透明数据加密(Transparent Data Encryption),
它允许你完全无需修改应用程序代码而对整个数据库加密。当一个用户数据库可
用且已启用TDE时,在写入到磁盘时在页级实现加密。在数据页读入内存时解密
。如果数据库文件或数据库备份被盗,没有用来加密的原始证书将无法访问。
TDE Demo:
--在还原数据库之前必须还原证书,否则数据无法被还原 --错误提示:找不到指纹为'XXX' 的服务器证书。 use master; GO --还原证书DB_TDE_cert CREATE CERTIFICATE DB_TDE_cert FROM FILE = 'E:DB_TDE_cert.cer' WITH PRIVATE KEY (FILE = 'E:DB_TDE_cert.pvk', DECRYPTION BY PASSWORD = 'Tde@sql123') GO --还原数据库 RESTORE DATABASE [TestDB] FILE = N'TestDB' FROM DISK = N'E:TestDB.BAK' WITH FILE = 1, MOVE N'TestDB' TO N'E:DB\TestDB.mdf', MOVE N'TestDB_log' TO N'E:DB\TestDB_0.LDF', NOUNLOAD, STATS = 10 GO
还原被TDE加密的数据库备份
use master GO SELECT name,is_master_key_encrypted_by_server FROM sys.databases; --查看master数据库下的密钥信息 SELECT * FROM sys.symmetric_keys; --创建证书用来保护数据库加密密钥(DEK) CREATE CERTIFICATE DB_TDE_cert WITH SUBJECT = N'DB_TDE_cert'; --备份证书 BACKUP CERTIFICATE DB_TDE_cert TO FILE = 'D:DB_TDE_cert.cer' WITH PRIVATE KEY ( FILE = 'D:DB_TDE_cert.pvk' , ENCRYPTION BY PASSWORD = 'Tde@sql123' ); ---创建测试DB IF(DB_ID('TestDB') IS NOT NULL) BEGIN DROP DATABASE TestDB END GO CREATE DATABASE TestDB GO USE TestDB; GO --创建由master_server_cert保护的DEK 数据库加密密钥(对称密钥) CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE DB_TDE_cert; GO --将数据库改成单用户模式 use master GO ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO --开始数据加密 ALTER DATABASE TestDB SET ENCRYPTION ON; GO --将数据库设置为多用户模式 ALTER DATABASE TestDB SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO --查看数据库加密模式 SELECT DB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;
管理删除TDE
USE TestDB GO --修改加密算法 ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_128 Go USE master GO --创建新的证书 CREATE CERTIFICATE TDE_Server_Certificate_V2 WITH SUBJECT = 'Server-level cert for TDE V2' GO USE TestDB GO --用新证书修改DEK ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2 --移除数据库透明加密 ALTER DATABASE DB_Encrypt_Demo SET ENCRYPTION OFF GO --移除TDE后,可以删除DEK USE TestDB GO Drop DATABASE ENCRYPTION KEY GO