Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 2007

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

By Rob Garrison

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.

Data Type

varbinary Length

bit

52

tinyint

52

smallint

52

integer

68

bigint

68

 

String Length

varbinary Length

ASCII

UNICODE

1

52

52

10

68

68

100

148

260

1,000

1,060

2,052

(max UNICODE) 3,971

4,020

7,988

(max ASCII) 7,943

7,988

n/a

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:

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

SQL Server 2005 SP2

Windows XP Professional

System 1

CPU: Single P4, 2.66 GHz

Memory: 2 GB

Hard Drives: One physical drive

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:)

System 3

CPU: Dual 3.4 GHz Xeon

Memory: 2 GB

Hard Drives: One physical drive

The Results

System 1 Processor Graph:

System 1 Statistics:

Action

Encryption

ms Duration

Cost vs. Unencrypted

Create

None

11,233

-

 

Cert

15,030

1.33 : 1

 

Key

11,340

1.01 : 1

Read

None

423

-

 

Cert

362,486

856.94 : 1

 

Key

2,720

6.43 : 1

Total

None

11,656

-

 

Cert

377,516

32.39 : 1

 

Key

14,060

1.21 : 1

System 2 Processor Graph:

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

System 2 Statistics:

Action

Encryption

ms Duration

Cost vs. Unencrypted

Create

None

2,740

-

 

Cert

12,336

4.50 : 1

 

Key

5,753

2.10 : 1

Read

None

466

-

 

Cert

354,290

760.28 : 1

 

Key

2,440

4.23 : 1

Total

None

3,206

-

 

Cert

366,626

114.36 : 1

 

Key

8,193

2.56 : 1

System 3 Processor Graph:

System 3 Statistics:

Action

Encryption

ms Duration

Cost vs. Unencrypted

Create

None

3,826

-

 

Cert

12,050

3.15 : 1

 

Key

7,063

1.85 : 1

Read

None

343

-

 

Cert

293,016

854.27 : 1

 

Key

2,313

6.74 : 1

Total

None

4,169

-

 

Cert

305,066

73.17 : 1

 

Key

9,376

2.25 : 1

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

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.

SqlCredit - Developing a Complete SQL Server OLTP Database Project



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date