SQL Server 2005 –Encrypting data on existing applications


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


——————————————————-


—————————————————–


 

Comments (6)

  1.  I am glad to hear this information is being useful.

     In case of decrypting data, you can use DecryptByKeyAutoCert (http://msdn2.microsoft.com/en-us/library/ms182559.aspx) instead of the regular OpenKey/DecryptByKey/CloseKey, this will hopefully resolve the problem for the modules where you only need to decrypt. For the ones where you need to encrypt, the key needs to be explicitly opened before calling EncryptByKey (we don’t have an automatic cert/asymmetric key decryption builtin for this case).

    As an alternative, if you have enough control over the session (i.e. you application will not allow arbitrary T-SQL statements), or the users who have access to your SPs are really trustworthy (your main concern is to protect the data in disk, not from your application users), you can call OpenKey once and keep the key in memory for the rest of the session (or until there will be no further encryption calls).

     You don’t need to give full access to the certificate protecting the symmetric key to your app users, you can create a signed/Execute-as stored procedure to open the key and keep it open for the rest of the session; but be aware that if you leave the key opened for the session a user with access to this Open-Key stored procedure and the ability to execute arbitrary SQL statements in the same session can potentially decrypt all the sensitive data you are trying to protect. As I mentioned before, depending on your particular scenario, this risk may be acceptable.

     If you decide to keep the symmetric opened for the session, you can minimize the potential for abusing this privilege by removing direct access to the table storing the sensitive data to the application users. You can use signed modules to grant access to this table, enforcing access to it only via your application (or by a DB administrator).

     BTW. Let me know if you have any idea/suggestion for new demos. I have a couple of demos in mind for the near future, but I will be more interested in writing demos that our customers ask. I cannot promise to fulfill all requests, but I will do my best to write the most relevant or asked-for demos.

     Thanks a lot for your comments. I hope this information will be useful.

    -Raul

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

  3. Mayur22 says:

    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

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

  5. abhijit says:

    i have encrypted my database with the help of your method. but i have a problem in 1 table it contains 27 columns and above 200000 records so when i execute a simple query   "select * from table name "

    the execution time is around 2 min where as previously on the master table to was taking 0.05 sec

    can you help me

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