SQL Server 2005 –Encrypting data on existing applications

Published Mar 23 2019 04:20 AM 115 Views
Microsoft
First published on MSDN on May 04, 2006

SQL Server 2005 encryption requires the application to be aware of it and to decrypt the data before it can be consumed as well as encrypt (and verify that the encryption call succeeded) before storing it.



When you are writing new schemas and new applications you can design them with encryption in mind, but writing everything from scratch is not always an option. Most likely you already have some data that needs to be protected; while the new applications can be aware of these changes, the existing ones are not aware of them, and it may not be possible to update such applications immediately. Unfortunately, because of the nature of the problem itself, there is no solution that will work on all the cases.



If sacrificing performance (linear searches instead of seeks) during the transition phase is acceptable, it may be possible to modify the schema in such a way that the sensitive columns are protected, allowing new applications to be written  (for example, using equity-based indexing) while also allowing existing applications to continue working (with a degraded performance).



I wrote a small demo that hopefully will be useful if you face this problem, but as I already described, it may not work on all cases. I will appreciate any feedback and questions on this article and the demo I include below.



/******************************************************************


*


*   This posting is provided "AS IS"


*   with no warranties, and confers no rights.


*


* Title:    Transparent encryption demo


* Author:   Raul Garcia ( raulga@microsoft.com )


* History:  05/03/2006  - First version


*


* Summary:  Modifying an existing schema to


* support data at rest can affect existing applications


* that depend on the existing schema.


*   This demo is intended to show a few steps that


* can be used to help in such cases.


*


* Notes:


* This document is intended just as a demonstartion


* using a fictional application and schema.


* It is possible that the mechanisms used here may


* not apply to a particular application or schema.


*


******************************************************************


*


* (c) 2006 Microsoft Corporation. All rights reserved.


*


******************************************************************/



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


-- Create a test DB for our fictional application


CREATE DATABASE db_Demo


go


USE db_Demo


go



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


-- Our schema will consist only in this table


-- The values we are interested in protect at rest are:


--    * SSN  => It is also being used as a primary key


--    * name => Customer's name. We want to keep it


--      confidential, no indexes ae created for


--      this column


-- The rest of the columns are not sensitive


-- information and do not require to be protected


CREATE TABLE t_Customer(


SSN nvarchar(20) PRIMARY KEY,


name nvarchar(200)


NOT NULL,


Status int NOT NULL,


ExtraData nvarchar(100) )


go




-- The following modules will be our fictional


-- applciations



-- Prints the total number of active customers


CREATE PROC app_RunReport


AS


declare @Count int


SELECT @Count = count(*) FROM t_Customer


WHERE Status > 0


PRINT 'Total active customers: '


+ convert(varchar, @Count)


go



-- Updates the


CREATE PROC app_ChangeData(


@SSN nvarchar(20),


@name nvarchar(200),


@Status int,


@ExtraData nvarchar(100) )


AS


IF( @SSN is not null )


BEGIN


IF( (SELECT count(*) FROM t_Customer


WHERE SSN = @SSN ) > 0 )


BEGIN


IF( @name is not null )


UPDATE t_Customer


SET Name = @name


WHERE SSN = @SSN


IF( @Status is not null )


UPDATE t_Customer


SET Status = @Status


WHERE SSN = @SSN


IF( @ExtraData is not null )


UPDATE t_Customer


SET ExtraData = @ExtraData


WHERE SSN = @SSN


END


ELSE


PRINT 'Invalid SSN parameter. No entries with this SSN were found'


END


ELSE -- #SSN is null


PRINT 'Invalid SSN parameter. SSN cannot be null'


go



-- Returns 1 if the customer with the given SSN exists


-- and is active, 0 otherwise


CREATE FUNCTION dbo.isCustomerActive( @SSN nvarchar(20) )


RETURNS int


AS


BEGIN


DECLARE @RetVal int


SET @RetVal = 0


IF( Exists( SELECT * FROM t_Customer


WHERE @SSN = SSN AND Status > 0) )


SET @RetVal = 1


return @RetVal


END


go



