We are storing BLOB data (call recordings) in commodity cloud storage. These are encrypted and the keys stored in an SQL Server database.

I am working on a project to implement "Retention", i.e. deletion of data after an expiry period has elapsed. The plan is not to bother deleting the Blobs themselves, as the storage is cheap, but to remove the encryption keys from the database thereby making the call irretrievable and therefore logically deleted.

I need to prove (for compliance), that after the encryption key columns for logically deleted rows are NULLed (or updated to a new value or otherwise), the encryption keys would not be retrievable from the data files themselves (e.g. by a hacker using a hex editor to access the pages themselves).

My question is, is the data going to be physically scrubbed from the data pages after NULLing the columns, or do I need to update them (e.g. to a dummy value) first then NULL them? Will the UPDATE operation physically change the bits or will it simply be a metadata operation that leaves the bits on the disk untouched? I have done some reading and investigation but I'm still not sure. Table definition is below. Encryption Key columns are called EncryptionKey and EncryptionIV.

CREATE TABLE [File].[FileDetail]
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [AccountId] [bigint] NOT NULL,
    [TransferredDateTime] [datetime] NOT NULL,
    [IsEncrypted] [bit] NOT NULL,
    [EncyptionType] [nvarchar](50) NOT NULL,
    [EncryptionKey] [varbinary](2048) NULL,
    [EncryptionIV] [varbinary](2048) NULL,
    [OriginalPath] [nvarchar](400) NOT NULL,
    [SourceMachine] [nvarchar](4000) NOT NULL,
    [RelativeLocation] [nvarchar](400) NOT NULL,
    [FileCreationTime] [datetime] NOT NULL,
    [DurationMs] [int] NOT NULL,
    [EncryptionDurationMs] [int] NOT NULL,
    [CopiedSuccessfully] [bit] NOT NULL,
    [UserId] [bigint] NULL,
    [CustomerId] [bigint] NULL,
    [NumberCalled] [nvarchar](50) NULL,
    [TelephonyCallId] [uniqueidentifier] NULL,
    [SolutionInstanceId] [bigint] NULL,
    [Filename] [nvarchar](240) NULL,
    [NonCompliantFilename] [nvarchar](240) NULL,
    [StorageLocationType] [int] NOT NULL,
    [DeletedStatus] [int] NOT NULL,
    [DeletedStatusTimestamp] [datetime] NULL,
    [FileSize] [int] NULL
  • You'll need to encrypt the database files to protect the contents (e.g. TDE). Artifacts of deleted data may remain in data pages and log until physically overwritten.
    – Dan Guzman
    Jun 29 '16 at 11:44
  • Thanks, Dan. No TDE for us though as we are on Std Edition. Hurrah! Jun 29 '16 at 12:53
  • Have you considered BitLocker (dba.stackexchange.com/questions/54600/…)?
    – Dan Guzman
    Jun 29 '16 at 13:09
  • No, hadn't considered that, but will, thank you very much. Jun 29 '16 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.