The SQL Server Administrator's Console

Transparent Data Encryption (TDE)

Update: December 2011: Be sure to have a read of the more recent blog post "The Emporer's New Transparent Data Encryption" for some interesting updates on TDE.

SQL Server 2008 has more than a few new features. Perdictably, it looks a lot like a well packaged Service Pack to SQL Server 2005 and is as easy to implement as a good service pack. With SQL Server 2008 in the picture, the better organizational choice for those running still on SQL Server 2000 is SQL Server 2008. If you have questions about making the move feel free to drop me a note. I have done several upgrades from SQL Server 2000 to SQL Server 2008 and would be glad help out in any way I am able. 

To be sure there are several new additions to the product in SQL Server 2008. Additions like the new neighbors at the upper levels of the hierarchy of a SQL Instance: SQL Auditm Policy Based Management, the Data Collector and an improved Resource Govornor are new sights in the otherwise mostly familiar SLQ Server Management Studio Object Explorer.

Their are some things that remain incomplete. Documentation of the SMO object model is my pet example here. I suspect the SMO documentation will never be fleshed out. And I suspect the reason is because if they ever bothered to explain how some of this stuff worked they'd have to actually make it all work. And how about some real Encryption Hirearchy Support. Scattereted about as the pieces are I recommend that you make no encryption related changes without a script. There are a few new functions that make encryption hierarchy scripting a bit easier. See the SQL Server 2008 help topic Database Engine|Getting Started|What's New|Security Enhancements (Database Engine) for documentation links to is_objectsigned, asymkeyproperty, symkeyproperty, CRYPT_GEN_RANDOM, KEY_NAME, and sys.fn_check_object_signatures.    

At any rate, a few of the improvements with SQL Server 2008 spin around encryption. For starters, the conceptual presentation is somewhat better in SQL Server 2008 and the feature set is expanded. Still some work to be done to get the concepts across in a more universally accessible way. Among the most interesting new encryption topics is Transparent Data Encryption (TDE).

TDE is a 'me too' feature. Oracle, beginning with 10g I believe, has been out there with Transparent Data Encryption for quite a while. nonetheless, TDE is a helpful and value added addition to SQL Server.    

TDE allows encryption of an entire database with absolutely no change to the applications and - at least the promise of - only a tiny fraction of the performance penalty of other encryptions. Using TDE, malicious folks can be prevented from taking a backup or copy of a database away from the premises and viewing the data. The only place it would be visible is on the system where it was encrypted without special information that your DBA and/or System Administrator would securely protect and have always ready at hand if needed.

A caution: Do not make the mistake of thinking your data is fully protected because you enable TDE. Anyone with access to a TDE encrypted database could easily export the data to a file or another database while connected to the primary storage location. That is what makes TDE such a strong feature: good protection of data with no changes to the application layer or data access layer required. this strength is also the greates weakness. TDE is limited in what it can do by the requirement that no user change be required.  Everything that comes to the TDE encrypted database sees the same data as if it weren't encrypted. The need to stringently restrict access to production data and the need to obfuscate sensitive production data when moved to development or another secondary environment does not go away. TDE protects only file copies, backups and physical disc from being put to unintended use or used in unauthorize scenarios. The data remains visible to anyone that has access to the original database.

To protect succinctly identified sensitive or private data, encryption at the column level is needed for additional granularity. Columnar encryption in SQL Server 2008 is easy but not trasparent. The application must be changed - at the minimum at the stored procedure level - to support columnar encryption.

Turning on TDE.   

There is a nice how-to visualization in the SQL Server 2008 help topic "Understanding Transparent Data Encryption (TDE)"

Here is a script I use to create TDE encrypted database in production environments.

Always think about the consequences for the local SQL Instance before you run this (or any) script. For example, a Service Master Key is added to the server when this script is executed. Along with a Service Master Key comes the required onligation of the DBA to backup and properly secure the backup of that Service Master Key. There are ways remove the encryption hierarchy components in a database. Some database encription can only be removed by removing the database, but it can be done. The server level encryption hierarchy nodes are not so easy to eliminate. Another systemic consideration is trhat whan a user database is encrypted, tempdb is encrypted. ALl of tempdb. This could have performance consequences for other users databases on the instance. The best apprach is to plan for encryption to always be a properly maintained part of a server once encryption is introduced on that server. 

/*

Intended for use in SQLCMD mode only

(Select SQLCMD Mode from the SSMS Query menu)

SQLCMD values must be wrapped in double quotes

Service Master Key and Database Master Key

will be backed up to the default path on server

by this script immediately after being created

to be same folder as the database mdf file -

best practice is to store the keys and the backup

passwords in secure offsite location

*/

:setvar MASTERKEYENCRYPTIONPASSWORD put a password here in double quotes

:setvar DATABASEKEYENCRYPTIONPASSWORD put a password here in double quotes

:setvar SERVICEKEYBACKUPPASSWORD put a password here in double quotes

:setvar MASTERKEYBACKUPPASSWORD put a password here in double quotes

:setvar DATABASEKEYBACKUPPASSWORD put a password here in double quotes

:setvar DATABASENAME put database name here in double quotes

GO

-- the setvar statements will fail if not run in SQLCMD mode but the

-- script will keep going - stop the script now

IF LEFT('$(MASTERKEYENCRYPTIONPASSWORD)', 1)= '$'

