Row level Encryption for multi Tenant system

Posted on 2012-08-21
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.
Question by:venk_r
    LVL 24

    Expert Comment

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

    Author Comment

    So how will I be able to acheive this?Do I need to write native encryption?
    LVL 24

    Expert Comment

    by:DBAduck - Ben Miller
    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.
    LVL 28

    Accepted Solution

    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.
    LVL 8

    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now