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 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 );
An important difference for this test is the addition of indexes to support efficient lookups.
Why is there no MAC for CardEncryptByKey.SecurityCode1? First, we wont use the MAC columns to guarantee uniqueness, so there is no value there. Second, we wont 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.
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:
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 dont have access to those other systems.
SQL Server 2005 SP2
CPU: Single P4, 3.0 GHz HT
System 2 Processor Graph:
System 2 Statistics:
We have put many of SQL Server 2005s 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 weve learned?
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, lets 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.
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.
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 Im 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.SqlCredit - Developing a Complete SQL Server OLTP Database Project
Performance Testing SQL 2008's Transparent Data Encryption
SQL Server 2008's Change Data Capture - Tracking the Moving Parts
Performance Testing - SQL Server 2008 versus SQL Server 2005
Exploring SQL Server's Index INCLUDEs
Exploring SQL 2005's Ranking Functions - NTILE() and ROW_NUMBER()
SqlCredit - Part 19: Exploring SQL 2005's Ranking Functions - RANK() and DENSE_RANK()
SqlCredit, Part 18: Exploring the Performance of SQL 2005's OUTPUT Clause
SqlCredit - Part 17: Exploring SQL 2005's OUTPUT Clause
SqlCredit - Part 16: The Cost of Bloat
SqlCredit - Part 15: The Cost of Distribution
SqlCredit - Part 14: The Cost of Translation
SqlCredit - Part 13: More on Indexed Persisted Computed Columns
SqlCredit - Part 12: Exploring Indexed Persisted Computed Columns
SqlCredit - Part 11: Change Tracking Using History Records
SqlCredit - Part 10: MAC Performance and Updating SqlCredit
SqlCredit - Part 9: Message Authentication Codes
SqlCredit Part 8: Comparing Encrypt/DecryptByCert and Encrypt/DecryptByKey
SqlCredit Part 7: Performance Impact of EncryptByCert and DecryptByCert
SqlCredit Part 6: Exploring EncryptByCert and DecryptByCert
SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring
SqlCredit - Part 4: Schema and Procedure Security
SqlCredit - Part 3: Schema/Proc Updates and Automated Unit Testing
SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures
SqlCredit - Developing a Complete SQL Server OLTP Database Project