SqlCredit Part 6: Exploring EncryptByCert and DecryptByCert

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

There are many good articles available that cover data encryption and data security in general. I will not try to explain why you need to encrypt sensitive data within your database. If you don’t already have someone knocking on your door telling you to do this, listen carefully and you can hear them coming down the hall.

For years, many database designers have played the performance card and gotten away with storing plain-text data inside the database. They (we) have created elaborate security schemes around the database, but if someone could actually get access to the database itself, they were in.

SQL Server 2005 includes support for encrypting and decrypting data for storage using EncryptByCert. Let’s look at how to implement this in the SqlCredit database. We will use it to encrypt the credit card number and security code that were previously stored in plain text.

117 ASCII Characters? 58 UNICODE Characters?

Before we continue, we need to understand the limitations of EncryptByCert regarding string lengths. There is a great explanation here, which states the basic problem but does not address UNICODE. The critical quote:

“The length of plaintext data that can be encrypted … is the length of the key modulus minus eleven bytes.”

This means that for ASCII strings (char or varchar), the largest string that can be encrypted using EncryptByCert is 117 characters. For UNICODE strings (nchar or nvarchar), the largest string that can be encrypted using EncryptByCert is 58 characters. Attempting to encrypt longer strings returns NULL.

I built a simple script that illustrates this very clearly.

For the strings (actually bigints and smallints) we will encrypt in SqlCredit, this is not an issue, but it is very important to understand if you plan to use EncryptByCert in your application.

Why Not Use varbinary(MAX)?

Since EncryptByCert returns a “varbinary with a maximum size of 8,000 bytes”, the easiest thing to use for your datatype would be varbinary(MAX). The problem comes when you try to index that column. You will receive this error:

“Column 'CardNumber' in table 'dbo.Card' is of a type that is invalid for use as a key column in an index.”

Based on the results of EncryptByCertStringLength.sql, the proper size of the varbinary is 128. A column of that type works fine in an index.

Some Running Changes

I noticed that I had left the security code out of the Card table (as explained in Part 1 of the series), so I have added that. I also replaced the non-unique index on Card.CardNumber with a unique index on Card.CardNumber/Card.SecurityCode. These changes lay the ground for encrypting these entries. There were also small changes to the associated stored procedures and views.

New stored procedure: CardReadByCardNumberAndSecurityCode. Added to BuildSqlCredit.bat: CreateCert.sql.

Using EncryptByCert

Before using EncryptByCert, it is necessary to create a master encryption key and create a certificate. (See BackupKeys.sql for an example of backing up the “service master key” and the “database master key”.)

Once the keys are created, using EncryptByCert is fairly simple. Here is the Card table without encryption:

CREATE TABLE dbo.Card (
    CardID       int            NOT NULL  IDENTITY,
    CardNumber   bigint         NOT NULL,
    SecurityCode smallint       NOT NULL,
    ...

and with encryption

CREATE TABLE dbo.Card (
    CardID       int            NOT NULL IDENTITY,
    CardNumber   varbinary(128) NOT NULL,
    SecurityCode varbinary(128) NOT NULL,
    ...

The INSERT procedure (CardCreate) without encryption:

...
INSERT INTO dbo.Card (
    CardNumber,
    SecurityCode,
    ...
)
VALUES (
    @CardNumber,
    @SecurityCode,
    ...

and with encryption

...
INSERT INTO dbo.Card (
    CardNumber,
    SecurityCode,
    ...
)
VALUES (
    EncryptByCert (
        Cert_ID (N'SqlCreditCert'),
        CAST (@CardNumber AS varchar(20))
    ),
    EncryptByCert (
        Cert_ID (N'SqlCreditCert'),
        CAST (@SecurityCode AS varchar(6))
    ),
    ...

The SELECT procedure (CardReadByID) without encryption:

SELECT
    CardID,
    CardNumber,
    SecurityCode,
    ...

and with encryption

SELECT
    CardID,
    CAST (
        CAST (
            DecryptByCert (
                Cert_ID (N'SqlCreditCert'),
                CardNumber
            )
            AS varchar(20)
        )
        AS bigint
    ) AS CardNumber
    CAST (
        CAST (
            DecryptByCert (
                Cert_ID (N'SqlCreditCert'),
                SecurityCode
            )
            AS varchar(6)
        )
        AS smallint
    ) AS securityCode,
    ...

I cannot overstate the importance of the fact that the inputs to CardCreate and the outputs from CardReadBy<inputs> have not changed. The unit tests passed without any changes because all of the encryption/decryption is encapsulated inside of the stored procedures and views. The calling code does not have to change in any way to accommodate the encryption. This means that encrypting data using EncryptByCert is a database refactoring as defined by Scott Ambler because it improves the database design (better security) “while retaining both its behavioral and information semantics.”

(Notes: I actually modified the read procedures to use CardView, so the decryption code is in only one place, but the code above would work. Also, I was not able to make the decryption work without doing a double-cast. If you have a better way, please share.)

Loss of Datatype Enforcement

We love having very specific datatypes for all the data in our databases. You can’t store a string in a column where the datatype is int. However, encryption works on strings, so you convert your data from an int to a string and store it in a varbinary column.

I can create a table with a varbinary(4000) column and store encrypted strings, integers, dates, whatever. When I call DecryptByCert, I have to tell it what datatype to convert the unencrypted data into.

This simple script illustrates the problem.

The most important lesson from this is in line with general database design best-practices: Don’t use a column for more than one purpose. Don’t create an EncryptedStuff column where you can store either an integer or a date depending on specifics of the application.

For Next Time

Now that CardNumber and SecurityCode are encrypted, how will performance be affected?

Also, I really don’t like passing actual numbers around for StatusCd. I will change that to use UDFs to translate between numbers and their corresponding status strings.

References

SQL Server 2005 Security - Part 3 Encryption at Database Journal

Cryptographic Functions (Transact-SQL) at SQL Server 2005 Books Online

Refactoring Databases, by Scott W. Ambler and Pramod J. Sadalage

Downloads

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

» See All Articles by Columnist Rob Garrison