Encryption Decryption Routine

,

In this post we will look at a complete end to end routine for encrypting, storing, decrypting data in SQL Server and just how easy it is to set-up and maintain.  SQL Server encrypts data with a hierarchical encryption and key management infrastructure.  Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys.  It is important to understand the different layers of protection, how they interact the performance overhead and best practices.  Books Online has a great visualisation of the SQL Server encryption hierarchy which I have included below.



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

USESQLServer365;

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


-- 

Change the path to the certificate backup

BACKUP CERTIFICATE CertBankDetails TOFILE = '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

USESQLServer365

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 TOFILE = '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 KEYSymKeyBankDetails

      WITH ALGORITHM= AES_256

    ENCRYPTION BY CERTIFICATECertBankDetails;

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 KEYSymKeyBankDetails

   DECRYPTION BY CERTIFICATECertBankDetails;

-- 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 KEYSymKeyBankDetails

   DECRYPTION BY CERTIFICATECertBankDetails;

  

-- Return decrypted record

SELECTBankDetailsID,

      CustomerID,

      CONVERT(VARCHAR, DecryptByKey(SortCode)) AS SortCode, -- Decrypt SortCode

      CONVERT(VARCHAR, DecryptByKey(AccountNumber)) AS AccountNumber, -- Decrypt AccountNumber

      InsertDate

FROMSQLServer365.dbo.BankDetails;

END

GO

-- Insert an encrypted record

EXECSQLServer365.dbo.spInsertBankDetails;

GO

-- Return encrypted data

SELECTBankDetailsID,

      CustomerID,

      SortCode,

      AccountNumber,

      InsertDate

  FROMSQLServer365.dbo.BankDetails;

GO 

 

-- Return decrypted data

EXECSQLServer365.dbo.spGetBankDetails;

GO

/*

      Unencrypted data for performance comparison

*/

-- Set database context

USESQLServer365

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] PRIMARYKEY,

      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

EXECSQLServer365.dbo.spInsertBankDetailsNoEncryption;

GO

-- Return data

EXECSQLServer365.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

Be aware of the expiry date of the certificates!

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

Rate

Share

Share

Rate