Script
Below is the script used to create both the encryption / decryption routine and the equivalent routine without encryption. You will need to Modify the below;
-- Change the database name below
USE
SQLServer365;
GO
-- Change the path to the database master key backup
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey'
ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';
GO
--
BACKUP CERTIFICATE CertBankDetails TO
FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';
GO
/*
-----------------------------------------------------------------
Encryption / Decryption Routine
-----------------------------------------------------------------
For more SQL resources, check out
SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own
purposes.
You may republish altered code as long as
you give due credit.
You must obtain prior permission before
blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED
"AS IS"
-----------------------------------------------------------------
*/
-- Set database
context
USE
SQLServer365
GO
-- Create table
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.BankDetails')
AND [type] = 'U' )
CREATE TABLE dbo.BankDetails(
BankDetailsID INT IDENTITY(1,1) NOT NULL CONSTRAINT [PK_BankDetails:BankDetailsID] PRIMARY KEY,
CustomerID INT NOT NULL,
SortCode VARBINARY(128) NOT NULL,
AccountNumber VARBINARY(128) NOT NULL,
InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetails:InsertDate] DEFAULT (GETDATE())
) ON [PRIMARY]
ELSE
PRINT 'Error:
Table "dbo.BankDetails" already exists, please modify the script to
create a table name that does not already exist';
GO
-- Create
database master key
/*
This is the database master key that is
used to encrypt all certificates when a password is not supplied
*/
IF NOT EXISTS ( SELECT 1
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB'
ELSE
PRINT 'Error:
Database master key already exists!'
GO
-- Backup master
key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey'
ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';
GO
-- Create
certificate
/*
This is the certificate used to protect
the symmetric key
*/
IF NOT EXISTS ( SELECT 1
FROM sys.certificates
WHERE name = 'CertBankDetails' )
CREATE CERTIFICATE CertBankDetails
WITH SUBJECT = 'Bank Details Certificate',
EXPIRY_DATE = '02/25/2014';
ELSE
PRINT 'Error:
Certificate "CertBankDetails" already exists, please modify the
script to create a certificate that does not already exist';
GO
-- Backup the
certificate
BACKUP CERTIFICATE CertBankDetails TO
FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';
GO
-- Create
symmetric key
/*
This is the symmetric key used in
conjunction with the certificate to encrypt / decrypt the data
*/
IF NOT EXISTS ( SELECT 1
FROM sys.symmetric_keys
WHERE name = 'SymKeyBankDetails' )
CREATE SYMMETRIC KEY
SymKeyBankDetails
WITH ALGORITHM
= AES_256
ENCRYPTION BY CERTIFICATE
CertBankDetails;
ELSE
PRINT 'Error:
Symmetric key "SymKeyBankDetails" already exists, please modify the
script to create a symmetric key that does not already exist';
GO
-- Create
Encryption Proc
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.spInsertBankDetails')
AND
[type] IN ( 'P' ) )
BEGIN
DROP PROCEDURE dbo.spInsertBankDetails
END
GO
CREATE PROCEDURE dbo.spInsertBankDetails
AS
BEGIN
-- Open Symetric
Key
OPEN SYMMETRIC KEY
SymKeyBankDetails
DECRYPTION BY CERTIFICATE
CertBankDetails;
-- Insert a
record
INSERT INTO SQLServer365.dbo.BankDetails
(CustomerID,
SortCode,
AccountNumber,
InsertDate)
VALUES
(1,
EncryptByKey(Key_GUID('SymKeyBankDetails'), '01-02-03'), -- Encrypt SortCode
EncryptByKey(Key_GUID('SymKeyBankDetails'), '01234567'), -- Encrypt AccountNumber
GETDATE());
END
GO
-- Create
Decryption Proc
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.spGetBankDetails')
AND
[type] IN ( 'P' ) )
BEGIN
DROP PROCEDURE dbo.spGetBankDetails
END
GO
CREATE PROCEDURE dbo.spGetBankDetails
AS
BEGIN
-- Open Symetric
Key
OPEN SYMMETRIC KEY
SymKeyBankDetails
DECRYPTION BY CERTIFICATE
CertBankDetails;
-- Return
decrypted record
SELECT
BankDetailsID,
CustomerID,
CONVERT(VARCHAR, DecryptByKey(SortCode)) AS SortCode, -- Decrypt SortCode
CONVERT(VARCHAR, DecryptByKey(AccountNumber)) AS AccountNumber, -- Decrypt AccountNumber
InsertDate
FROM
SQLServer365.dbo.BankDetails;
END
GO
-- Insert an
encrypted record
EXEC
SQLServer365.dbo.spInsertBankDetails;
GO
-- Return
encrypted data
SELECT
BankDetailsID,
CustomerID,
SortCode,
AccountNumber,
InsertDate
FROM
SQLServer365.dbo.BankDetails;
GO
-- Return
decrypted data
EXEC
SQLServer365.dbo.spGetBankDetails;
GO
/*
Unencrypted data for performance
comparison
*/
-- Set database
context
USE
SQLServer365
GO
-- Create table
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.BankDetailsNoEncryption')
AND [type] = 'U' )
CREATE TABLE dbo.BankDetailsNoEncryption(
BankDetailsNoEncryptionID INT IDENTITY(1,1) NOT NULL CONSTRAINT
[PK_BankDetailsNoEncryption:BankDetailsNoEncryptionID] PRIMARY
KEY,
CustomerID INT NOT NULL,
SortCode VARCHAR(50) NOT NULL,
AccountNumber VARCHAR(50) NOT NULL,
InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetailsNoEncryption:InsertDate] DEFAULT (GETDATE())
) ON [PRIMARY]
ELSE
PRINT 'Error:
Table "dbo.BankDetailsNoEncryption" already exists, please modify the
script to create a table name that does not already exist';
GO
-- Create Insert
Proc
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.spInsertBankDetailsNoEncryption')
AND
[type] IN ( 'P' ) )
BEGIN
DROP PROCEDURE dbo.spInsertBankDetailsNoEncryption
END
GO
CREATE PROCEDURE dbo.spInsertBankDetailsNoEncryption
AS
BEGIN
-- Insert a
record
INSERT INTO SQLServer365.dbo.BankDetailsNoEncryption
(CustomerID,
SortCode,
AccountNumber,
InsertDate)
VALUES
(1,
'01-02-03',
'01234567',
GETDATE());
END
GO
-- Create Get
Proc
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =
OBJECT_ID('dbo.spGetBankDetailsNoEncryption')
AND
[type] IN ( 'P' ) )
BEGIN
DROP PROCEDURE dbo.spGetBankDetailsNoEncryption
END
GO
CREATE PROCEDURE dbo.spGetBankDetailsNoEncryption
AS
BEGIN
SELECT BankDetailsNoEncryptionID ,
CustomerID ,
SortCode ,
AccountNumber InsertDate
FROM SQLServer365.dbo.BankDetailsNoEncryption;
END
GO
-- Insert
unencrypted record
EXEC
SQLServer365.dbo.spInsertBankDetailsNoEncryption;
GO
-- Return data
EXEC
SQLServer365.dbo.spGetBankDetailsNoEncryption;
GO
Performance Comparison
I did some performance analysis comparing the insert encrypting the data / the select decrypting the data to the equivalent without encryption and decryption. I used SQLQueryStress by Adam Machanic to execute the insert and select of both routines 100 times across 10 threads, the results of which I have to say might surprise a few of you;
UnencryptedInsert
|
EncryptedInsert
|
IncreasePercentage
|
|
Execution
Time
|
0.73
|
2.5712
|
252.22
|
Client
Seconds / Iteration (Avg)
|
0.0055
|
0.022
|
300.00
|
Logical
Reads / Iteration (Avg)
|
2.034
|
2.114
|
3.93
|
CPU
Seconds / Iteration (Avg)
|
0.0002
|
0.0033
|
1550.00
|
Actual
Seconds / Iteration (Avg)
|
0.0076
|
0.0288
|
278.95
|
UnencryptedSelect
|
EncryptedSelect
|
IncreasePercentage
|
|
Execution
Time
|
0.582
|
3.7593
|
545.93
|
Client
Seconds / Iteration (Avg)
|
0.0032
|
0.0267
|
734.38
|
Logical
Reads / Iteration (Avg)
|
10
|
26
|
160.00
|
CPU
Seconds / Iteration (Avg)
|
0.0012
|
0.0103
|
758.33
|
Actual
Seconds / Iteration (Avg)
|
0.0017
|
0.0319
|
1776.47
|
With this significant overhead, I recommend you make sure you have the capacity to make use of SQL Servers encryption hierarchy. It is important to be selective, only encrypt data that you actually need to. Investigate the use of an Hardware Security Module (HSM) as these add a layer of abstraction by keeping the encryption keys separate from the the encrypted data. It is also possible to offload the encryption overhead from the SQL Server to the HSM for improved performance.
I will finish with 3 recommendations;
Backup the certificates and keys!
Simple really, make sure you backup all your database master keys and all your certificates, the usual precautions apply here as they do for all backups;
- Back them up to a different drive
- Back them up to tape / different array
- Get them off site
Again this goes without saying but you don't want the be the person responsible when the applications are throwing errors as the certificate has expired :)
Scripts Save Life's!
As I have said many times before, I'm not a GUI fan. Each to their own but you really should be scripting this stuff! Scripts can be saved, backed up, recovered and the end result is achieved quicker than a GUI or Wizard if you have the scripts to hand.
Enjoy!
Chris
