Database Encryption with DB Certificates

SQL Servers Database Encryption on Swim lanes

This document is create on May 2^nd, 2012 and is valid for Swim Lanes. Certificates are installed on LD4PRDCLT101 and this version of the certificate and key should be used across all swim lanes if needed.

Create a new Master Key

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';

Go

--To Drop the masterkey

--DROP MASTER KEY

Create a new Certificate per Database that need to be encrypted

--database encryption key

CREATE CERTIFICATE DatabaseName1_Cert WITH SUBJECT = 'DatabaseName1 Certificate'

Go

CREATE CERTIFICATE DatabaseName2_Cert WITH SUBJECT = 'DatabaseName2 Certificate'

Go

CREATE CERTIFICATE DatabaseName3_Cert WITH SUBJECT = 'DatabaseName3 Certificate'

Go

--To Drop the certificate

-- DROP CERTIFICATE DatabaseName1_Cert

Backup Master Key and certificates and use for restoring databases on a different server

      Use master

GO

Backup master key to file = 'C:\MasterKey.key' encryption by password = 'Password' ;

BACKUP CERTIFICATE DatabaseName1_Cert TO FILE = 'c:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName1_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName2_Cert TO FILE = 'c:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName2_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName3_Cert TO FILE = 'c:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName3_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

Create certificates from a backup certificate on another server, (note:  the master key should be created before this can be done)

CREATE CERTIFICATE DatabaseName1_Cert

FROM FILE = 'C:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName1_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName2_Cert

FROM FILE = 'C:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName2_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName3_Cert

FROM FILE = 'C:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName3_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

Encrypt a Database

USE [DatabaseName1]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName1_Cert

GO

ALTER DATABASE [DatabaseName1]

SET ENCRYPTION ON

GO

USE [DatabaseName2]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName2_Cert

GO

ALTER DATABASE [DatabaseName2]

SET ENCRYPTION ON

GO

USE [DatabaseName3]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName3_Cert

GO

ALTER DATABASE [DatabaseName3]

SET ENCRYPTION ON

GO

Restore the Databases on a separate server, the key and certificates should be already created and restored. If the certificates are not created properly the restore will fail.

RESTORE DATABASE [DatabaseName1_retored]

FROM  DISK = N'c:\DatabaseName1.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName1.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName1.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName2_retored]

FROM  DISK = N'c:\DatabaseName2.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName2.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName2.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName3_retored]

FROM  DISK = N'c:\DatabaseName3.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName3.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName3.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

View which Databases are encrypted

SELECT db_name(database_id), encryption_state, *

FROM sys.dm_database_encryption_keys with (nolock)

System Tables to view the encryption keys

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.certificates

SELECT * FROM sys.dm_database_encryption_keys

Remove Encryption from Database

ALTER DATABASE [DatabaseName1];

SET ENCRYPTION OFF;

GO

use [DatabaseName1]

Go

DROP DATABASE ENCRYPTION KEY;

GO