We were working with Chris Mitchell (Twitter) from the Microsoft Technology Center in Atlanta the other day, out of the Microsoft Office in Tampa. The Office Has a Beautiful and very distracting view, see the picture below, of the Bay area, my friend Dan Taylor(blog|Twitter) took the photo.
One of those views that makes you love living in Florida!
But I digress, we were having a really great conversation about features that are enabled or disabled when you enter a database into 80 Compatibility level, but are running on a 2008 R2 Instance.
When having discussions you typically throw things out by features (or at least that is what I’ve heard people do), Database Compression will not work, Filestream will not work, Encryption will not work. But when we brought up encryption I asked does that mean Transparent Data Encryption will not work. Neither of us knew off hand.
I’ve presented on this topic so I’ve got some scripts on standby, so I opened one up fired it off, changed the Compatibility Level, and it still worked.
“So Balls”, you say “Prove It.”
Dear Reader I wouldn’t have it any other way!
AND….. HERE….. WE…… GO!
First we’ll create our Demo Database
/*
Create TDE Database for demo
*/
USE master
GO
IF EXISTS(select * from sys.databases where name=N'TDE')
BEGIN
DROP DATABASE TDE
END
CREATE DATABASE TDE
GO
When we use Transparent Data Encryption we need to First Create a Master Key and a Certificate in the Master Database.
/*
Create the Master Key for the
SQL Server
*/
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='brad1'
GO
/*
Create the Certificate that will be used
for database level encryption
*/
CREATE CERTIFICATE DatabaseCertificate WITH SUBJECT='NunyaBiznes'
GO
Now that we’ve got those let’s alter our databases compatibility level to 80, SQL 2000.
USE TDE
GO
/*
Let's Alter Our Database
And Place It in 80,SQL 2000,
Compatability Mode
*/
ALTER DATABASE TDE
SET COMPATIBILITY_LEVEL = 80
Now let’s Create our table, and Insert some data. You’ll see that I have default values set up to simulate an SSN.
/*
Create Table for filler data
*/
IF EXISTS(SELECT * FROM SYS.tables WHERE name='tdeData')
BEGIN
DROP TABLE dbo.tdeData
END
CREATE TABLE dbo.tdeData(
ID int IDENTITY(1,1) NOT NULL
,nameText varchar(100) default 'fakeSSN'
,ssnText varchar(100) default '111-11-1111'
,fillerText char(5000) default 'a'
)
GO
/*
Create filler data for TDE demo
*/
DECLARE @i int
SET @i = 0
WHILE (@i < 15000)
BEGIN
INSERT INTO tdeData DEFAULT VALUES
SET @i = @i +1
END
I do this in Demo’s because I want people to see that when you insert data into a Data File or a back it up to a Backup File, the data is in plain text. That is part of the reason you use TDE, because it adds an additional layer of protection. So let’s backup our data Pre Encryption, and look at it in a Hex Editor.
Look at that there is our social security number 111-11-1111! Now let’s double check our Compatibility Level.
And now let’s enable encryption. We create a Database Encryption Key using our Certificate we made earlier, and specify our algorithm. Then we set the database encryption to on.
/*
Create Database Encryption Key
*/
USE TDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DatabaseCertificate
GO
/*
Set The Encryption On
*/
ALTER DATABASE TDE
SET ENCRYPTION ON
We’ll use a DMV to validate the encryption process that SQL MVP Jasper Smith(blog) wrote that I’ve loved to use for years now.
SELECT
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
e.percent_complete,
DB_NAME(e.database_id) AS DatabaseName,
c.name as CertificateName,
e.encryption_state
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.certificates AS c
ON e.encryptor_thumbprint = c.thumbprint
You just execute this after you set Encryption On and watch the internal process work, the size of the database will determine how long it runs. For a large database this is a great DMV to show you just how long the process is taking. And now we are encrypted!
So let’s do another backup and open that up in a Hex Editor to see if we can find our SSN.
And as you can see our file space looks quite a bit different, and we couldn’t find the string for our SSN
WHAT IN THE NAME OF PRO-WRESTLING DOES THIS MEAN!?
First off it means that if you have to keep your database in 80 Compatibility Level, you can still use Transparent Data Encryption to secure your backups and your Data Files at rest. Your log files will be encrypted too, but this occurs at a VLF, virtual log file, level. So the full log will not be encrypted until all VLF's have been over written.
Some other pretty cool possibilities, I asked Paul Randal (blog|twitter) what part of the Storage Engine handled the Encryption and Decryption of pages.
I know Access Methods handles Compression, because the pages are stored in memory in a Compressed State before being passed back to the Relational Engine. But Pages are decrypted when stored in memory? So I didn’t think it was the Access Methods.
Paul confirmed that it was indeed the Buffer Manager that handles TDE.
So that leads me to think some ROCKING things may be possible, if you know what the Buffer Manager IS COOKIN!
Thanks,
Brad
