?
Solved

Row level Encryption for multi Tenant system

Posted on 2012-08-21
5
Medium Priority
?
569 Views
Last Modified: 2012-09-09
We have been asked to implement row level encryption for a multi tenant system.
In other words,each client will have unique encryption algorithm.
Can I use TDE  feature on SQL 2008 to acheive this?If yes, then how.
Thanks
0
Comment
Question by:venk_r
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 38318973
TDE is whole database encryption.  So row level is not an option with TDE.
0
 
LVL 8

Author Comment

by:venk_r
ID: 38320065
So how will I be able to acheive this?Do I need to write native encryption?
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 38320157
You will have to use cell level encryption for the columns.  You could use encryption on the application side, but when you want row encryption that has to be different for different tenants, the options are a lot more difficult.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1500 total points
ID: 38322749
You definitely don't need to write any kind of encryption yourself to do this - there are plenty of good algorithms out there you could use, like AES. The majority are natively included in the .NET framework,  so you could easily integrate them with SQL Server using SQL-CLR.

Though in both cases you'd be doing it at the cell level, you have two choices about how could accomplish this:

1. You can write a .NET SQL-CLR component that does the encrypt/decrypt using something native to .NET, like AES256. You'd have to store the key for each user somewhere - possibly in the user table alongside their login information, or in another table that you tighten security down on. Then the stored proc you use to fetch the sensitive data could pass it through the CLR assembly to encrypt/decrypt it, so it's encypted while stored.

2. Alternatively, you coudl do this at the application layer - each user (or company, if they're grouped that way) would have an encryption key available to them that they use to store and fetch the sensitive data. The application would fetch the data in the encrypted format, and then decrypt and display it as its needed. The added advantage here is that you don't need to store the keys in the database itself - you can use certificates on the servers themselves.
0
 
LVL 8

Author Closing Comment

by:venk_r
ID: 38381576
thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question