TDE and Tempdb

Published 11 June 10 11:05 PM | MartinBell
Looking at the topic “Understanding Transparent Data Encryption (TDE)” in Books Online it says:

“The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.”

Not encrypting tempdb would leave a very big hole in the encryption architecture, but the second statement could be one of the major reasons why you may want to back out of using TDE. First a quick reminder of a few of the uses for tempdb:

User objects – temporary tables and user tables created in tempdb

Version stores - row versions for MARS, triggers, Snapshot isolation and online index builds etc.

Internal objects – intermediate sorts and results, spooling, XML variables, cursors, service broker messages etc.

Therefore it is not always a easy to know the impact that encrypting tempdb will be. Another issue is procedural; how many people will actually do meaningful performance testing, especially as this is only a database change? Of those who do some performance testing, how many of them will assess the impact on other databases/applications that may be using the same instance, or another instance on the same server, or another virtual machine on the same virtual server?

How do you know that tempdb is encrypted?

For a user database if you look at sys.database the is_encrypted column will have a value of 1, again if you look at book online it says:

”Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause). Can be one of the following values:

1 = Encrypted

0 = Not Encrypted

For more information about database encryption, see Understanding Transparent Data Encryption (TDE).

If the database is in the process of being decrypted, is_encrypted shows a value of 0. You can see the state of the encryption process by using the sys.dm_database_encryption_keys dynamic management view.”

As you have not issued an ALTER DATABASE statement to encrypt tempdb this may be as you would expect, but then why isn’t the column called is_encrypted_using_ALTER_DATABASE?

So what does sys.dm_database_encryption_keys say about the encryption state of tempdb? Lets go through encrypting a database and see what it shows:

If necessary create database master key for the master database and then a certificate to use for database encryption key:

USE master

GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd' ;GO BACKUP MASTER KEY TO FILE = 'C:\temp\MasterKey.pkf'ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd' ;GO CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate' ;GO BACKUP CERTIFICATE TDECertificate TO FILE = 'C:\temp\TDECertificate.cer'WITH PRIVATE KEY(      FILE = 'C:\temp\TDECertificate.pvk' ,      ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd') ;GO SELECT LEFT(d.name,25) as [Name],k.*FROM sys.dm_database_encryption_keys kJOIN sys.databases d ON d.database_id = k.database_id ;

GO
 

/* Name database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint percent_complete------------------------- ----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ---------------- (0 row(s) affected) 

*/

This shows use that there are no database encryption keys on the system. Create the database and then create the database encryption key:

CREATE DATABASE [TDEDatabase] ;GO USE [TDEDatabase] ;GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVERCERTIFICATE TDECertificate ;GO SELECT LEFT(d.name,25) as [Name],k.*FROM sys.dm_database_encryption_keys kJOIN sys.databases d ON d.database_id = k.database_id ;GO /* Name database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint percent_complete------------------------- ----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ----------------TDEDatabase 16 1 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 NULL 2010-06-11 13:22:07.433 AES 128 0x75603CE3303587A08D4CA68AA616569980491BE1 0 (1 row(s) affected) */ 

The database encryption key for the new database shows up with an encryption status of 1, so it is not being used. Enable TDE and see what happens:

ALTER DATABASE TDEDatabase SET ENCRYPTION ON ;GO -- Wait a while before we verify the encription WAITFOR DELAY '00:00:05' ;GO SELECT LEFT(d.name,25) as [Name],k.*FROM sys.dm_database_encryption_keys kJOIN sys.databases d ON d.database_id = k.database_id ;GO /* Name database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint percent_complete------------------------- ----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ----------------tempdb 2 3 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 NULL 2010-06-11 13:24:52.693 AES 256 0x 0TDEDatabase 16 3 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 2010-06-11 13:24:52.690 2010-06-11 13:22:07.433 AES 128 0x75603CE3303587A08D4CA68AA616569980491BE1 0 (2 row(s) affected) 

*/

There are now two database encryption keys one is tempdb and a status of 3 indicates the keys are encrypting the database.

So now lets look at what happens when TDE is turned off:


ALTER DATABASE TDEDatabase SET ENCRYPTION OFF ;
GO SELECT LEFT(d.name,25) as [Name],k.*FROM sys.dm_database_encryption_keys kJOIN sys.databases d ON d.database_id = k.database_id ;GO /* Name database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint percent_complete------------------------- ----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ----------------tempdb 2 3 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 NULL 2010-06-11 13:24:52.693 AES 256 0x 0TDEDatabase 16 1 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 2010-06-11 13:22:07.433 2010-06-11 13:24:52.690 2010-06-11 13:22:07.433 AES 128 0x75603CE3303587A08D4CA68AA616569980491BE1 0 (2 row(s) affected) */ This shows that they key in TDEDatabase is still there but not being used to encrypt the database, but tempdb is still encrypted. After dropping the TDEDatabase:

USE master ;
GO

DROP DATABASE [TDEDatabase] ;
GO

SELECT LEFT(d.name,25) as [Name],k.*
FROM sys.dm_database_encryption_keys k
JOIN sys.databases d ON d.database_id = k.database_id ;
GO

/*

Name database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint percent_complete
------------------------- ----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------ ----------------
tempdb 2 3 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 2010-06-11 13:24:52.693 NULL 2010-06-11 13:24:52.693 AES 256 0x 0

(1 row(s) affected)

*/
Tempdb is still encrypted. So how do we stop tempdb being encrypted? Unfortunately you can’t use ALTER DATABASE to turn this of for tempdb:

ALTER DATABASE Tempdb SET ENCRYPTION OFF ;
GO /* Msg 33102, Level 16, State 1, Line 1Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed. */ 

The only way to stop tempdb being encrypted seems to be stop and re-start SQL Server, which recreates the database. I didn’t find anywhere in Books Online that tells you to do that, but John Magnabosco has blogged about it.

This is going to cause problems for systems that require high availability, especially as the highly available database may not be the encrypted one, so I raised a connect issue.

Filed under:

Comments

# Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's Trail said on December 11, 2010 07:55 PM:

Pingback from  Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's Trail

# Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's Trail said on December 11, 2010 07:55 PM:

Pingback from  Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's Trail

# Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's - Learning in the Open said on July 23, 2015 04:02 PM:

Pingback from  Microsoft — SQL Server — Transparent Data Encryption (TDE) – vMS SQL Server 2008 | Daniel Adeniji's - Learning in the Open

This Blog

SQL Blogs

Syndication