SQL Server – Data Encryption – Foundational

Background

This is my first look at Data Encryption in MS SQL Server.

We will cover what is required and the general API sets.

In later posts, we will look at optimization, key management ( what needs to be secured), etc.

 

 

Key Management

Here the a couple of things that we need:

  1. Master Key
  2. Certificate
  3. Symmetric key

Master Key Creation



set nocount on;

if db_id('dbLAB') is null
begin

	exec('create database [dbLAB];')

end
go

use [dbLAB]
go

/*

	CREATE MASTER KEY (Transact-SQL)
	https://msdn.microsoft.com/en-us/library/ms174382.aspx


	BACKUP MASTER KEY (Transact-SQL)
	https://msdn.microsoft.com/en-us/library/ms174387.aspx

*/


declare @symmetrickeyIDMaster int

declare @keyAlgorithm			 varchar(30)
declare @keyLength				 smallint
declare @AlgorithmDescription    varchar(30)
declare @backupMasterKey	     bit = 0

declare @iFileExistsBackup INT

/*
	Symmetric Key - Master - 101
*/
set @symmetrickeyIDMaster = 101
set @backupMasterKey = 1

set @keyAlgorithm = null
set @keyLength = null
set @AlgorithmDescription = null


/*
	Get Info for Master Key
	by querying the sys.symmetric_keys catalog view.

	-- select * from sys.symmetric_keys tblSK
*/
SELECT 
		  @keyAlgorithm = tblSK.key_algorithm
		, @keyLength = tblSK.key_length
		, @AlgorithmDescription = tblSK.algorithm_desc

FROM   sys.symmetric_keys tblSK

WHERE  tblSK.symmetric_key_id = @symmetrickeyIDMaster


/*
	Create master key if it does not exist!
*/
if (@keyAlgorithm is null)
begin

	print 'Master key does not exist ...'
	
		CREATE MASTER KEY ENCRYPTION BY 
		PASSWORD = 'n8ceyAP6UHevudrawrucRus7';

	print 'Master key created ...'

end
else
begin

	print 'Master Key exists!'

end

if (@backupMasterKey = 1)
begin

	print 'Backup master key ....'

	/*

		Msg 15313, Level 16, State 1, Line 111
		The key is not encrypted using the specified decryptor.

	*/

	OPEN MASTER KEY DECRYPTION BY PASSWORD = 'n8ceyAP6UHevudrawrucRus7'


		exec master.dbo.xp_fileexist 
					  @filename = 'd:\SQLServerMasterKey4DB__dbLAB.mssqlMaskeyKey'
					, @iFileExistsBackup = @iFileExistsBackup output


		if (@iFileExistsBackup != 1)
		begin

			BACKUP MASTER KEY TO FILE = 'd:\SQLServerMasterKey4DB__dbLAB.mssqlMaskeyKey'
				ENCRYPTION BY PASSWORD = 'th8y7cuKuN5rathESt5JAcha';
		
			print 'Master key backed up'

		end
		else
		begin

			print 'Backup file already exist.  Skipping backup!'

		end


	CLOSE MASTER KEY;


end
go


Certificates



use [dbLAB]
go

/*
	CREATE Certificate
	https://msdn.microsoft.com/en-us/library/ms187798.aspx

*/


declare @certificateID int
declare @certificate   varchar(60) = 'certPIA'

set @certificateID = null

-- select * from sys.certificates dmvSC
select @certificateID = dmvSC.certificate_id
from   sys.certificates dmvSC
where  dmvSC.name = @certificate

if (@certificateID is null)
begin

	print 'Creating Certificate ...'

	CREATE CERTIFICATE [certPIA]
		AUTHORIZATION dbo
		WITH SUBJECT = 'certPIA'
			;
	
	print 'Created Certificate'


end
go


Create Symmetric Key


use [dbLAB]
go

/*
	CREATE SYMMETRIC KEY
	https://msdn.microsoft.com/en-us/library/ms188357.aspx

	DROP SYMMETRIC KEY
	https://msdn.microsoft.com/en-us/library/ms182698.aspx

*/


declare @symmetricKeyID		    int
declare @symmetricKeyLength	    int

declare @symmetricKey		    varchar(60) = 'symmetricKeyPIA'
declare @symmetricKeyidentityValue  varchar(60) = 'symmetricKeyPIA'

declare @symmetricKeyAlgorithm	    varchar(255) = null
declare @symmetricKeyAlgorithmDesc  varchar(255) = null

set @symmetricKeyID = null
set @symmetricKeyLength = null
set @symmetricKeyAlgorithm = null
set @symmetricKeyAlgorithmDesc = null


--select * from sys.symmetric_keys
select 
		  @symmetricKeyID = dmvSK.symmetric_key_id
		, @symmetricKeyLength = dmvSK.key_length
		, @symmetricKeyAlgorithm = dmvSK.key_algorithm
		, @symmetricKeyAlgorithmDesc = dmvSK.algorithm_desc
from   sys.symmetric_keys dmvSK
where  dmvSK.name = @symmetricKey

if  (@symmetricKeyID is null)
begin

    print 'Creating Symmetric Key ...'

   /*
    http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/
    The IDENTITY_VALUE parameter is used to generate the guid for the key 
    and the KEY_SOURCE is used to generate the actual key. 
    This allows you to run the above code on any server as many times as you like, 
    with the same values, to generate the exact same key.
  */
  CREATE SYMMETRIC KEY [SymmetricKeyPIA]
	AUTHORIZATION [dbo]
	WITH 
		  ALGORITHM = AES_256
		, IDENTITY_VALUE = 'SymmetricKeyPIA'
		, KEY_SOURCE = 'SymmetricKeyPIA'

	ENCRYPTION BY CERTIFICATE [certPIA]
	;
	
	print 'Created Symmetric Key'


end

go


Targeted Objects

Tables

[dataPlain].[accountHolder]

 


set noexec off
go


if schema_id('dataPlain') is not null
begin

	set noexec on

end
go


create schema [dataPlain]
	authorization [dbo]
	;

go


/*

	drop table [dataPlain].[accountHolder];
	drop table [encryptionData].[accountHolder];

*/
if object_id('[dataPlain].[accountHolder]') is null
begin

	create table [dataPlain].[accountHolder]
	(
		  [id]		bigint not null identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null
							constraint  [dataPlain.defaultAddedBy]
								default SYSTEM_USER

		, [addedOn]     datetime not null
							constraint  [dataPlain.defaultAddedOn]
								default getdate()
							
  	    , constraint [dataPlain.PK_ACCOUNTHOLDER] primary key
			(
				[entityID]
			)


	)

end
go


 

[dataEncrypted].[accountHolder]

 



set noexec off
go

if schema_id('dataEncrypted') is not null
begin

	set noexec on

end
go


create schema [dataEncrypted]
	authorization [dbo]
	;

go

set noexec off
go




/*

	drop table [dataEncrypted].[accountHolder];

*/
if object_id('[dataEncrypted].[accountHolder]') is null
begin

 create table [dataEncrypted].[accountHolder]
 (

      [id]  bigint not null identity(1,1)
    , [entityID]    varbinary(300) not null  
    , [firstname]   varbinary(300) not null
    , [lastname]    varbinary(300) not null

    , [addedBy]	    nvarchar(60) not null
			constraint  [dataEncrypted.defaultAddedBy]
				default SYSTEM_USER

   , [addedOn]	   datetime not null
			 constraint  [dataEncrypted.defaultAddedOn]
			      default getdate()
							
   , constraint [dataEncrypted.PK_ACCOUNTHOLDER] primary key
		(
  		  [entityID]
		)
  )

end
go

 

Sample Data

SQL Data Generator

TemplateDefinition

Generated Data


INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('191-14-338', 'Damaris', 'Boyle');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('188-92-921', 'Pearl', 'Frye');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('328-41-549', 'Devan', 'Everett');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('812-31-537', 'Elliot', 'Nunez');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('602-56-283', 'Angel', 'Petersen');

 

Revised

Removed single quotes from column names


INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('191-14-338', 'Damaris', 'Boyle');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('188-92-921', 'Pearl', 'Frye');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('328-41-549', 'Devan', 'Everett');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('812-31-537', 'Elliot', 'Nunez');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('602-56-283', 'Angel', 'Petersen');


 

Encrypt data

 


set nocount on;

truncate table [dataEncrypted].[accountHolder]
go

/*
	https://msdn.microsoft.com/en-us/library/ms174361.aspx
	Using the SQL Server encryption functions together with the ANSI_PADDING OFF setting,
	could cause data loss because of implicit conversions. 
	For more information about ANSI_PADDING, see SET ANSI_PADDING (Transact-SQL).
*/
set ansi_padding on;

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY [SymmetricKeyPIA]
   DECRYPTION BY CERTIFICATE  [certPIA];

set identity_insert [dataEncrypted].[accountHolder] on;

declare @keyGUID uniqueIdentifier = key_guid('SymmetricKeyPIA')

insert into [dataEncrypted].[accountHolder]
(
	  [id]
    , [entityID] 
	, [firstname]
	, [lastname]
)
select 
		    tblAH.[id]

		  , ENCRYPTBYKEY(
						  @keyGUID -- Is the GUID of the key to be used to encrypt the cleartext
						, CONVERT(varbinary(8000), tblAH.[entityID]) -- value
						, 1 -- add_authenticator
						, CONVERT(varbinary(8000), tblAH.[id]) -- authenticator is the ID Column
					 ) as [entityID]


		 , ENCRYPTBYKEY(
						  key_guid('SymmetricKeyPIA')
						, CONVERT(varbinary(8000), tblAH.[firstname])
						, 1
						, CONVERT(varbinary(8000), tblAH.[id])
  					   ) as [firstname]


		 , ENCRYPTBYKEY(
						  key_guid('SymmetricKeyPIA')
						, CONVERT(varbinary(8000), tblAH.[lastname])
						, 1
						, CONVERT(varbinary(8000), tblAH.[id])
  					   ) as [lastname]

