SqlCredit - Part 10: MAC Performance and Updating SqlCredit

October 26, 2007

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

Download the code for this article.

There are three main sections here: MAC performance results, a summary of encryption findings, and comments on updating the SqlCredit code based on those findings.

MAC Performance Results

Part 9 included information about message authentication codes but did not include the performance results. Those are here.

The Tests

Changes from Part 8’s tests revolve around the MAC changes described in Part 9 plus the addition of supporting indexes.

The tests again create two tables, one with no encryption (CardNoEncrypt) and one with ByKey encryption that now includes MAC codes for CardNumber1 and SecureString1 (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)
  );

  CREATE UNIQUE NONCLUSTERED INDEX <name> ON dbo.CardNoEncrypt (
      CardNumber1,
      SecurityCode1
  );

  CREATE NONCLUSTERED INDEX <name> ON dbo.CardNoEncrypt (
      SecureString1
  );

  CREATE TABLE dbo.CardEncryptByKey (
      CardID           int                   NOT NULL  IDENTITY,
      CardNumber1      EncryptedBigint       NOT NULL,
      CardNumber1MAC   HashResultSHA         NOT NULL, -- MAC
      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,
      SecureString1MAC HashResultSHA         NOT NULL, -- MAC
      SecureString2    varchar(36)           NOT NULL,
      SecureString3    varchar(36)           NOT NULL,
      SecureString4    varchar(36)           NOT NULL,

      CONSTRAINT CardEncryptByKey_PK
      PRIMARY KEY CLUSTERED (CardID)
  );

  CREATE NONCLUSTERED INDEX <name> ON dbo.CardEncryptByKey (
      CardNumber1MAC
  );

  CREATE NONCLUSTERED INDEX <name> ON dbo.CardEncryptByKey (
      SecureString1MAC
  );

Indexes

An important difference for this test is the addition of indexes to support efficient lookups.

CardNoEncrypt indexes:

• The clustered primary key on the IDENTITY column. (This matches the primary key on CardEncryptByKey.)
• A unique index on CardNumber1/SecurityCode1. This supports efficient lookups and also guarantees the uniqueness of the CardNumber/SecurityCode combination.
• A non-unique index on SecureString1 to support efficient lookups.

CardEncryptByKey indexes:

• The clustered primary key on the IDENTITY column. (This matches the primary key on CardNoEncrypt.)
• A non-unique index on CardNumber1MAC. This supports efficient lookups only. Because hashing has the potential for collisions, this index should not be unique.
• A non-unique index on SecureString1 to support efficient lookups.

Why is there no MAC for CardEncryptByKey.SecurityCode1? First, we won’t use the MAC columns to guarantee uniqueness, so there is no value there. Second, we won’t ever look up a card by SecurityCode1 without CardNumber1. For this particular application (credit card), there will never be a large number of cards that have the same number but different security codes.

Test Steps:

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

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

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

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

The Systems

I ran this test on just one system, the one designated “System 2” in previous tests. The first reason is that the results from previous tests show that there is not a significant difference (in these particular tests) between a single, hyper-threaded CPU and a dual-processor system that does not have hyper-threading (System 3 from the previous tests).

The more significant difference is between a single, non-HT CPU and either an HT or dual-proc."

The second reason is that there have been some recent changes in my job, so I don’t have access to those other systems.

Common

System 2

The Results

System 2 Processor Graph:

System 2 Statistics:

Encryption Summary

We have put many of SQL Server 2005’s encryption capabilities under the microscope: Encrypt/DecryptByCert (part 6 and 7), Encrypt/DecryptByKey (part 8), message authentication codes (MAC) and HashBytes (part 9 and this article). What are the primary bits we’ve learned?

• Don’t index encrypted values.
• Encrypt/DecryptByCert is definitely the wrong choice for transactional data. It can be used for small bits of data.
• Encrypt/DecryptByKey performs very well on transactional data. There is some cost in both CPU utilization and duration of the operation.
• Both EncryptByCert and EncryptByKey have string length limitations. See part 6 for EncryptByCert and part 8 for EncryptByKey.
• To support both unencryption and efficient lookups, combine a column that stores the data encrypted with a column that stores the data hashed.
• varbinary(MAX) is fine for a quick spike, but for real code, be thoughtful about what size varbinary you use.
• Encrypting data at the database layer can be done without affecting the calling code.
• Encrypt only the data that needs to be encrypted. Much of what is stored in transactional databases is not sensitive.

Do you have other thoughts on this? There is a forum thread to discuss encryption in the database at a high level. A specific question here is, should the data be passed plain-text to the database and encrypted as it is stored, or should it be passed to the database as an encrypted value?

Pulling All This into the SqlCredit Code

Now that we have a good understanding of the capabilities and nuances of encryption and hashing in SQL Server 2005, let’s use that knowledge to update the code for SqlCredit.

I spent a lot of time updating the code and writing tests that validate that it works correctly. Updating the existing tests was not too hard since most of the encryption complexities are hidden from the calling code. The changes required in the calling code are mostly due to my optimizations that require, for instance, that the calling code have the GUID of the key to pass in to CardCreate.

Running Changes

Saving the Salt Look-up

In the function HashBigintSHA in part 9, I had code that read the secret from the SecretSalt table each time the function was called. The reason was that I did not want to unencrypt that value in the calling code and pass it in as clear-text.

As I reviewed that code, I figured out that I could look up the value of the salt without unencrypting it and pass that value to the function. Then I could unencrypt the value inside the function without having to look it up.

This detracts slightly from the simplicity and “purity” of the function. I made a similar choice in EncryptBigint, where I pass in a KeyGUID. In both cases, I am trading simplicity for performance. As long as I’m not complicating things significantly, that is generally a trade I like to make.

This kind of “early” performance optimization really bothers some people. What do you think? Is it acceptable to do this kind of optimization early, before performance testing has shown where the optimization is required, or should you code everything as simply as possible and wait for testing to prove that you need to change certain things?

I updated CardCreate to take an @Salt parameter. To simplify debugging, I added a check at the beginning of the procedure that validates that both the @KeyGUID and @Salt parameters are not NULL.

For Next Time

Other than Purchase, every table in SqlCredit can be updated. Currently, we have no way to track changes. Next month, I will look at a couple of options for tracking changes and implement one of them.

Download the code for this article.

» See All Articles by Columnist Rob Garrison