SQL 2008 - Best Practices in column level encryption

Posted on 2011-05-02
Last Modified: 2013-11-05


We are using sql 2008. We have large amounts of data sources (mainframe files that we import as flat files using ssis) and many of the data sources has confidential information.
The confidential data is in many tables and they are all not related as each table is independent of any other table. We have encrypted the confidential data columns (for ex: ssn) in all these tables.
We have written a .net clr assembly to encrypt the individual columns and it works fine. But I read somewhere on the web that individual column level encryption is not recommnded since it can have a huge performance hit. And we are having a huge performance hit. Our data sources are  huge and the data is imported every day. The encrypt and then the data load takes a long time every day. (we have verified the .net clr is fine, its just the huge volume of data that is causing the performance issue. Without the encrypt, the data load takes an hour and with the encrypt it takes over 3 hours).
Also, this approach takes a hit on the subsquent processing logic as some of these sensitive data are key columns in the joins of many sql queries. (Of course we have a decrypt function also).

I would like get suggestions, recommendations or best practices used in the implementation of sql encryption in  real world scenario. Also, would like to hear how we can restrict the access to these for the sql developers involved in this team. (Just assume that we cannot mask the data as many of these practical business scenarios cannot be validated with dummy or data masked values. The developers needed one days worth of real world data to develop/produce the results needed for the business)

Please appreciate if responses included best practices/ implementations in  practical business situations

Question by:kamur
    LVL 9

    Expert Comment

    Its a bad practice to use a custom library when reading/writing to DB. You need to use Symmetic encryption which was built in with SQL Server. Any issues on that for you?
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Yes, it degrade the performance of SQL Server and your application. Its good to have encryption on SQL Server level.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>We are using sql 2008.<<
    What edition are you using?

    Author Comment

    Thanks for the comments so far..
    We are using sql enterprise edition x64 bit with quad core processors on a virtual windows server

    LVL 75

    Accepted Solution

    Than you may want to consider using TDE, as in:
    Understanding Transparent Data Encryption (TDE)

    Author Comment

    I am more inclined to go with TDE, will be doing some tests this week.

    radceaser, patelalpesh

    Why is it considered a bad practice to use a custom library when reading/writing to DB?? How is the performance degraded by using custom library? Aren't they still running clr?? Just trying to understand....

    LVL 75

    Expert Comment

    by:Anthony Perkins
    You have discovered the performance problem, so I will not harp on that, but let me point out one other significant difference that you may have overlooked between SQL Server encryption and any home-grown .NET (including CLR) encryption you may use:
    The encrypted result for the same value using SQL Server will be different.  In other words, supposing you have a SS# column encrypted using SQL Server, then if there are two identical SS#s in different rows, then the encrpted result will be different.

    Author Closing Comment

    Thanks for the solution

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 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

    11 Experts available now in Live!

    Get 1:1 Help Now