from    [dataPlain].[accountHolder] tblAH

CLOSE SYMMETRIC KEY [SymmetricKeyPIA];


set identity_insert [dataEncrypted].[accountHolder] off;

 

 

Query Data

Query – Plain data


select top 5 
		  [id]
		, [entityID]
		, [firstname]
		, [lastname]
from   [dataPlain].[accountHolder]
order by id

 

queryDataPlain

 

Query – Encrypted data

Simple Mind

queryDataSimpleMind

Procedural



-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY [SymmetricKeyPIA]
   DECRYPTION BY CERTIFICATE  [certPIA];


select 
     tblAH.id
   , cast(
	    DECRYPTBYKEY
               (
		   tblAH.[entityID]
		 , 1
		 , CONVERT(varbinary(8000), tblAH.[id])
	       )
		as varchar(255)
	) as [entityID]



  , cast
	(
 	   DECRYPTBYKEY(
	 	           tblAH.[firstname]
			 , 1
			 , CONVERT(varbinary(8000), tblAH.[id])
			)
			as nvarchar(255)
	) as [firstname]


  , cast
	(
	  DECRYPTBYKEY(
	                  tblAH.[lastname]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
	 	      )
		as nvarchar(255)
	) as [lastname]


from    [dataEncrypted].[accountHolder] tblAH

order by tblAH.[id] asc

CLOSE SYMMETRIC KEY [SymmetricKeyPIA];


Declarative


SELECT top 5
	  [id]
	, cast
		(
		   DecryptbyKeyAutoCert
			(
			      cert_id('certPIA')
			    , null
			    , tblAH.[entityID]
			    , 1
			    , CONVERT(varbinary(8000), tblAH.[id])
			)
			as varchar(80)
		) as [entityID]

	, cast
		(
		  DecryptbyKeyAutoCert
			(
			     cert_id('certPIA')
		  	   , null
			   , tblAH.[firstname]
			   , 1
			   , CONVERT(varbinary(8000), tblAH.[id])
			)
			as varchar(80)
		) as [firstnameIncorrectCastedToVarchar]

	, cast
	     (
		DecryptbyKeyAutoCert
		   (
		      cert_id('certPIA')
		    , null
		    , tblAH.[firstname]
		    , 1
		    , CONVERT(varbinary(8000), tblAH.[id])
		   )
			as nvarchar(80)
	     ) as [firstname]


	, cast
	    (
		DecryptbyKeyAutoCert
			(
		              cert_id('certPIA')
			    , null
			    , tblAH.[lastname]
			    , 1
			    , CONVERT(varbinary(8000), tblAH.[id])
			)
			  as nvarchar(80)
	  ) as [lastname]


from    [dataEncrypted].[accountHolder] tblAH

order by [id]
go

 

Comparative Analysis

Storage Requirements

Plain Data

plainData

Encrypted Data

encryptedData

Query Plan

encrypted

When dealing with encrypted data, prepare to see “Restricted Text”.

QueryCost-Restricted

Cleanup


/*
	Drop Table if it exists
*/
if object_id('[dataEncrypted].[accountHolder]') is not null
begin

	print 'Drop table - [dataEncrypted].[accountHolder]..'
	drop table [dataEncrypted].[accountHolder]

end
go

/*
	Symmetric Keys
*/
--select * from   sys.symmetric_keys dmvSK
if key_guid('SymmetricKeyPIA') is not null
begin

	print 'Drop SYMMETRIC KEY - SymmetricKeyPIA ..'

	DROP SYMMETRIC KEY [SymmetricKeyPIA]

end
go


/*
	Certificates
*/
--select * from  sys.certificates
if cert_id('certPIA') is not null
begin

	print 'Drop Certificate - certPIA ..'

	DROP CERTIFICATE [certPIA]

end
go


/*
	DROP MASTER KEY
*/

if exists
	(

		SELECT *
		FROM   sys.symmetric_keys tblSK
		WHERE  tblSK.symmetric_key_id = 101

	)
begin

	print 'Drop Master Key'

	DROP MASTER KEY;

end


 

Tecnical Summary

There we have it.

Authenticator

To thwart whole value substitution of encrypted data, we are using an authenticator.  In this case for the ease of simplicity the identity id of the row.

Please keep in mind that this value can not change through the life of our encrypted record.

 

Original Data Type

As encrypted data is saved as binary, one needs to keep a note of the original data types, once data is decrypted via DecryptbyKey or DecryptbyKeyAutoCert, one needs to cast/convert back to the original datatype.

One thought on “SQL Server – Data Encryption – Foundational

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s