SQL Server 2005 –Encrypting data on existing applications

[アーティクル]
05/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.

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

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

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

-- Create a test DB for our fictional application

CREATE DATABASE db_Demo

USE db_Demo

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

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

-- The following modules will be our fictional

-- applciations

-- Prints the total number of active customers

CREATE PROC app_RunReport

declare @Count int

SELECT @Count = count(*) FROM t_Customer

WHERE Status > 0

PRINT 'Total active customers: '

+ convert(varchar, @Count)

-- Updates the

CREATE PROC app_ChangeData(

@SSN nvarchar(20),

@name nvarchar(200),

@Status int,

@ExtraData nvarchar(100) )

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'

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

-- and is active, 0 otherwise

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

RETURNS int

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

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

CREATE VIEW v_CustomerData

SELECT Name, ExtraData as Data FROM t_Customer

WHERE Status <> 0

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

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

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Yukon900'

-- This certificate will be used to protect the

-- data-encryption key

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

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

-- This is the certificate that will protect our

-- MAC key-encryption key

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

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

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

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

RETURNS varbinary(24)

WITH EXECUTE AS 'dbo'

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

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

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 )

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

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

--- Create a new MAC key for this table

declare @objid int

SET @objid = object_id('t_CustomerRaw')

EXEC AddMacForTable @objid

-- Intercept the inserts and make sure the inserted

-- data is properly generated

CREATE TRIGGER trig_ProtectSSN on t_CustomerRaw

INSTEAD OF INSERT

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

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

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

-- Open the symmetric key before we can use it

OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys

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

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

CLOSE SYMMETRIC KEY key_Encryption

-- Once we are sure nothing is missing,

-- we will drop the original table...

DROP TABLE t_Customer

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

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

-- Intercept the inserts and make sure the inserted

-- data is properly generated

CREATE TRIGGER trig_ProtectView on t_Customer

INSTEAD OF INSERT

SET NOCOUNT ON

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

-- Intercept the inserts and make sure the inserted

-- data is properly generated

CREATE TRIGGER trig_ProtectViewUp on t_Customer

INSTEAD OF UPDATE

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

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

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

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

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

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

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

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

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

-- Intercept the inserts and make sure the

-- inserted data is properly generated

CREATE TRIGGER trig_CustomerDataEx_ins on v_CustomerDataEx

INSTEAD OF INSERT

SET NOCOUNT ON

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

-- Intercept the inserts and make sure the inserted

-- data is properly generated

CREATE TRIGGER trig_CustomerDataEx_upg on v_CustomerDataEx

INSTEAD OF UPDATE

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

-- Testing the new view for inserts

-- Remember to open the symmetric key

OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys

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

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

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

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

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

CLOSE SYMMETRIC KEY key_Encryption

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

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

-- MAC-index generation:

CREATE FUNCTION GetCustomerRawIndex( @SSN nvarchar(20) )

RETURNS varbinary(24)

BEGIN

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

END

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

-- our application

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

RETURNS int

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

--- Try our application again

-- Notice the much improved efficiency of the

-- new function

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

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

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

Comments

Anonymous
May 16, 2006
The comment has been removed
Anonymous
May 16, 2006
I want to apologize to exBK, I saw a duplicated entry for his comment, and apparently I made a mistake when trying to delete the duplicated entry. I am a neophyte blogger, and this is the first time I tried to manage comments.

Here is a copy of the original comment by exBK:

“The details given in this posting are really great!!!. I have a question for you: When we convert an existing app to start encryption and decrypting data as needed, I am required to make changes to atleast 50 stored procs. Within each of these SPs, I am using OPEN SYMMETRIC KEY ... and CLOSE SYMMETRIC KEY calls. Is it possible to not use these in every SP's that calls for an encryption or decryption? Any tips are greatly appreciated.”

I am truly sorry for this mistake, I will try to be more careful in the future.

Thanks a lot,
-Raul
Anonymous
January 31, 2008
Hi I have a server on which i encrypt my database using sql 2005 mechanism now i take back up of smk from this server . Now this server has crashed and i have a second machine with fresh installaed sql 2005 on it . Now i restore database on it and when i restore my SMK on this new machine it is not decrypting coreectly . Kindly help
Anonymous
February 07, 2008
Laurentiu wrote an article that describes how to restore a DB that uses encryption. Hopefully the information in this article will be able to clarify your problem, but if you still have any additional questions or feedback, please let us know. Thanks a lot, -Raul Garcia SDT/T SQL Server Engine
Anonymous
November 17, 2011
The comment has been removed
Anonymous
December 05, 2011
The method I described here pretty much was designed as a workaround for scenarios that needed to be encrypted at rest, but the application was not prepared for it. 200K records is not that much, so I am a bit surprised; try using DecryptByAutoCert directly on the base table (i.e. SELECT *, DecryptbyAutoCert( encrypted_column) … ) and see if there is any difference. BTW. This article was written before the introduction of SQL Server 2008 Transparent Data Encryption (msdn.microsoft.com/.../bb934049.aspx). If you have the possibility, I would strongly recommend using TDE instead. The performance penalty will be significantly reduced.

次の方法で共有

SQL Server 2005 –Encrypting data on existing applications

Comments

その他のリソース