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