SQL SERVER 2008 使用TDE加密和解密

SQL SERVER 2008 加密和解密，这样的文件在互联网上不胜枚举，本文的寓意还是一样，一为记录，二可以为开发者提供在实现过程中的注意事项。

TDE: Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008

1.    If it does not already exist, create a database master key (DMK) for the master database. Ensure that the database master key is encrypted by the service master key (SMK).

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;

                

2.    Either create or designate an existing certificate for use as the database encryption key (DEK) protector. For the best security, it is recommended that you create a new certificate whose only function is to protect the DEK. Ensure that this certificate is protected by the DMK.

CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;

 

3.    Create a backup of the certificate with the private key and store it in a secure location. (Note that the private key is stored in a separate file—be sure to keep both files). Be sure to maintain backups of the certificate as data loss may occur otherwise.

BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’

   WITH PRIVATE KEY (

         FILE = ‘path_to_private_key_file’,

         ENCRYPTION BY PASSWORD = ‘cert password’);

 

4.    Optionally, enable SSL on the server to protect data in transit.

Perform the following steps in the user database. These require CONTROL permissions on the database.

5.    Create the database encryption key (DEK) encrypted with the certificate designated from step 2 above. This certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database.

CREATE DATABASE ENCRYPTION KEY

   WITH ALGORITHM = AES_256

   ENCRYPTION BY SERVER CERTIFICATE tdeCert

 

6.    Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously.

ALTER DATABASE myDatabase SET ENCRYPTION ON

View Code

简而言之，就是先创建自己的Master key, 然后创建CERTIFICATE证书，然后备份证书，接着关联Master Key 和CERTIFICATE，然后就是设置数据库SET ENCRYPTION ON.
按照步骤下来，你就可以利用下面的这段话来看，是否加密成功。当然，你也可以一开始就去先做判断。

---查看db_name是否加密，------------
---- is_encrypted=1表示加密---------
---- is_encrypted=0表示未加密-------
SELECT is_encrypted FROM sys.databases 
WHERE name ='db_name'

View Code

解密：

　　删除Master key

ALTER DATABASE db_name;
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a 
value of  1 in the query below. */
SELECT encryption_state
FROM sys.dm_database_encryption_keys;
GO
USE db_name;
GO
DROP DATABASE ENCRYPTION KEY;
GO

　　在删除Master key的时候，首先要设置SET ENCRYPTION OFF，然后使用DROP DATABASE ENCRYPTION KEY删除，如果数据库的数据或是结构有变化的话，SQL Management Studio会提示让你去备份数据库日志文件Backup log AdventureWorks2012 set disk="物理地址"。

　　删除CERTIFICATE

完成之后，在SQL Management Stud中测试时，无论附加还是还原，需要注意的是，这里如果有了3241，我们仍然可以使用SQL Script的方式进行测试。