Microsoft SQL Server has many security features available within the database, but until release of SQL Server 2008 there has been no “out-of-the-box” method for protecting the data at the operating system level. The Transparent Data Encryption (TDE) feature introduced in SQL Server 2008 allows sensitive data to be encrypted within the data files to prevent access to it from the operating system. It solves the problems of security of data means encrypting databases on hard disk and on any backup media and is the best possible choice for bulk encryption to meet the regulatory compliance or corporate data security standards. This feature encrypts both data and logs as the records are written to SQL database files (*.mdf) in real-time, including backups, snapshots and transaction logs. TDE encrypts data before it’s written to disk and decrypts data before it is returned to the application. The encryption and decryption process is performed at the SQL layer, completely transparent to applications and users. TDE encryption uses a Database Encryption Key (DEK) (that is an asymmetric key secured by using a certificate stored in the master database), which is stored in the database boot record for availability during recovery.
In this post, I’ll show you how to encrypt database using Transparent Data Encryption (TDE) and then I will discuss the limitations of TDE.
Architecture of Transparent Data Encryption
The following illustration shows the architecture of TDE encryption:
Service Master Key is created at a time of SQL Server setup; DPAPI encrypts the Service Master Key. Service Master Key encrypts Database Master Key for the Master Database. The Database Master Key of the master Database Creates the Certificate then the certificate encrypts the database encryption key in the user database. The entire database is secured by the Database Master Key of the user Database by using TDE. TDE performs the encryption at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.
Microsoft Reference: Transparent Data Encryption (http://msdn.microsoft.com/en-us/library/bb934049.aspx)
Note: For the purposes of this post, I’ll be encrypting SQL Server 2012 sample database AdventureWorks2012 database using TDE. It is advisable to backup the database prior to implementing TDE.
Our first step to setup TDE is to create a database master key for our master database. To do that, open the New Query window and execute the following script:
USE [master] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'; GO
Information about the database master key is visible in the sys.symmetric_keys catalog view. Execute the following query to verify that database master key is encrypted by the service master key:
USE [master] GO SELECT b.[name] ,a.[crypt_type_desc] FROM [sys].[key_encryptions] a INNER JOIN [sys].[symmetric_keys] b ON a.[key_id] = b.[symmetric_key_id] WHERE b.[name] = '##MS_DatabaseMasterKey##'; GO
Now the next step is to create a self-signed certificate that is protected by the database master key of our master database. This certificate encrypts the Database Encryption Key in the AdventureWorks2012 database.
Execute the following script to create the self-signed certificate:
USE [master] GO CREATE CERTIFICATE CertificateforTDE WITH SUBJECT = 'Certificate for TDE' ,EXPIRY_DATE = '20220101'; GO
Navigate as follow, to view this certificate in SQL Server Management Studio:
Note: Certificate expiration is not enforced when the certificate is used for encryption.
Now backup your certificate and database master key immediately as you need them in a recovery situation. To do that, execute the following script to backup certificate:
USE [master] GO BACKUP CERTIFICATE CertificateforTDE TO FILE = 'D:\TDE_Demo\CertificateforTDE.cer' WITH PRIVATE KEY ( FILE = 'D:\TDE_Demo\CertificateforTDE.key' ,ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1' ) GO
Execute the following script to backup the database master key of master database:
USE [master] GO -- Master key password must be specified when it is opened. OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1' BACKUP MASTER KEY TO FILE = 'D:\TDE_Demo\ExportedMasterKey.key' ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'; GO
Once successfully executed, verify that the master key and security certificate backup files are created in the location specified in script for example for in this demo its D:\TDE_Demo\ (see below):
Note: Save your master key and security certificate backup files in a secure location as you’ll need them when restoring the database on a different SQL Server otherwise the restore process will fail.
Now that we have created the database master key and the certificate in the master database, we are now ready to create the database encryption key for our database.
Execute the following script to create the database encryption key in AdventureWorks2012 database:
USE [AdventureWorks2012] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertificateforTDE; GO
The AES_128 option specifies Advanced Encryption Standard (AES) with a 256 bit key length, and we protect the database encryption key with the AWTDECertificate certificate that was created in the Master database.
Note: SQL Server allow you to encrypt data with several encryption algorithms such as DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
The final step in the setup process of TDE is to enable it. This is accomplished by executing the ALTER DATABASE command with the SET ENCRYPTION ON argument.
Execute the following script to enable TDE on AdventureWorks2012 database:
USE [master] GO ALTER DATABASE [AdventureWorks2012] SET ENCRYPTION ON; GO
To verify that database is encrypted using TDE, right-click the database and choose option and you will see encryption option is now ON as shown in figure below:
Execute the following query, to find out what databases are encrypted using TDE:
USE [master] GO SELECT db.[name] ,db.[is_encrypted] ,dm.[encryption_state] ,dm.[percent_complete] ,dm.[key_algorithm] ,dm.[key_length] FROM [sys].[databases] db LEFT JOIN [sys].[dm_database_encryption_keys] dm ON db.[database_id] = dm.[database_id]; GO
Now perform the full backup of AdventureWorks2012 database using script below:
USE [master] GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Backups\AdventureWorks2012.bak' WITH NOFORMAT ,NOINIT ,NAME = N'AdventureWorks2012-Full Database Backup' ,SKIP ,NOREWIND ,NOUNLOAD ,STATS = 10; GO
Now test the restore the database of AdventureWorks2012 database as follow:
As you can see from above that database restore is successful on same instance.
Now try restoring this database on different SQL Server using script below:
USE [master] GO RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'D:\Backups\AdventureWorks2012.bak' WITH FILE = 1 ,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream' ,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf' ,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf' ,NOUNLOAD ,STATS = 5; GO
As you can see, the restore process will fail with the error below:
This is because the database is encrypted with TDE.
To restore the TDE encrypted database on different SQL Server instance, you first need to restore the database master key and then the self-signed certificate that is used to encrypt the database encryption key.
Execute the following script to restore the database master key from master key backup:
USE [master] GO RESTORE MASTER KEY FROM FILE = 'D:\TDE_Demo_Backup\ExportedMasterKey.key' DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1' ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2'; GO
Next step is to create the SQL Server certificate on the second SQL Server using the Private Key backup of Principle SQL Server. To do that execute the following script:
USE [master] GO OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2' CREATE CERTIFICATE CertificateforTDE FROM FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.cer' WITH PRIVATE KEY ( FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.key' ,DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1' ); GO
We are now ready to restore the database on second SQL Server.
Note: You must OPEN MASTER KEY first and then perform restore otherwise restore will fail.
Execute the script below which we executed earlier to restore the AdventureWorks2012 database:
USE [master] GO OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2' RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'D:\Backups\AdventureWorks2012.bak' WITH FILE = 1 ,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream' ,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf' ,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf' ,NOUNLOAD ,STATS = 10; GO
This time restore script executed successfully and AdventureWorks2012 database has now been restored on second SQL Server (see below):
Limitation of Transparent data encryption
- TDE does not provide encryption across communication channels.
- When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
- The encrypting certificate or Asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
- Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.
Note that you do not need to restore the database master key to restore a TDE protected database to another instance, you only need the database backup and the backup of the cert used to protect the database. On your target instance if a database master key has not been created then you will need to do so but there is no dependency on the source master key
I hope it is not off-topic here to point out some differences between built-in SQL TDE and Encryptionizer TDE:
Just what I needed, Thanks!
Now I know this post is old, but I hope you can reply to one question. I’m trying to change the Certificate that I used to Encrypt a database with. That way I can create new Certificates and expire old one. Any information on that?
To change the certificate used to secure the database encryption key for your TDE protected database, create a new certificate in the master database and then use the following
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE newcertname
Look at Perry’s reply, as he actually answered your question.
Perfect, I will be testing this today. Thanks to both of you.
Tested, it works perfectly thank you!
Now for my other question, I put it up on Twitter but I’m still not understanding the answer fully. So I’ve already tested restoring the DB on another server and this was the process I used:
Created new Master Key
Restored Certificate from Server1 to Server2
Restored the Encrypted Database
According to the post, I need to restore the Master Key from Server1 to Server2 and I’m having trouble understanding why. My best guess is that the Master Key encrypts the Certificate so it is best to restore it from Server1 to have the Certificate encrypted in the same way. Is that right? Seems more like a best practice approach but at the same time, should I have the same Master Key across different servers?
Thanks in advance for your help.
This TechNet article discusses creating a new Master Key on the new server and not restoring it.
I agree with you. Although, I prefer to use the same master key. I also backup the master key and certificate for DR scenario. there is no security best practice related to this topic.
you should not use the same password for all your instances DMKs, always keep them unique otherwise if the password is released you compromise all instances 😉
Now to the issue of restoring a TDE database to a new server;
You DO NOT need to backup and restore the DMK! The SMK and DMK sit at the top of the SQL Server encryption hierarchy and the TDE cert has no direct dependency on either of these. The DMK merely encrypts the Certs private key while it is stored on the instance. When the Cert is backed up away from the server the private is then encrypted with a password.
To move a TDE protected database to a new server you only need the database backup file, the cert backup file and password and permission to create said objects.
So let me throw this scenario out there. I take my Cert on Server1 and Encrypt DB1. Then I create a new Database Master Key on Server2 and I take my Cert from Server1 and put it on Server2 and then I restore DB1 on there. Out of forgetfulness I did not backup the DMK on Server2, that will not affect my Encrypted DB1 and will not affect my Cert correct? So if I wanted to take DB1 off of Server2 and restore it to Server3 then all I need is the Certificate with the Private key and the password for that Private key to restore the Cert onto Server3?
I don’t want to have to keep backups of all the DMKs across servers. Of course I will keep my Cert and Private key file safe with a strong password and that is all I want to rely on to restore the Encrypted DB.
I appreciate you guys hanging on there and helping me at the clearest picture. I’m about 90% of the way there 🙂
I understand better now. So if I use separate Master Keys I would need to back them up as well, and store them securely. If I use the same Master keys then I just need one backup to be stored securely for multiple servers. More like a management strategy. I actually like your way better, and since I’m in the early implementation phase it won’t be hard to put it to practice. Thanks for your help!
the DMK is immaterial between instances it’s just a key created and encrypted by supplying a user defined password. The DMKs primary function is to protect the private keys of any certs or asymmetric keys that are stored on the SQL Server instance. The SMK and DMK are of no relevance between instances.
Focus youur efforts on backing up and securing any certs you create, this is a must
I wrote this out before your other reply. I think I got it now. As long as my Cert and the PrivateKey+Password are in a safe place then I’m good.
Who can backup the Certificate from SQL, I am trying to do a security analysis of if someone gained access to our SQL machine – could they backup the database and certificate ? What rights would they need – sysadmin, diskadmin, serveradmin ?
Who has access to backup certificates and can you prevent backing up certificates after they have been backed up once? My example is someone gains access to your SQL box and that account has sysadmin, backs up the database and backs up the certificate (public/private) then has access to your data. So 2 questions, what server roles have access to backup certificates, second question can you prevent the private key from being backed up after 1st backup (put in some security vault).
Great article just wondering why on the restore you decided to encrypt using a different password? ex: ‘$tr0ngPa$$w0rd2’;