创建SQLSERVER数据库TDE（透明数据加密）的TSQL

作者：[51ak ]

use master

SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

--查看master数据库下的密钥信息

SELECT * FROM sys.symmetric_keys;

--创建证书用来保护数据库加密密钥(DEK)

CREATE CERTIFICATE db_sql_error_cert WITH SUBJECT = N'db_sql_error_cert';

IF DB_ID('db_sql_error2') IS NOT NULL

DROP DATABASE db_sql_error2

USE db_sql_error;

--创建由master_server_cert保护的DEK 数据库加密密钥（对称密钥）

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE db_sql_error_cert;

--执行上语句以后出现：

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 db_sql_error_cert TO FILE = 'D:\db_sql_error_cert.cer'

WITH PRIVATE KEY (

FILE = 'D:\db_sql_error_cert.pvk' ,

ENCRYPTION BY PASSWORD = 'Wokofo2' );

--相应的，我们也备份一下数据库主密钥(master)

USE master;

--如果没有启用主密钥的自动解密功能

--OPEN MASTER KEY DECRYPTION BY PASSWORD = '浪客!@#$%^&*()0A';

BACKUP MASTER KEY TO FILE = 'D:\master.cer'

ENCRYPTION BY PASSWORD = 'Wokofo2';

--生产环境下，设置成单用户在运行加密

ALTER DATABASE db_sql_error SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--备份成功以后，开启TDE 加密

ALTER DATABASE db_sql_error SET ENCRYPTION ON;

--设置多用户访问

ALTER DATABASE db_sql_error SET MULTI_USER WITH ROLLBACK IMMEDIATE;

--查看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;

器还原了MASTER KEY (原机器master库无master key)

RESTORE MASTER KEY

FROM FILE = 'E:\master.cer'

DECRYPTION BY PASSWORD = 'Wokofo2'

ENCRYPTION BY PASSWORD = 'Wokofo2';

--如果没有自动加密

OPEN MASTER KEY DECRYPTION BY PASSWORD=N'Wokofo2';

--创建证书

CREATE CERTIFICATE master_server_cert

FROM FILE = 'e:\db_sql_error_cert.cer'

WITH PRIVATE KEY (FILE = 'e:\db_sql_error_cert.pvk',

DECRYPTION BY PASSWORD = 'Wokofo2');

CLOSE MASTER KEY