SQL SERVER 2008 使用TDE加密和解密

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 tdeCert6. Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously.ALTER DATABASE myDatabase SET ENCRYPTION ON

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