–下面的两个例子将展示如何启用和维护透明数据加密
–示例一、启用透明加密(TDE)
USE Master
GO
–删除旧主密钥
–Drop MASTER KEY
–GO
–创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD =’B19ACE32-AB68-4589-81AE-010E9092FC6B’
GO
–创建证书,用于透明数据加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = ‘Server-level cert for TDE’
GO
USE [pratice]
GO
–第一步：现在开始透明加密
CREATE DATABASE ENCRYPTION KEY–创建数据库加密密钥
WITH ALGORITHM = TRIPLE_DES_3KEY–加密方式
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate–使用服务器级证书加密
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.
*/
–第二步：打开加密开关
ALTER DATABASE [pratice] SET ENCRYPTION ON
GO
–查看数据库是否加密
SELECT is_encrypted FROM sys.databases
WHERE name = ‘pratice’
–注意：一旦在数据库应用了加密，应该立刻备份服务器级证书！
–没有加密dek的证书，该数据库将无法打开，附加到别的服务器也无法使用，数据库文件亦不会被Hack。
–如果一个DBA想要合法地将数据库从一个SQL Server实例移动到另一个SQL Server实例，
–那么她应该首先备份服务器级证书，然后在新的SQL Server实例中创建证书。
–此时可以合法地备份、还原数据库或附加数据及日志文件。
–示例二、管理和移除透明加密(TDE)
USE [pratice]
GO
–修改加密算法
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go
SELECT DB_NAME(database_id) databasenm,
CASE encryption_state
WHEN 0 THEN ‘No encryption’
WHEN 1 THEN ‘Unencrypted’
WHEN 2 THEN ‘Encryption in progress’
WHEN 3 THEN ‘Encrypted’
WHEN 4 THEN ‘Key change in progress’
WHEN 5 THEN ‘Decryption in progress’
