I'm experiment with TDE in a test environment. I want to be able to check that I have succesfully encrypted a database. The steps I have taken so far are as follows: USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'see secure pwd repos for pwd'; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate' go --===================================================================== USE master BACKUP CERTIFICATE MyServerCert TO FILE = 'J:\certificate backups\MyServerCert'; GO --===================================================================== USE test_db GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert GO ALTER DATABASE test_db SET ENCRYPTION ON GO I suppose there are two things I find a little disconcerting. 1. The database I was trying to encrypt was 10 gig, but the script took about 1 sec to run, which makes me think nothing has been encrypted. 2. I get a warning saying the certificate has not been backed up. I presume this refers to the certificate on master called MyServerCert. I know that it has been back up, so why the warning. Anyway I'd be grateful if anyone can tell me how I can test this. Cheers Greg
The scripts below will help you to monitor the encryption/decryption process. There is a percent_complete column that shows you the level of completion, but it is not an immediate process. ALTER DATABASE mydb SET ENCRYPTION OFF ALTER DATABASE mydb SET ENCRYPTION ON -- 1=not encrypted, 3=encrypted SELECT db_name(database_id), encryption_state, percent_complete, * FROM sys.dm_database_encryption_keys select is_encrypted, db_name(database_id), * from sys.databases
SELECT DB_NAME(database_id), encryption_state FROM sys.dm_database_encryption_keys ; GO When the statement above is run, a state is returned. A database encryption state of “2” means that encryption has begun, and an encryption state of “3” indicates that encryption has completed.