0

I'm new to SQL Server TDE and have a question.

I have two VM's (VM1 = Source and VM2 = Target). On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key. No issues here.

On the target machine, i executed the following t-sql commands

CREATE MASTER KEY ENCRYPTION

CREATE CERTIFICATE
FROM FILE
WITH PRIVATE KEY(
FILE
DECRYPTION BY PASSWORD
);

without any issue and successfully restored my Test TDE database.

On the source machine, I had to run this command

CREATE DATABASE ENCRYPTION KEY

but I didn't have to when I did the restore on the target machine. Here's my question: Is my Test database on the target machine TDE enabled?

1

When you say:

On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key. No issues here.

We don't really know the steps you took.

Here is a query you can run to see if the database is encrypted on the target server. Look at the EncryptionState column.

SELECT 
    db_name(database_id) as DatabaseName, 
    Case 
        when encryption_state=0 then 'No Database Encryption'
        when encryption_state=1 then 'Unencrypted'
        when encryption_state=2 then 'Encryption In Progress'
        when encryption_state=3 then 'Encrypted'
        when encryption_state=4 then 'Key Change In Progress'
        when encryption_state=5 then 'Decryption In Progress'
        when encryption_state=6 then 'Protection Changes In Progress'
    end as EncryptionState,
    percent_complete
FROM sys.dm_database_encryption_keys
  • 3. Created a "Test" database 4. Ran CREATE MASTER KEY ENCRYPTION 5. Ran CREATE CERTIFICATE 6. Ran CREATE DATABASE ENCRYPTION KEY 7. Ran ALTER DATABASE Test SET ENCRYPTION ON 8. Ran BACKUP CERTIFICATE TO FILE WITH PRIVATE KEY 9. BACKUP DATABASE Test – sydney Aug 21 '18 at 17:43
  • After that, I took the backup db and copied the certificate and private key to the target machine to perform the restore. I ran your sample statement, "tempdb" was the only one that came back. I had already checked this before posting. Here's my confusion. When I right click on my Test db -> Properties -> Options -> scroll down and I saw this Encryption Enabled = True So I am conflicted results. Is my restored Test TDE db on the target still TDE enabled?? – sydney Aug 21 '18 at 17:48
  • Sorry I didn't know how to format nicely on my replies. But that what I did on the source machine in my replies above. – sydney Aug 21 '18 at 17:50
  • @sydney - can you update your question and include the backup command you ran on the source server? I'm wondering if your restore actually restored the encrypted database from the source. – Scott Hodgin Aug 21 '18 at 18:00
  • @sydney - when you right-clicked on your test db and looked at properties, did you do that on the source or target server? – Scott Hodgin Aug 21 '18 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.