OR LEFT('$(DATABASEKEYENCRYPTIONPASSWORD)', 1) = '$'

OR LEFT('$(SERVICEKEYBACKUPPASSWORD)', 1) = '$'

OR LEFT('$(MASTERKEYBACKUPPASSWORD)', 1) = '$'

OR LEFT('$(DATABASEKEYBACKUPPASSWORD)', 1) = '$'

OR LEFT('$(DATABASENAME)', 1) = '$'

OR DB_ID('$(DATABASENAME)') IS NULL

RAISERROR('Script cancelled. Intended for SQLCMD mode use only. Parameter values are required.',20,1) WITH LOG;

GO

USE [master];

GO

IF NOT EXISTS (SELECT * FROM [sys].[symmetric_keys] WHERE [symmetric_key_id] = 101)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(MASTERKEYENCRYPTIONPASSWORD)';

-- immediately backup the key

IF NOT EXISTS (SELECT *

FROM [sys].[symmetric_keys]

WHERE [symmetric_key_id] = 101)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(DATABASEKEYENCRYPTIONPASSWORD)';

-- immediately backup the key

DECLARE @master_MasterKey_Backup_Statement NVARCHAR(300)

SET @master_MasterKey_Backup_Statement = 'BACKUP MASTER KEY TO FILE = ''master_Database_MasterKey_'

+ CAST(DATEPART(YEAR,CURRENT_TIMESTAMP) AS [VARCHAR] (4)) + '_'

+ CAST(DATEPART(MONTH,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(DAY,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(SECOND,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + ''' '

+ 'ENCRYPTION BY PASSWORD = ''$(MASTERKEYBACKUPPASSWORD)'';'

EXEC sp_executesql @master_MasterKey_Backup_Statement;

END

GO

-- backup the service master key now too (symmetric_key_id = 102 in sys.symmetric_keys) 

DECLARE @Service_MasterKey_Backup_Statement NVARCHAR(300)

SET @Service_MasterKey_Backup_Statement = 'BACKUP SERVICE MASTER KEY TO FILE = ''Service_MasterKey_'

+ CAST(DATEPART(YEAR,CURRENT_TIMESTAMP) AS [VARCHAR] (4)) + '_'

+ CAST(DATEPART(MONTH,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(DAY,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(SECOND,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + ''' '

+ 'ENCRYPTION BY PASSWORD = ''$(SERVICEKEYBACKUPPASSWORD)'';'

EXEC sp_executesql @Service_MasterKey_Backup_Statement;

GO

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE [name] = 'ServerTDECert')

CREATE CERTIFICATE ServerTDECert WITH SUBJECT = 'TDECertificate';

GO

USE $(DATABASENAME);

GO

IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys

WHERE [database_id] = DB_ID('$(DATABASENAME)')

AND [database_id] = DB_ID())

BEGIN

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128 -- there are other algorithms that may be better

ENCRYPTION BY SERVER CERTIFICATE ServerTDECert

END

GO

IF EXISTS (SELECT * FROM sys.dm_database_encryption_keys

WHERE [database_id] = DB_ID('$(DATABASENAME)')

AND [database_id] = DB_ID()

AND [encryption_state] = 1 )

BEGIN

ALTER DATABASE $(DATABASENAME)

SET ENCRYPTION ON

END

GO

IF NOT EXISTS (SELECT *

FROM [sys].[symmetric_keys]

WHERE [symmetric_key_id] = 101)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(DATABASEKEYENCRYPTIONPASSWORD)';

-- immediately backup the key

DECLARE @DBA_MasterKey_Backup_Statement NVARCHAR(300)

SET @DBA_MasterKey_Backup_Statement = 'BACKUP MASTER KEY TO FILE = ''$(DATABASENAME)_Database_MasterKey_'

+ CAST(DATEPART(YEAR,CURRENT_TIMESTAMP) AS [VARCHAR] (4)) + '_'

+ CAST(DATEPART(MONTH,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(DAY,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + '_'

+ CAST(DATEPART(SECOND,CURRENT_TIMESTAMP) AS [VARCHAR] (2)) + ''' '

+ 'ENCRYPTION BY PASSWORD = ''$(DATABASEKEYBACKUPPASSWORD)'';'

EXEC sp_executesql @$(DATABASENAME)_MasterKey_Backup_Statement;

END

GO

When TDE encryption is set ON the ErrorLog and Application Event log will recieve the event message:

Setting database option ENCRYPTION to ON for database UserDbToEncrypt.

Beginning database encryption scan for database 'UserDbToEncrypt'

When Encryption completes sucessfully the 33113 message will be logged:

 Database encryption scan for database 'UserDbToEncrypt' is complete. 

If there is a problem the 33114 message will most likely be logged:

 Database encryption scan for database 'UserDbToEncrypt' was aborted. Reissue

ALTER DB to resume the scan.

Look in the [sys].[dm_database_encryption_keys] system view to verify that the encryption process is 'stuck' in pending. Finding the encryption process in pending state is consistent with the 33114 message but doesn't explain the failure Also, it could bethat the encryption has a lot to do and it is just taking a while to get it done.

SELECT * FROM [sys].[dm_database_encryption_keys]

WHERE DB_NAME(database_id) = 'UserDbToEncrypt';