Geeks With Blogs
Chris Falter .NET Design and Best Practices

When one of our departments decided to store sensitive reports, the architecture I first considered was storing them, unencrypted, in our imaging system.  The imaging system cannot manage encrypted documents, but its security capabilities allow it to to deny unauthorized users access to the reports.  This ability to restrict access seemed to meet our security requirements. 

Then I started asking questions about data backups.  Offsite backups can be a major security vulnerability, as we have recently learned.  The names, addresses, social insurance numbers, and (in many cases) bank account details of almost half of England ended up who-knows-where last year because database backups disappeared in the postal system.  And our company, like any responsible organization, backs up our data and stores it offsite.  So leaving the sensitive data unencrypted was not acceptable.

Fortunately, SQL Server 2005 comes with native encryption capabilities that are simple to use. In the absence of an affordable imaging system with built-in encryption capabilities, here's the approach I hammered out:

  1. Create a table in a SQL Server 2005 database to store the encrypted documents.
  2. Create a symmetric key in the same database.  To facilitate recovery from database failures, store the instructions for re-creating the key in a safe, remote location. 
  3. Create stored procedures that use the key to encrypt/decrypt documents that are stored in the table.
  4. Create a service account under which the service that stores the report will run.
  5. Create a (presumably internal) website for displaying the reports.
    • Use the same service account for the site's app pool identity.
    • Restrict access to authorized groups via Windows authentication.
    • Require secure http so that a network trace cannot intercept decrypted documents on their way to the browser.
  6. On SQL Server, deny permissions for the stored procedures and table to anyone other than the service account.  The security context of the backup job must also be granted the db_backupoperator role on the database, of course.
  7. Implement network encryption between SQL Server and any services that insert or select sensitive documents, using IPSec or SQL Server Network Encryption.

Below is a diagram of the system that allows authorized users to view sensitive documents.

Viewing Sensitive Documents

There is scant MSDN documentation for accomplishing the first 3 steps, but the rest are well understood in the world of Microsoft development.  The remainder of this article will therefore focus on creating and managing the tables, keys, and stored procedures.

Step #1: Create a Table to Store the Encrypted Documents

Several considerations drive the design of the table: 

  1. The column that stores a block of encrypted data must have the datatype of varbinary(8000) because the result of an encryption operation is always a varbinary(8000). 
  2. Unless you are dealing with very small documents, 8000 bytes will not hold an entire document.  Therefore a single document will require multiple records, which will be associated with one another by a common unique identifier.  A foreign key to the business entity associated with the document would be a good candidate.  In our system, the encrypted document is associated with an "Order," so the identifier is an OrderId.
  3. In order to thwart brute force decryption attacks, you should "salt" the encryption with a unique integer for each report.  Unless your sensitive documents are very large, the foreign key (OrderId) will serve the purpose nicely.

Here's the table definition that emerges from these considerations:

