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:
- Create a table in a SQL Server 2005 database to store the encrypted documents.
- 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.
- Create stored procedures that use the key to encrypt/decrypt documents that are stored in the table.
- Create a service account under which the service that stores the report will run.
- 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.
- 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.
- 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.
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:
- 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).
- 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.
- 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,
CONSTRAINT [PK_EncryptedReport] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
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.
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:
- Open the symmetric key.
- 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).
- Encrypt each block.
- Insert each encrypted block into the table.
Here's the definition of the stored procedure that does the trick:
CREATE PROCEDURE [dbo].[usp_StoreReportWithEncryption]
@ReportText 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
decryption 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:
- Open the symmetric key, then
- 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]
@OrderId int = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @numBlocks int
Declare @rowIdx int
Declare @text varchar(max)
Declare @block varbinary(8000)
Open Symmetric Key keyReportEncryption
decryption 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
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.