Setting up Transparent Data Encryption

Below I have posted all my code for setting up Transparent Data Encryption from scratch. Feel free to use it, but please note that you do it at your own risk.

Open in Management studio and run one step at a time.

If any questions, then please contact me.


— This script shows you how to setup Transparent data encryption
— Remember to backup all you keys and certificates

— NOTE: YOU RUN THIS SCRIPT ON YOUR OWN RISK

— Created by Søren Agerbo Frydensbjerg, 2011
— Feel free to use the examples and share them with your network.
— Just remember to give me some credit

use master
— TDE
— 1. Create a master database key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘1234’;

— 2. Create a certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certifikat’;

— 3. BACKUP Certificate
BACKUP CERTIFICATE tdeCert TO FILE = N’c:\sj\cert.sjback’

   WITH PRIVATE KEY (

         FILE = N’c:\sj\Key.sjback’,

         ENCRYPTION BY PASSWORD = ‘1234’);

use AdventureWorks
— 4. Create Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER CERTIFICATE tdeCert

— 5. Enable encryption
ALTER DATABASE adventureworks SET ENCRYPTION ON

— 6. Follow progress
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s