question

GPO avatar image
GPO asked

Testing TDE encryption (SQL 2008)

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
securityencryption
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

After you resolve this, the next problem will be insufficient permissions by (non dbowner) users trying to decrypt something. When that happens have a look at GRANT CONTROL ON CERTIFICATE :: certname TO username
0 Likes 0 ·
Jason Cumberland avatar image
Jason Cumberland answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Leo and Jason Many thanks for taking the time to respond. I haven't had a chance to get back and test your suggestions yet, but will endeavour to do so tomorrow, at which point I'll come back and do my voting and so on. Thanks again.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.