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
