Column Encryption for SQL Server and MSDE

Overview Technical FAQ APIs

• Why would I want to use column encryption APIs?
• How do I use the APIs in SQL?
• I want to distribute my application with or without column encryption.
• Can I use the APIs to protect columns from the SA?
• Where can I see a list of the APIs?

Why would I want to use column encryption APIs?

The main purpose of the APIs is to protect columns, fields, or rows from certain users who are otherwise authorized to access a table. For example, while you may want everyone to be able to access the customer table, you may want only certain users or roles to access the credit card column. You can accomplish this with the column encryption APIs.

While there are many ways of implementing this, one way is to create a view, function, or procedure which retrieves decrypted column data. Then you only grant rights to the procedure or view to certain users/roles.

There are also APIs for encrypting and decrypting files. There are many possible uses for these, but one example is secure log shipping. If you are using xp_sendmail to ship a log, you could encrypt the log file before sending it. And since Encryptionizer is so scalable, the receiver would not necessarily need Encryptionizer for SQL Server. They could have Encryptionizer for SQL Express or Encryptionizer DE instead.

How do I use the APIs in SQL?

In SQL Server 2000, 2005, or 2008, column encryption APIs are implemented as User Defined Functions, or UDFs. They can be used in: sql commands (Select, Update, etc), stored procedures, user defined functions, views, triggers, jobs, etc.

For example, the following is a UDF that retrieves the unencrypted credit card number column.

Create Function fnGetCreditCardData

  (@nCustID Integer, @nKeyHandle Integer)

  Returns Varchar(16)

As Begin

  Declare @cc As Varchar(16)

  select @cc = ( Select fn_n_decrypt(CreditCard,0,@nKeyhandle)

    from Customer where CustId = @nId )

  Return @cc

End

or even just in-line as

select @cc = ( Select fn_n_decrypt(CreditCard,0,@nKeyhandle)

from Customer where CustId = @nId )

You can make it even harder for unauthorized users/roles to access the decrypted data by selectively granting execute permission to fnGetCreditCardData and/or fn_n_decrypt.

I want to distribute my application with or without column encryption.

You can avoid maintaining two different versions of your source code. Besides the APIs, we also include "stub APIs" which you would distribute with releases that don't include column encryption.

Can I use the APIs to protect columns from the SA?

Yes! Implemented properly, APIs can be used to protect certain columns even from an SA or other system administrator. The evaluation version includes a sample VB.DBO application that shows one way of doing it.

Where can I see a list of the APIs?

Click here for a list of all the Encryptionizer APIs.