TSQL: Transparent Data Encryption exercise for 70-462

Here’s some TSQL for the WingTipToys2012 Transparent Data Encryption (TDE) exercise in the 70-462 training materials.

CREATE DATABASE WingTipToys2012;
GO
 
USE WingTipToys2012;
GO
 
USE [master];
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretPa$$w0rd';
GO
 
CREATE CERTIFICATE server_cert WITH SUBJECT = 'My DEK Certificate';
GO
 
USE WingTipToys2012;
GO
 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE server_cert;
GO
 
ALTER DATABASE WingTipToys2012
SET ENCRYPTION ON;
GO
 
-- Create tables
CREATE TABLE dbo.aeroplanes
(
	[model] VARCHAR(MAX)
) WITH (DATA_COMPRESSION = ROW);
GO
 
CREATE TABLE dbo.helicopters
(
	[model] VARCHAR(MAX)
) WITH (DATA_COMPRESSION = ROW);
GO
 
-- While we're here backup the certificate
USE [master];
GO
BACKUP CERTIFICATE server_cert
TO FILE = 'server_cert'
WITH PRIVATE KEY
(
	FILE = 'private_key_file',
	ENCRYPTION BY PASSWORD = 'Secret2014'
);
GO

Leave a Reply