CREATE TABLE [dbo].[EncryptedReport](
   [OrderId] [int] NOT NULL,
   [BlockNum] [int] NOT NULL,
   [Block] [varbinary](8000) NULL,
   [OrderId] ASC,
   [BlockNum] ASC

Step #2: Create a Symmetric Key

To encrypt data in SQL Server 2005, you may use a certificate or a symmetric key.  A certificate provides somewhat stronger protection than a symmetric key, so it is the encryption method of choice for a small amount of data (such as a Social Security Number). However, its far greater demand for server resources makes it impractical for large amounts of text or other data.  Therefore, if you are encrypting a sensitive document, you should create a symmetric key and encrypt it with a certificate.  At run-time, SQL Server will use the certificate to decrypt the key, then use the key to encrypt or decrypt the document. 

Key Generation

Before SQL Server can encrypt or decrypt a sensitive document for you, it must traverse a chain of encrypted secrets:

  • A certificate available only to the SQL Server process identity encrypts SQL Server's Service Master Key.
  • The Service Master Key encrypts the Database Master Key.
  • The Database Master key encrypts a certificate ("certDocEncryption" in this example).
  • The certificate encrypts a symmetric key ("keyReportEncryption" in this example).
  • (Finally!) the symmetric key encrypts your sensitive documents.

SQL Server stores all of these secrets for you, except for the process identity's certificate (which is stored in the Windows certificate store).  Consequently, you do not need to worry about making a "secret" available to your run-time environment by, for example, storing it in a configuration file.  Database backups will not include the process identity's certificate (which the operating system maintains for you), so the only way the bad guy that steals your backup can decrypt your sensitive documents is by a brute force attack. 

This design does place certain responsibilities on your IT organization:

  • You must be very careful about using this design with a virtual server.  While creating an image of an entire virtual server  (Windows OS, SQL Server, and data) facilitates quick disaster recovery, it can also facilitate the theft of sensitive documents, since the virtual server image will have everything (from Windows certificate down to the symmetric key) that a hacker needs to read the data.
  • To recover data in the event of a server failure, you must be able to re-create the symmetric key.  While it is impossible to back up a symmetric key, you can always create an identical key on any instance of SQL Server 2005 by issuing the "CREATE SYMMETRIC KEY" statement with the same algorithm, key source, and identity value.  Consequently, you must store the statement used to create your symmetric key in a secure location, such as a safe deposit box at a local bank.  And although this statement is quite obvious, I'll say it anyway: don't store the statement with your data backups!

Step #3: Create Stored Procedures

Encrypting a sensitive document is a four-step process:

  1. Open the symmetric key.
  2. Break the document into blocks that, when encrypted, will consume less than 8000 bytes. When I applied Microsoft's published formula to my encryption key process, I concluded that 7876 bytes was the maximum size of unencrypted data that I could safely fit into a varbinary(8000).
  3. Encrypt each block.
  4. Insert each encrypted block into the table.

Here's the definition of the stored procedure that does the trick:

CREATE PROCEDURE [dbo].[usp_StoreReportWithEncryption]
varchar(max) = ''

    declare @idx int
    declare @textLength int
    declare @blockSize int
    declare @blockNum int
    declare @block varbinary(8000)
declare @keyGuid uniqueidentifier

    Open Symmetric Key keyReportEncryption
by certificate certDocEncryption

set @keyGuid = Key_GUID('keyReportEncryption')
set @blockSize = 7876
set @blockNum = 1
set @idx = 1
set @textLength = datalength(@ReportText)

        WHILE @idx < @textLength
            set @block = EncryptByKey(@keyGuid, SUBSTRING(@ReportText, @idx, @blockSize), 1, Convert(varbinary, @OrderId))
insert into dbo.EncryptedReport(OrderId, BlockNum, Block)
values(@OrderId, @blockNum, @block)
set @idx = @idx + @blockSize
set @blockNum = @blockNum + 1


To decrypt the document, you must:

  1. Open the symmetric key, then
  2. Reassemble the document by
    • Decrypting each block, and
    • Concatenating the results. 

Here's the definition of the stored procedure that will fetch the document associated with a particular OrderId:

CREATE PROCEDURE [dbo].[usp_FetchEncryptedReport]
int = 0
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

Declare @numBlocks int
    Declare @rowIdx int
    Declare @text varchar(max)
Declare @block varbinary(8000)

Open Symmetric Key keyReportEncryption
by certificate certDocEncryption

set @rowIdx = 1
set @text = ''
set @numBlocks = (select IsNull(max(blocknum), 0) from dbo.EncryptedReport where OrderId = @OrderId)

     WHILE @rowIdx <= @numBlocks
            set @block = (Select Block from dbo.EncryptedReport where OrderId = @OrderId and BlockNum = @rowIdx)
set @text = @text + Convert(varchar(max), DecryptByKey(@block, 1, Convert(varbinary,@OrderId)))
set @rowIdx = @rowIdx + 1
    Select @text


Finishing Your Security Infrastructure

There is no such thing as a perfectly secure architecture that provides a 100% guarantee against the theft or compromise of your sensitive documents.  An authorized user can give up his network credentials to a social engineering attack; an unpatched or badly configured database server might allow a hacker to gain administrator access to it; a trusted network administrator can steal data outright.   What SQL Server 2005 does very well is protecting data "at rest"--i.e., sensitive documents stored in your database.  By following the first 3 steps in this article, you should be able to get a solution up and running quickly.  Just remember: make sure you implement other forms of network security (including the last 4 steps mentioned in the introduction) to make your entire infrastructure for handling sensitive documents resilient against theft and hacking. 

If you have suggestions or questions, please leave a comment!  Thanks for reading; I trust you have learned something useful from this article.

Posted on Thursday, May 8, 2008 4:13 PM Database Considerations | Back to top

Comments on this post: How To: Encrypt and Manage Documents with SQL Server 2005

# re: How To: Encrypt and Manage Documents with SQL Server 2005
Requesting Gravatar...
Can we encrypt the integer value from symmetric key.
in above example use the integer value but not encrypt the integer value , so
please immediate guide me it is possible or not.
I am waiting your reply.
Asit sinha
Left by asit sinha on May 28, 2008 11:25 PM

# re: How To: Encrypt and Manage Documents with SQL Server 2005
Requesting Gravatar...
Asit -

I am not sure which integer value you are referring to. Assuming you are talking about the OrderId...possession of the OrderId will be of very little value to a hacker. The hacker needs to gain possession of either the symmetric key itself or the authority to execute usp_FetchEncryptedReport in order to decrypt the sensitive document (short of a brute force attack).

- Chris
Left by Chris Falter on May 29, 2008 8:39 AM

# re: How To: Encrypt and Manage Documents with SQL Server 2005
Requesting Gravatar...
Respect sir
Here i am explain actually what our requirements,

In my table have 3 fields.
1st Check Number (Integer not null)
2nd CheckDate (DateTime not null)
3rd CheckAmtInWords (Varchar (100) not null)

From above example we encrypt the all 3 columns, and store into temporary table.
can it be possible, we encrypt these data ?
if yes then how we decrypt the data ?

please sir immediate reply me, and please give me example.
Asit Sinha

Left by asit sinha on May 29, 2008 8:11 PM

# re: How To: Encrypt and Manage Documents with SQL Server 2005
Requesting Gravatar...
@Asit -

For data fields that are small (less than 120 bytes) you can just encrypt/decrypt using the certificate. Check the MSDN documentation for EncryptByCert and DecryptByCert for pointers.
Left by Chris Falter on Jun 06, 2008 3:18 PM

Your comment:
 (will show your gravatar)

Copyright © Chris Falter | Powered by: