SQL Server 365: 2013

So, the end of 2013 is upon us and what a year it has been. The SQL Server 365 blog saw the number of site hits double from last year, which I am thrilled with. The biggest personal achievement this year was popping my speaking cherry at the Leeds SQL Server User Group, on top of that I also spoke at the inaugural Newcastle SQL Server User Group, many thanks to Chris Taylor for having me. If that wasn't enough I was also honored to have spoken at the Manchester leg of SQL Relay R2, taking my tally to 3 appearances, 2 user groups and 1 conference! I also attended my first virtual chapter, seeing as most of these are US time it works perfectly for me as they are around 18:00 or 19:00 UK time. These are definitely worth a look, some fantastic content that can be attended from anywhere, check out the PASS site for more details.

So here is SQL Server 365 in numbers.

Site Hits - 24,210
Total Posts - 81

Top 5 Posts
Don't be a Balloon - 2447 Views
SQL Server Error Logs - 1134 Views
Cost Threshold for Parallelism - 897
Script Replication with Powershell - 716
Dedicated administrator Connection - 497 Views

Top 5 Countries
United States - 10,335 Views
United Kingdom - 3528 Views
India - 860 Views
Germany - 671 Views
China - 647 Views

Top 5 Browsers
Internet Explorer - 9573 Views
Chrome - 6377 Views
Firefox - 5604 Views
Safari - 829 Views
Mobile Safari - 232 Views

Top 5 OS's
Windows - 20,626 Views
Macintosh - 1314 Views
Linux - 583 Views
iPhone - 435 Views
Android - 338 Views

In 2014 I want to get even more involved in our fantastic SQL Server community, I have a couple of ideas for some collaboration and networking, which I want to get the ball rolling with early in the new year. As for the blog I will continue to post as often as I can, I already have a backlog of about 5 posts to keep me busy.

If there is anything you would like me to to do a post on or would like to do a guest post then please get in touch and we can arrange it! ckwmcgowan@gmail.com or @ckwmcgowan.

Thank you to the entire SQL Server community worldwide for their continuing support and general awesomeness, keep it up and hears to a prosperous 2014.

Happy New Year!

Chris

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

USE SQLServer365;

-- 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';

-- Change the path to the certificate backup

BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';

-----------------------------------------------------------------

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

-- 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';

-- 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!'

-- 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';

-- 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';

-- Backup the certificate

BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';

-- 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';

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

CREATE PROCEDURE dbo.spInsertBankDetails

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

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

CREATE PROCEDURE dbo.spGetBankDetails

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

-- Insert an encrypted record

EXEC SQLServer365.dbo.spInsertBankDetails;

-- Return encrypted data

SELECT BankDetailsID,

CustomerID,

SortCode,

AccountNumber,

InsertDate

FROM SQLServer365.dbo.BankDetails;

-- Return decrypted data

EXEC SQLServer365.dbo.spGetBankDetails;

Unencrypted data for performance comparison

-- Set database context

USE SQLServer365

-- 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';

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

CREATE PROCEDURE dbo.spInsertBankDetailsNoEncryption

BEGIN

-- Insert a record

INSERT INTO SQLServer365.dbo.BankDetailsNoEncryption

(CustomerID,

SortCode,

AccountNumber,

InsertDate)

VALUES

(1,

'01-02-03',

'01234567',

GETDATE());

END

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

CREATE PROCEDURE dbo.spGetBankDetailsNoEncryption

BEGIN

SELECT BankDetailsNoEncryptionID ,

CustomerID ,

SortCode ,

AccountNumber InsertDate

FROM SQLServer365.dbo.BankDetailsNoEncryption;

END

-- Insert unencrypted record

EXEC SQLServer365.dbo.spInsertBankDetailsNoEncryption;

-- Return data

EXEC SQLServer365.dbo.spGetBankDetailsNoEncryption;

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

SQL Server 365

Tuesday, 31 December 2013

2013 - The Year of Community Speaking

Thursday, 17 October 2013

PASS Summit

Wednesday, 11 September 2013

Encryption Decryption Routine