Thursday, May 03, 2012

Database Encryption with DB Certificates


SQL Servers Database Encryption on Swim lanes
This document is create on May 2nd, 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

There was an error in this gadget
There was an error in this gadget