SQL Server – Transparent Data Encryption (TDE) – Version MS SQL Server 2008

 

 

Is there a master key?

To begin with encryption, the MS SQL Server Instance needs a “master key”

The first step towards creating one, is to determine whether one exists

 

Using master.sys.databases ?

SELECT
           [name]
         , [is_master_key_encrypted_by_server]
         , [is_encrypted]
FROM master.sys.databases
GO

image

 

Using master.sys.symmetric_keys ?

select
        [name]
      , [principal_id]
      , [algorithm_desc]
      , [create_date]
from master.sys.symmetric_keys

image

 

If a master keys exists, then a record bearing “…MS_DatabaseMasterKey..” will be returned;

 

References?

  1. Transparent Data Encryption (TDE) in SQL Server 2008
    http://www.databasejournal.com/features/mssql/article.php/3883391/Transparent-Data-Encryption-TDE-in-SQL-Server-2008.htm

 

 

Create Master Key

http://msdn.microsoft.com/en-us/library/ms187798.aspx

 

In the event, that one does not exist, then create one.  To create a master key issue:

Syntax:


use [master]

CREATE MASTER KEY ENCRYPTION BY PASSWORD = [master-key-encryption];

Sample:

use [master]

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'super-secure-tera2010!';

 

 

Error Messages:

  1. If you try to create more master keys while one already exists, you will get an error stating “There is already a master key in the database.  Please drop it before performing this statement.”

 

Backup Master Key

Just as in the real world, make a second copy and save in a safe place.

Syntax:

BACKUP MASTER KEY
TO FILE= [file-name]
ENCRYPTION BY PASSWORD =  [password]
;

 

Sample:

BACKUP MASTER KEY
TO FILE='D:\MSSQL\Certs\MasterKey\LAB\LAB.crt'
ENCRYPTION BY PASSWORD =  'super-secure-tera2010!';

Error Messages:

  1. One of the errors that might comes states that “Cannot write into file” –> Cannot write into file ‘<file-name>’. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

 

  • If you experience this error, please check, and re-check the file name.  Also, identify the account that the MS SQL Server Instance is running on and ensure that the account has NTFS create\write permissions to the designated FS

 

 

Review existing certificates?

Are they existing certificates that we can use?

The first step towards creating one, is to determine whether one exists


select

    name
 , certificate_id
 , principal_id
 , pvt_key_encryption_type_desc
 , start_date
 , expiry_date

from sys.certificates;

 

image

 

  1. Make sure that “pvt_key_encryption_type_desc” states “ENCRYPTED_BY_MASTER_KEY”

 

Create Certificate

2008 R2 :- http://msdn.microsoft.com/en-us/library/ms187798.aspx

Syntax:

use [master]

CREATE CERTIFICATE [certificate-name]
--ENCRYPTION BY PASSWORD = [password]
WITH SUBJECT = [subject-name]
     , EXPIRY_DATE = [expiry-date]
Sample:
use [master]
CREATE CERTIFICATE [SQLServerCertLAB]
--ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Certificate to protect TDE key'
 ,  EXPIRY_DATE = 'expiry-date'

 

Factors:

  1. Note that if “encryption by password” is not indicated, the server’s master key will be used.  And, in fact for TDE, only server certificates.  In the event that a request is made to “create database encryption” against a certificate encrypted by password, an error stating “Cannot find the certificate ‘<certificate>’, because it does not exist or you do not have permission.” is returned
    • Note that though the “Create Certificate” API accepts an expiry date, it is not used\consulted per TDE.
    • ”Server Service Broker checks the expiration date; however, expiration is not enforced when the certificate is used for encryption – http://msdn.microsoft.com/en-us/library/ms187798.aspx

 

 

Backup Certificate

Reference:

Again, just as in the real world, backup each certificate that you create

Syntax:

BACKUP CERTIFICATE [certificate-name]
TO FILE= 'filename'

 

Sample:

BACKUP CERTIFICATE [SQLServerCertLAB]
TO FILE='D:\MSSQL\Certs\MasterKey\LAB\LAB.crt'

 

Create Database Encryption Key

2008 R2 :- http://msdn.microsoft.com/en-us/library/bb677241.aspx

Syntax:

use [database-name];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
ENCRYPTION BY SERVER
{
   CERTIFICATE Encryptor_Name |   ASYMMETRIC KEY Encryptor_Name
};

 

Sample:


use [LAB];

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SQLServerCert;
GO

 

Factors:

  1. As a reminder, Only Server Certificates are allowed
    • If one tries to create a new database encryption key, while an existing database encryption key exists, an error is generated:

Msg 33103, Level 16, State 1, Line 1
A database encryption key already exists for this database.

 

Effect Encryption

To effect encryption, issue

Syntax:

ALTER DATABASE [database-name]
SET ENCRYPTION ON;
GO

 

Sample

ALTER DATABASE [LAB]
SET ENCRYPTION ON;
GO

 

Turn Encryption Off

To turn encryption off:

DROP DATABASE ENCRYPTION KEY (Transact-SQL) – http://msdn.microsoft.com/en-us/library/bb630256.aspx

 


ALTER DATABASE [database-name]
SET ENCRYPTION OFF;

/* Wait for decryption operation to complete, look for a
value of  1 in the query below. */
SELECT encryption_state
FROM   sys.dm_database_encryption_keys;
GO

USE [database-name]
GO

DROP DATABASE ENCRYPTION KEY;
GO

 

 

Get Information on Specific Database Encryption Settings

Get Information on Database Encryption Settings:

SELECT
      db_name(db_id()) as databaseName
    , case
      when (encryption_state = 0) then 'No database encryption key present, no encryption'
      when (encryption_state = 1) then 'Unencrypted'
      when (encryption_state = 2) then 'Encryption in progress'
      when (encryption_state = 3) then 'Encrypted'
      when (encryption_state = 4) then 'Key change in progress'
      when (encryption_state = 5) then 'Description in progress'
      when (encryption_state = 5) then 'Protection change in progress'
   end as encryption_state_desc
     , create_date
     , modify_date
     , set_date
     , opened_date
     , key_algorithm
     , percent_complete as percentCompleteIfModeIsCurrentChanging
FROM sys.dm_database_encryption_keys;
GO

 

image

 

 

DROP DATABASE ENCRYPTION

DROP DATABASE ENCRYPTION KEY (Transact-SQL) – http://msdn.microsoft.com/en-us/library/bb630256.aspx
 


/* Wait for decryption operation to complete, look for a
value of  1 in the query below. */
SELECT encryption_state
FROM sys.dm_database_encryption_keys;
GO

USE [database-name]
GO

DROP DATABASE ENCRYPTION KEY;
GO

 

Other Considerations

Database

  1. Only User databases can be encrypted – Database encryption statements are allowed only on user databases (CREATE DATABASE ENCRYPTION KEY (Transact-SQL) – http://msdn.microsoft.com/en-us/library/bb677241.aspx)

 

References:

Introduction

 

Backing up Master Key

 

Error Backing up Master Key

 

System Tables

  1. sys.dm_database_encryption_keys (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/bb677274.aspx

 

Impact on System Operations

 

Leave a Reply

Please log in using one of these methods to post your comment:

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