jump to navigation

First post on WordPress – Database Encryption October 6, 2008

Posted by msrviking in Security.
trackback

Alright this is my first posting on wordpress.com and I am little excited. Okay, let me leave my feelings behind and come to the topic – Database Encryption.

One of my client had come up with a question on SQL Server 2005 Database Encryption. Although, I haven’t worked extensively (exhaustively!) in designing encryption policies for any databases I gathered my thoughts and had this for him. The context was set before I could get back and it was in the form of questions

When we have data is stored in columns of different data types with different length in an encrypted format (except the image values),

1) What will be the performance impact of encryption and decryption of these columns? Should all the columns be encrypted?
2) What methodology of encryption should be used?
3) Would performance be impacted having the service layer calling the stored procedure that would do encryption and decryption of the values?
4) What should be compromised for columns that have encryption enabled (Performance or Security)?
5) Are there any options or solutions to get Performance and Security together?
6) What are the guidelines to backup and restore the encrypted keys?

And before I could answer the questions, I wanted to make a note that some of the points in the below points are contentious and keeping requirements in mind we agreed upon, this is what I could think of.

–       What is Database Encryption and what does this mean to us?

Database encryption protects data from internal and external attacks. However, database encryption isn’t a one-size-fits-all solution and we need to evaluate what data needs protection, where the encryption should be performed (e.g., in the database, in the application where the data lies, above the database), and how much performance would be traded for increased security. Well, we may also have no choice in these factors if we must encrypt data to comply with regulatory standards.

–       What level of encryption should be implemented?

Having the option of only cell level encryption and with the hierarchical encryption, key management infrastructure for encrypting data we have 3 choices of implementation.

·         Certificates (quoted from BOL)

A public key certificate, usually just called a certificate, is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key.

·         Asymmetric keys (quoted from BOL)

An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but provide a higher level of security than symmetric encryption.

·         Symmetric keys (quoted from BOL)

A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.

With all these points in mind I moved onto next level of questions based on our discussion in the call. The context was when we have data is stored in columns of different data types with different length in an encrypted format (except the image values),

–       What will be the performance impact of encryption and decryption of these columns? Should all the columns be encrypted?

Due to encryption the value of the column attributes changes and therefore the data type of the column is changed to varbinary and then is recast back to the original value after decryption. The manual process means that none of the automatic techniques used by the SQL Server query optimizer will work and becomes cpu intensive operation. The performance impact could be felt significantly if the system has high cpu load and high i/o.

Now the larger the data type length of the encrypted column, the poorer the performance of the system during decryption of these column values. Here is a link (you probably would have had look at this article) which talks about how much data is stored in each data type defined.

With these points in mind we would need to identify columns that need to be encrypted based on type data (sensitivity) or based on data type defined for the column.

In short my answers to the above questions will be – Performance is certainly impacted based on type of encryption method used, data type and length defined of the columns, and unfortunately I couldn’t quantify how much performance will be impacted. The above mentioned link could give us first insight of the performance issues rising. And definitely all columns can’t be encrypted, instead columns based on sensitivity of information should be encrypted and if all the columns have to be encrypted we need to justify the data types and column length to obtain best performance or at least not increase the performance problems.

–       What methodology of encryption should be used?

Going by most obvious reasons with the points discussed above and results from the link, we should prefer using encryption and decryption using symmetric keys.

–       Would performance be impacted having the service layer calling the stored procedure that would do encryption and decryption of the values?

My answer would be performance impact could be higher or lower based on where we embed the logic of identifying the type of key. And if we have an uniform key to be used across all types of columns then we don’t have to be worried about the service layer performance overhead.

To get into more of this situation let’s say –  if we create multiple symmetric keys for different type of columns like Name (First, Last) we have symmetric key created FSymKey and columns like SSN, Health Insurance Number we have symmetric key created SSymKey each key having its own encrypting mechanism.  Now while decryption we would use

/*To decrypt data for name columns*/

OPEN SYMMETRIC KEY FSymKey…… or OPEN SYMMETRIC KEY SSymKey ……

If logic to decrypt data is not embedded in stored procedure against specific column type, instead the logic is embedded in service layer then the overhead of identifying which key to be used could be a performance (overhead) hurdle. But now we have a catch in here, for sake of performance gain from service layer if we put in the logic into stored procedure we may breach the security by exposing the key names and other details, but we have solution for all problems. We probably would encrypt the stored procedure to protect the symmetric key that is used against specific columns, and again that could turn out to be a overhead for development and maintenance of encrypted sp’s, encrypted keys, and so on. We probably should have all wise thoughts together before implementing any of the options to gain performance and /or security.

–       What should be compromised for columns that have encryption enabled (Performance or Security)? Are there any options or solutions to get Performance and Security together?

I have clubbed the questions 4 and 5 for ease of answering.

Well, the first question is a tricky question because we need to do a trade-off between performance and security. We will have to identify what columns could cause performance issues, what basis should we decide that columns would be performance overheads, would we need to encrypt the column that would cause performance issues, if yes what should be the data type and length defined of the column.

What if we are unable to compromise on the data type, security and length then what are the other alternatives for getting the performance. This would be answer to the second question by using MAC (Message Authentication Codes) and there are few articles article 1, article 2 and article 3 that give a insight on how to gain performance by using Message Authentication Codes and indexing encrypted data. We could also use SQL CLR procedures to gain better performance (although not tested one) for encryption and decryption for columns that will have large data types.

–       What are the guidelines to backup and restore the encrypted keys?

There is a good article on how should we backup and restore database that have objects with encrypted columns enabled. IMHO, we should stick the methodology defined in this article for efficient management of database that uses encryption.

I hope this post helps you all and do let me know your comments. I would like to Thank! Laurentiu, Raul and Rob Garrison for sharing there experienced thoughts on their blogs of which I could make best of benefit.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: