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

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

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