-- A view that shows the active users only and hides the SSN


CREATE VIEW v_CustomerData


AS


SELECT Name, ExtraData as Data FROM t_Customer


WHERE Status <> 0


Go



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


-- Insert some dummy data


SET nocount on


DECLARE @i int


DECLARE @cmd varchar(max)


SET @i = 0


WHILE @i < 50000


BEGIN


SET @i = @i + 1


SET @cmd = 'INSERT INTO t_Customer values ( N''111-11-' + convert(varchar, @i) + ''', N''User ' + convert(varchar, @i) + ''', 1, N''Extra Data ' + convert(varchar, @i) + ''' )'


EXEC( @cmd )


END


go



-- common usage examples


INSERT INTO t_Customer VALUES ( N'2222-22-2222', N'New user',1, N'Extra Data ' )


EXEC app_RunReport


EXEC app_ChangeData N'111-11-108', N'New Name 108', null, null


EXEC app_ChangeData N'111-11-118', null, 0, N'Removed'


EXEC app_RunReport


SELECT * FROM t_Customer


SELECT * FROM t_Customer WHERE SSN = N'111-11-108'


SELECT * FROM t_Customer WHERE dbo.isCustomerActive( SSN ) = 0


SELECT * FROM v_CustomerData


SELECT * FROM v_CustomerData WHERE NAME LIKE '%108'


SELECT * FROM v_CustomerData WHERE NAME LIKE '%118'



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


--  Now let's modify the schema to protect the


-- sensitive data


-- We will allow indexing on the SNN based


-- based on the previous demo:


-- http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx


--


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Yukon900'


go



-- This certificate will be used to protect the


-- data-encryption key


CREATE CERTIFICATE cert_ProtectEncryptionKeys WITH SUBJECT = 'Data encryption key protection'


go



-- This key will be used to protect our plaintext data


CREATE SYMMETRIC KEY key_Encryption WITH ALGORITHM = TRIPLE_DES --AES_128


ENCRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys


go



-- This is the certificate that will protect our


-- MAC key-encryption key


CREATE CERTIFICATE cert_ProtectIndexingKeys WITH SUBJECT = 'Data indexing key protection'


go



-- This key will be used to protect the MAC keys


CREATE SYMMETRIC KEY key_Indexing WITH ALGORITHM = TRIPLE_DES --AES_128


ENCRYPTION BY CERTIFICATE cert_ProtectIndexingKeys


go



-- This table will store the encrypted MAC keys


-- for all tables


CREATE TABLE t_MacIndexKeys( table_id int PRIMARY KEY, Mac_key varbinary(100) not null )


go




CREATE FUNCTION MAC( @Message nvarchar(4000), @Table_id int )


RETURNS varbinary(24)


WITH EXECUTE AS 'dbo'


AS


BEGIN


declare @RetVal varbinary(24)


declare @Key      varbinary(100)


SET @RetVal = null


SET @key    = null


SELECT @Key = DecryptByKeyAutoCert( cert_id('cert_ProtectIndexingKeys'), null, Mac_key) FROM t_MacIndexKeys WHERE table_id = @Table_id


if( @Key is not null )


SELECT @RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @Message) + @Key )


RETURN @RetVal


END


go



--  This SP creates a new randomly generated MAC key


-- for the table specified by table_id


-- Parameters:


--    @Table_id   The table id (object_id) related


--     to the MAC key.


--


--  NOTE:  This SP will run under the DBO's context


-- to be able to access the required keys and tables.


--  It will not return any new or existing key


-- blob back, but it is recommended to only


-- grant execute permissions to trusted principals


-- who need to be able to create new MAC keys.


--


CREATE PROC AddMacForTable @Table_id int


WITH EXECUTE AS 'dbo'


AS


declare @Key       varbinary(100)


declare @KeyGuid uniqueidentifier


SET @KeyGuid = key_guid('key_Indexing')


-- Open the encryption key


-- Make sure the key is closed before doing


-- any operation


-- that may end the module, otherwise the key will


-- remain opened after the store-procedure execution ends


OPEN SYMMETRIC KEY key_Indexing DECRYPTION BY CERTIFICATE cert_ProtectIndexingKeys



-- The new MAC key is derived from an encryption


-- of a newly created GUID. As the encryption function


-- is not deterministic, the output is random


-- After getting this cipher, we calculate a


-- SHA1 Hash for it.


SELECT @Key = HashBytes( N'SHA1', ENCRYPTBYKEY( @KeyGuid, convert(varbinary(100), newid())) )



-- Protect the new MAC key


SET @KEY = ENCRYPTBYKEY( @KeyGuid, @Key )



-- Closing the encryption key


CLOSE SYMMETRIC KEY key_Indexing


-- As we have closed the key we opened,


-- it is safe to return from the SP at any time



if @Key is null


BEGIN


RAISERROR( 'Failed to create new key.', 16, 1)


END


INSERT INTO t_MacIndexKeys VALUES( @Table_id, @Key )


go




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



CREATE TABLE dbo.t_CustomerRaw(


-- MAC-based index (SSN)


SSN_index      varbinary(20) PRIMARY KEY,


-- ciphertext (SSN)


SSN_cipher     nvarchar(60) NOT NULL,


-- ciphertext, no index needed for name


Name_cipher nvarchar(300),


Status int,


ExtraData nvarchar(100) )


go



--- Create a new MAC key for this table


declare @objid int


SET @objid = object_id('t_CustomerRaw')


EXEC AddMacForTable @objid


go



-- Intercept the inserts and make sure the inserted


-- data is properly generated


CREATE TRIGGER trig_ProtectSSN on t_CustomerRaw


INSTEAD OF INSERT


AS


declare @Index varbinary(24)


declare @KeyGuid uniqueidentifier


declare @Cipher nvarchar(60)



if( select count(*) from inserted where SSN_cipher is null ) > 0


RAISERROR( 'Cannot store null as protected data. ', 16, 1)


ELSE


BEGIN


SET @KeyGuid = key_guid('key_Encryption')


SELECT @Index = dbo.MAC( SSN_cipher,


object_id('t_CustomerRaw') ) from inserted



if( @Index is null


OR @KeyGuid is null


OR encryptbykey( key_guid('key_Encryption'), 0x00)


is null )


BEGIN


RAISERROR( 'Cannot Insert protected data. Either the encryption or indexing keys are not available or the indexing key is not valid for MAC generation.', 16, 1)


END


ELSE


INSERT INTO dbo.t_CustomerRaw select


@Index,


encryptbykey( key_guid('key_Encryption'), SSN_cipher, 1, @Index ),


encryptbykey( key_guid('key_Encryption'), Name_cipher, 1, @Index ),


Status, Extradata


from inserted


END


go



-- Intercept any attempt to modify the RawData table


-- and prevent anyone from modifying the cipher values


CREATE TRIGGER trig_ProtectSSNUpdate on t_CustomerRaw


INSTEAD OF UPDATE


AS


-- We don't allow to update SSN-related columns


if( COLUMNS_UPDATED() & 3 ) > 0


raiserror( 'Cannot update protected columns. Drop the row and create a new one with the updated information.', 16, 1 )


ELSE


BEGIN


SET NOCOUNT ON


-- For name-related columns, as we need to


-- protect them, we require special handling


if( COLUMNS_UPDATED() & 4 ) > 0


BEGIN


if( encryptbykey( key_guid('key_Encryption'), 0x00) is null )


BEGIN


RAISERROR( 'Cannot Insert protected data. The encryption or indexing keys are not available.', 16, 1)


END


ELSE


UPDATE t_CustomerRaw SET Name_cipher = encryptbykey( key_guid('key_Encryption'), ins.Name_cipher, 1, ins.SSN_index )


FROM inserted ins, t_CustomerRaw orig


WHERE ins.SSN_index = orig.SSN_index


END


UPDATE t_CustomerRaw


SET Status = ins.Status, ExtraData = ins.ExtraData


FROM inserted ins, t_CustomerRaw orig


WHERE ins.SSN_index = orig.SSN_index


END


go



-- Open the symmetric key before we can use it


OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys


go



-- Do not do INSERT INTO .. SELECT, it will not


-- fire our trigger properly!!!


-- Warning: this call may take


-- a few minutes to complete


SET NOCOUNT ON


DECLARE @SSN nvarchar(15)


DECLARE @Name nvarchar(200)


DECLARE @Status int


DECLARE @ExtraData nvarchar(100)


DECLARE curs_Customer CURSOR FOR SELECT SSN, Name, Status, ExtraData FROM t_Customer


OPEN curs_Customer


FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData



WHILE @@FETCH_STATUS = 0


BEGIN


INSERT INTO t_CustomerRaw VALUES(  null, @SSN, @Name, @Status, @ExtraData )


FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData


END


CLOSE curs_Customer


DEALLOCATE curs_Customer


go



-- Verify that the new table is complete


-- and that the encrypted values are correctly


-- displayed when decrypted


SELECT


convert( nvarchar(15), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


SSN_cipher, 1, SSN_index)) as SSN,


convert( nvarchar(200), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


Name_cipher, 1, SSN_index)) as Name,


-- the rest of the data will remain the same


Status, ExtraData FROM dbo.t_CustomerRaw ORDER BY SSN


go



CLOSE SYMMETRIC KEY key_Encryption


go



-- Once we are sure nothing is missing,


-- we will drop the original table...


DROP TABLE t_Customer


go



-- ... and create a view with the same name


-- IMPORTANT NOTE: we will have no index for


-- the SSN column


CREATE VIEW t_Customer


WITH SCHEMABINDING


AS


SELECT


-- Use the certificate to automatically open


-- the encryption key.


-- Additionally use the index (MAC(k1, PT)) to


-- verify the decryption and prevent data tampering


-- such as copying encrypted values from one row


-- to another


convert( nvarchar(15), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


SSN_cipher, 1, SSN_index)) as SSN,


convert( nvarchar(200), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


Name_cipher, 1, SSN_index)) as Name,


-- the rest of the data will remain the same


Status, ExtraData FROM dbo.t_CustomerRaw


go



-- Intercept the inserts and make sure the inserted


--  data is properly generated


CREATE TRIGGER trig_ProtectView on t_Customer


INSTEAD OF INSERT


AS


SET NOCOUNT ON


INSERT INTO dbo.t_CustomerRaw select null, SSN, Name, Status, Extradata from inserted


go



-- Intercept the inserts and make sure the inserted


-- data is properly generated


CREATE TRIGGER trig_ProtectViewUp on t_Customer


INSTEAD OF UPDATE


AS


SET NOCOUNT ON


UPDATE dbo.t_CustomerRaw SET


Name_cipher = ins.Name,


Status = ins.Status,


Extradata = ins.Extradata


FROM inserted ins WHERE SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )


go



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


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


-- IMPORTANT NOTE:


-- Any application only trying to decrypt data will


-- continue working


-- But you will experince a performance degradation


-- the main two reasons for this degradation will be:


--  * Performing a linear search instead of a seek


--  * we will be decrypting the data for each row


-- for this linear search


-- This query may take a few minutes to complete!


EXEC app_RunReport


SELECT * FROM t_Customer


SELECT * FROM t_Customer WHERE SSN = N'111-11-308'


SELECT * FROM t_Customer WHERE SSN = N'111-11-318'


SELECT * FROM v_CustomerData


SELECT * FROM v_CustomerData WHERE NAME LIKE '%108'


SELECT * FROM v_CustomerData WHERE NAME LIKE '%118'



-- In this case we will experience


-- a ***huge*** perf impact!!!


-- In this case the degradation is exponential and


-- this particular function is rendered pretty


-- much useless


SELECT * FROM t_Customer WHERE dbo.isCustomerActive( SSN ) = 0


go



-- For the rest of the Applications where we need


-- to encrypt new data


-- a minor app change will be required.


-- When establishing the session (i.e we connect for


-- the first time)


-- We will need to open the symmetric key used to


-- encrypt data


OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys


go



EXEC app_ChangeData N'111-11-408', null, 0, N'Removed'


