SqlCredit Part 8: Comparing Encrypt/DecryptByCert and Encrypt/DecryptByKey

August 24, 2007

Download the files for this article.

This is part 8 of a series. If you have not read part 7, you can find it here.

That’s Much Better

In part 7, we found that the performance impact of using EncryptByCert and DecryptByCert was unacceptable for any high-volume, transactional data.

Enter EncryptByKey and DecryptByKey. The way you use these functions is very similar to EncryptByCert and DecryptByCert. To see an example of a simple use of EncryptByKey and DecryptByKey, see this script. Most of the code is setup, but the fundamental calls are here (using simplified variable names):

 SELECT @v = EncryptByKey(Key_GUID(N'TestKey1'), @s1) 
 SELECT @s2 = DecryptByKey(@v) 

The performance results are highlighted later in this article.

Varbinary Length Requirements

How do you know how much space is required to store various types of encrypted data? I wrote a simple script that tests the results for a number of different data types. The table below shows the results.

In Part 6 of this series, I showed that EncryptByCert stores a maximum of 117 ASCII characters or 58 UNICODE characters. In the case of EncryptByKey, the maximums are 7,493 ASCII and 3,971 UNICODE. These are much better, but you still need to be aware of the limitations.

The Indexing Issue

To be able to create an index on a varbinary column, it cannot be more than 900 bytes wide. But wait … if EncryptByKey creates a different ciphertext each time it is called for the same string, how will I look things up, and how will my index be of any value? The answer to the first question is that we will use message authentication codes, which I will cover in a later article. The second answer is that an index on the varbinary column that stores the result of EncryptByKey would be worthless. So ignore the 900-byte limit on varbinary, you won’t build an index on this column anyway.

Performance Testing

What I did was build a test similar to the test in part 7 that gives an indication of the performance hit when using EncryptByKey and DecryptByKey and also compares that performance to EncyptByCert and DecryptByCert. Once again, all of the code is included, so please do download the code and try it out yourself. I did have access this time to a dual-proc system, so I have results from that to add to the mix. If you run these tests, please share your results in the forum.

The test results here are from three different desktop-class systems. The first is a “sandbox” I set up to try SQL Server 2005 before it was released; the second is my primary development system; the third is a dual-proc development system loaned to me by our architect.

Finally, a Compelling Reason to Use UDTs

I have never built a UDT in SQL Server. I have never found a good reason to build one. Why create a UDT called CardID where it just maps to an integer? That would be complexity without any payback.

When dealing with encrypted values and Ecrypt/DecryptByKey, UDTs become very valuable. For instance, what is the size of varbinary required to store an encrypted smallint? It’s important that I get it right, but it’s not important that I remember (or give myself the chance of getting it wrong). For these tests, I created a UDT called EncryptedTinyint that maps to a varbinary(52). Now that adds value.

The Tests

The tests are fairly simple. They create three tables, one with no encryption (CardNoEncrypt), one with ByCert encryption (CardEncryptByCert), and one with ByKey encryption (CardEncryptByKey).

CREATE TABLE dbo.CardNoEncrypt (
    CardID         int             NOT NULL  IDENTITY,
    CardNumber1    bigint          NOT NULL,
    CardNumber2    bigint          NOT NULL,
    CardNumber3    bigint          NOT NULL,
    CardNumber4    bigint          NOT NULL,
    SecurityCode1  smallint        NOT NULL,
    SecurityCode2  smallint        NOT NULL,
    SecurityCode3  smallint        NOT NULL,
    SecurityCode4  smallint        NOT NULL,
    SecureString1  varchar(36)     NOT NULL,
    SecureString2  varchar(36)     NOT NULL,
    SecureString3  varchar(36)     NOT NULL,
    SecureString4  varchar(36)     NOT NULL,

    CONSTRAINT CardNoEncrypt_PK
    PRIMARY KEY CLUSTERED (CardID)
) ON [SqlCredit_FG1];

CREATE TABLE dbo.CardEncryptByCert (
    CardID         int             NOT NULL  IDENTITY,
    CardNumber1    EncryptedByCert NOT NULL,
    CardNumber2    bigint          NOT NULL,
    CardNumber3    bigint          NOT NULL,
    CardNumber4    bigint          NOT NULL,
    SecurityCode1  EncryptedByCert NOT NULL,
    SecurityCode2  smallint        NOT NULL,
    SecurityCode3  smallint        NOT NULL,
    SecurityCode4  smallint        NOT NULL,
    SecureString1  EncryptedByCert NOT NULL,
    SecureString2  varchar(36)     NOT NULL,
    SecureString3  varchar(36)     NOT NULL,
    SecureString4  varchar(36)     NOT NULL,

    CONSTRAINT CardEncrypt_PK
    PRIMARY KEY CLUSTERED (CardID)
) ON [SqlCredit_FG2];

CREATE TABLE dbo.CardEncryptByKey (
    CardID         int                   NOT NULL  IDENTITY,
    CardNumber1    EncryptedBigint       NOT NULL,
    CardNumber2    bigint                NOT NULL,
    CardNumber3    bigint                NOT NULL,
    CardNumber4    bigint                NOT NULL,
    SecurityCode1  EncryptedSmallint     NOT NULL,
    SecurityCode2  smallint              NOT NULL,
    SecurityCode3  smallint              NOT NULL,
    SecurityCode4  smallint              NOT NULL,
    SecureString1  EncryptedSecureString NOT NULL,
    SecureString2  varchar(36)           NOT NULL,
    SecureString3  varchar(36)           NOT NULL,
    SecureString4  varchar(36)           NOT NULL,

    CONSTRAINT CardEncrypt_PK
    PRIMARY KEY CLUSTERED (CardID)
) ON [SqlCredit_FG3];

For this test, there are no indexes other than the clustered primary keys.

Again, we encrypt only three columns (a bigint, a smallint, and a varchar(36)).

Test Steps:

• Insert n records into each table.
• Read n random records by ID.

Each test is separated by a five-second delay to allow the system to settle between tests.

To run the tests, run the SQL scripts in this order:

• DropAndCreateDatabasePerfTest.sql
• CreateCertAndKey.sql
• EncryptByKeyPerfTest_Setup.sql
• EncryptByKeyPerfTest_Test.sql

The Test script has one important parameter that maps to the n, above: @createMax. The tests were run with it set to 10,000, but when you run it on your own system, set it low to start with.

The Systems

Common

System 1

System 2

System 3

The Results

For these tests, I used filegroups split between the two physical drives.

Certainly, encryption and decryption by key are much better than by cert. I am very pleased with these ByKey results because I think the cost is low enough that performance becomes a non-issue.

Test Changes

• The EncryptByCert stored procedures now use functions to encrypt and decrypt rather than the view used in the previous test.
• The EncryptByCert stored procedures now call Cert_ID() once and pass that value around rather than calling CertID() inside of every encrypt and decrypt call.

For Next Time

I had planned to start right into message authentication codes this month, but the performance of various functions has captured my attention, so that will have to wait a bit.

References

• SQL Server 2005 Encryption – Encryption and data length limitations
• Cert_ID at msdn
• SQL Server 2005 Books Online (Note that Books Online’s current entry for Key_GUID states that it returns varbinary. It actually returns a uniqueidentifier.)

Download the files for this article.

» See All Articles by Columnist Rob Garrison

Discuss this article in the SqlCredit – Developing a Complete SQL Server OLTP Database Project Forum.