June 2010 - Posts

SQLBits the “Seven Wonders of SQL”
21 June 10 12:01 AM | MartinBell | 1 comment(s)

We are delighted to announce that venue and dates for SQLBits 7.

The “Seven Wonders of SQL” will run from 30th September – 2nd October 2010 in the historic city of York.

After the success of SQLBits VI we looking to push onwards and upwards (well north) with our next event and we will be reverting to the format that was so successful in Wales. Day one will be a training day, featuring in-depth seminars from a number of well-known SQL Server professionals; day two will be a deep-dive conference day with advanced sessions delivered by the best speakers from the SQL Server community; and day three will be the traditional SQLBits community conference day, with a wide range of sessions covering all aspects of SQL Server at all levels of ability. There will be a charge to attend days one and two, but day three will as usual be completely free to attend.

The venue selected by the committee for SQLBits 7 is York University http://www.york.ac.uk/ & http://www.yorkconferences.com/.

Session submission is now open and we are looking for presentations on the seven main parts of the SQL Server toolset: the relational engine and TSQL, Integration Services, Reporting Services, Analysis Services/PowerPivot, Master Data Services, StreamInsight and Parallel Data Warehouse. As a community organisation we are keen to have as many people involved as possible, so we welcome submissions from any new or inexperienced speakers.

For more details go to www.sqlbits.com.

Filed under:
TDE and Tempdb
11 June 10 11:05 PM | MartinBell | 3 comment(s)
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:

This Blog

SQL Blogs

Syndication