EXEC app_ChangeData N'111-11-418', N'New Name 418', null, N'named has changed'



SELECT * FROM t_Customer WHERE SSN = N'111-11-408'


SELECT * FROM t_Customer WHERE SSN = N'111-11-418'



INSERT INTO t_Customer VALUES ( N'333-33-3334', N'User 3333',1, N'Extra Data ' )


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



-- When no more updates/inserts are needed,


-- we can close the symmetric key


CLOSE SYMMETRIC KEY key_Encryption


go



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


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


-- As I explained, some applications may need


-- to change, otherwise the performance degradation


-- will render them unusable.


-- When re-writing a new application, it is possible


-- to create a view that is aware of the new schema


-- and use the MAC-based indexes as needed


CREATE VIEW v_CustomerDataEx


WITH SCHEMABINDING


AS


SELECT


SSN_index,


-- Use the certificate to automatically open


-- the encryption key.


-- Additionally use the index (MAC(k1, PT))


-- to verify the decryption and prevent data


-- tampering such as copying encrypted values from


-- one row to another


convert( nvarchar(15), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


SSN_cipher, 1, SSN_index)) as SSN,


convert( nvarchar(200), DecryptbyKeyAutoCert(


cert_id('cert_ProtectEncryptionKeys'), null,


Name_cipher, 1, SSN_index)) as Name,


-- the rest of the data will remain the same


Status, ExtraData FROM dbo.t_CustomerRaw


go



-- Intercept the inserts and make sure the


-- inserted data is properly generated


CREATE TRIGGER trig_CustomerDataEx_ins on v_CustomerDataEx


INSTEAD OF INSERT


AS


SET NOCOUNT ON


INSERT INTO dbo.t_CustomerRaw select null, SSN, Name, Status, Extradata from inserted


go



-- Intercept the inserts and make sure the inserted


-- data is properly generated


CREATE TRIGGER trig_CustomerDataEx_upg on v_CustomerDataEx


INSTEAD OF UPDATE


AS


SET NOCOUNT ON


UPDATE dbo.t_CustomerRaw SET


Name_cipher = ins.Name,


Status = ins.Status,


Extradata = ins.Extradata


FROM inserted ins


left outer join t_CustomerRaw raw ON raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )


WHERE raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )


go



-- Testing the new view for inserts


-- Remember to open the symmetric key


OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys


go



INSERT INTO v_CustomerDataEx VALUES ( null, N'555-55-5555', N'User 5555',1, N'Extra Data ' )


go


UPDATE v_CustomerDataEx SET


SSN_index = null,


SSN = N'555-55-5555',


Name = N'New User 5555',


Status = 0,


ExtraData = N'Extra Data2'


WHERE SSN_index = dbo.MAC( N'555-55-5555', object_id('t_CustomerRaw') )


go


SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'


go


DELETE v_CustomerDataEx WHERE SSN = N'555-55-5555'


go


SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'


go


CLOSE SYMMETRIC KEY key_Encryption


go


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


-- Now let's create a function to abstract the


-- MAC-index generation:


CREATE FUNCTION GetCustomerRawIndex( @SSN nvarchar(20) )


RETURNS varbinary(24)


AS


BEGIN


RETURN dbo.MAC( @SSN, object_id('t_CustomerRaw') )


END


go



-- Let's modify the function that is not affecting


-- our application


CREATE FUNCTION dbo.isCustomerActiveEx( @SSN_index varbinary(24) )


RETURNS int


AS


BEGIN


DECLARE @RetVal int


DECLARE @Status int


SET @RetVal = 0


SELECT @Status = Status FROM v_CustomerDataEx WHERE SSN_index = @SSN_index


IF( @Status is not null AND @Status > 0 )


SET @RetVal = 1


return @RetVal


END


go



--- Try our application again


-- Notice the much improved efficiency of the


-- new function


SELECT * FROM v_CustomerDataEx WHERE dbo.isCustomerActiveEx( SSN_index ) = 0


go


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


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


Version history
Last update:
‎Mar 23 2019 04:20 AM
Updated by: