Can I decrypt TDE enabled database if I have access to the master database’s mdf and ldf files or the master database backup?

If someone gets access to the master database, i.e., if s/he can manage to copy the MDF, LDF or the master database backup, along with the USER database, will s/he also be able to decrypt the data?

This was a question asked by one of our customers.

In the TDE implementation, we store the database master key and the certificate in the master database, and the database encryption key in the user database.

Consider this: someone has stolen your user database, along with the MDF and LDF of the master database. They will probably try to first restore your master database, and then then the user database.

To elaborate:

If SQL1 is running on machine1 and the startup account is START1.

And SQL2 is running on machine2 and the startup account is START2.

In this case, we can restore the master database of SQL1 on SQL2, but when we try to restore the user database, an error, like the one below, will occur:

Msg 15581, Level 16, State 3, Line 1 Please create a master key in the database or open the master key in the session before performing this operation. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

 

Then, we have to manually open the key, by specifying the password we used when we created the key on SQL1 instance, using the statement below

OPEN MASTER KEY DECRYPTION BY PASSWORD = '---your database master key password---'

 

Refer to this link for more information: https://technet.microsoft.com/en-us/library/ms174433.aspx

Thus, one cannot decrypt the user databases, even if s/he has taken the master database, along with the user database, with him/her.

So, if you know the password you used to create the database master key, the restore will be successful in decrypting the data that’s encrypted through TDE. SQL looks for the certificate, an opened database master key in the session, and the database encryption key in the user database. If all the three components (opened database master key, certificate, and the database encryption key) are available, then the database can be successfully restored.

I performed a small exercise to reproduce this scenario:

Scenario 1:

Machine 1:

USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1234!' CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate' create database tdeendabled use tdeendabled CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE tdeCert; ALTER DATABASE tdeendabled SET ENCRYPTION ON SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

 

Tempdb	3
tdeendabled	3

“3” means encryption is enabled.

create table test (id integer, name varchar(10)) insert into test values (1,'CSM') insert into test values (2,'MCS') select * from test

1	CSM
2	MCS

Backup the databases -- master and user db (tdeenabled):

backup database master to disk = 'c:\backup\master1.bak' backup database tdeendabled to disk = 'c:\backup\tde.bak'

Machine 2:

Master database restore:

Please click on the link below for instructions on how to restore master database from backup:

https://technet.microsoft.com/en-us/library/ms190679.aspx

restore database master from disk='c:\backup\master1.bak' with replace go Processed 3440 pages for database 'master', file 'master' on file 1. Processed 3 pages for database 'master', file 'mastlog' on file 1.

The master database has been successfully restored. Shutting down SQL Server.

SQL Server is terminating this process.

User database (tdeenabled) restore:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'masterkey1234!' restore database tdeenabled from disk = 'c:\backup\tde.bak' with replace select * from test

 

1	CSM
2	MCS

I’m able to see the data

Note: I tested all of these in SQL 2012. You may see different results in 2008 or 2008R2.

This brings us to another question: ˜What if I have 100 user databases to decrypt, and I want to automate it through a job or a script? Also note, my organizational policies do not allow me to hardcode passwords in scripts/jobs.’

In this case, we will have to manually restore them by typing the password each time to open the DMK in a session

In these sort of situations, we can leverage the SMK (Service master key).

To do that, we must:

Back up the SMK on source server -> restore it on the destination -> recreate DMK to add encryption by SMK.

The series of steps to be followed will be:

Source:

BACKUP SERVICE MASTER KEY TO FILE = 'c:\backup\smk2.bak' ENCRYPTION BY PASSWORD = 'abcd!12345'

Destination

1.OPEN MASTER KEY DECRYPTION BY PASSWORD = 'masterkey1234!' 2. RESTORE SERVICE MASTER KEY FROM FILE = 'c:\backup\smk.bak' DECRYPTION BY PASSWORD = 'abcd!12345' FORCE 3. alter master key add encryption by service master key

 

Please refer to this link for more information on adding encryption using the service master key:

https://blogs.msdn.com/b/lcris/archive/2005/09/30/475822.aspx

Once you issue the step-2 above, from the next instance, SQL will automatically open the DMK whenever needed. All we will need is a matching SMK in that instance.

In conclusion, no one can decrypt your TDE enabled database unless they know the password you set when you created your DMK.

Note: From personal experience - and this is, of course, solely my view   I have found that in some of the VM (sysprep) environments, even if we don’t have the password and open the DMK, user databases can be successfully restored, if the service startup accounts of the source and the destination SQL Server are the same.

As always, please feel free to get in touch with me with your comments or feedback.

Author:

Chandra Sekhar , Support Engineer, Microsoft India GTSC

Reviewed by:

Akbar Farishta, Escalation Engineer, Microsoft India GTSC

Amit Khandelwal, Technical Advisor, Microsoft India GTSC

Comments

• Anonymous
  May 18, 2014
  Good work! I have been searching for some good documents or blogs on practical implications of TDE, especially when I have to move them to new infrastructure, backup & restore them etc...And finally here it is... Keep it up. I have a question here. When you say BACKUP SERVICE MASTER KEY TO FILE = 'c:backupsmk2.bak' ENCRYPTION BY PASSWORD = 'abcd!12345' Should this be the same password we use to open the database master key, like in the command below? OPEN MASTER KEY DECRYPTION BY PASSWORD = 'masterkey1234!'

• Anonymous
  May 18, 2014
  Hi George, Thanks for the kind words. Actually, the password we use while backing up the service Master Key (SMK), "abcd!12345" in our example, is to protect the backed up file i.e.,smk2.bak so that people who know the password only can restore it on the destination. It need not to be the same as the one we use while opening the database master key  i.e.,"masterkey1234!" in our example. This password is the one we used when we initially create the DMK through the command below CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterkey1234!' Feel free to post here, if you have anymore questions. Regards, Chandra

• Anonymous
  October 12, 2014
  The comment has been removed

• Anonymous
  September 15, 2016
  This article seems to state that TDE can easily be hacked: http://simonmcauliffe.com/technology/tde/Can you speak to this and still confirm that TDE is as secure as you state?Thanks!