SqlCredit – Part 10: MAC Performance and Updating SqlCredit

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:

1.  Insert
n records into each table.

2.  Read
n random records by ID.

3.  Read
n random records by CardNumber1/SecurityCode1 (this reads the same
record as step 2).

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

SQL Server 2005 SP2

Windows XP Professional

System 2

CPU: Single P4, 3.0 GHz HT

Memory: 1.5 GB

Hard Drives: Two physical drives (SQL, OS, and logs on C:, data on D:)

The Results

System 2 Processor Graph:

System 2 Statistics:

Action

Encryption

ms Duration

Cost vs.
Unencrypted

Create

None

12,950

ByKey/MAC

28,516

2.20 : 1

Read

None

3,936

ByKey/MAC

17,406

4.42 : 1

Total

None

16,886

ByKey/MAC

45,922

2.72 : 1

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

Rob Garrison
Rob Garrison
Rob Garrison is a database architect at Fiserv in Portland, Oregon specializing in OLTP design and development. He has over twenty years of IT experience, with over ten years in database development and architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles