[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

SQL 2008 - Best Practices in column level encryption


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

1 Solution
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?

Alpesh PatelAssistant ConsultantCommented:
Yes, it degrade the performance of SQL Server and your application. Its good to have encryption on SQL Server level.
Anthony PerkinsCommented:
>>We are using sql 2008.<<
What edition are you using?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Anthony PerkinsCommented:
Than you may want to consider using TDE, as in:
Understanding Transparent Data Encryption (TDE)
kamurAuthor Commented:
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 under.net clr?? Just trying to understand....

Anthony PerkinsCommented:
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.
kamurAuthor Commented:
Thanks for the solution

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now