SQL Server 2008 Encryption - question...

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

, +

Hi
We have implemented a web site with a SQL server backend database. We have used symmetric column encryption in the database. The stored procedures open and close the key prior to any read or write.
All this works very well indeed, and we can see that all the data is beautifully encrypted.

I have a general question about the encryption in general really.
If someone gains access to the server via logging in to windows (needless to say we have everything locked down and firewalled up!) they can then open SQL Server Studio and simply issue queries based on our stored procedures and - voila - they have the un-encrypted data.

Is there an even better way of doing column level encryption or a more standard way to avoid the scenario described? So that the key is not stored within the DB itself? Somehow passing the key in from a string encrypted obfuscated asp.net .dll perhaps.

Is there a standard that I haven't discovered yet? I didn't want to go down a certain route to discover there's an easier way, or the new way has a hole in it...

Many thanks for your time

Julian

Posted 6-Jan-12 2:49am

julian@giant

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shameel · Answer 1 · 2012-02-26T23:25:00

The hard reality is that the more secure you make your database, the less performant it becomes. There's a certain level of security required in database and I think you have it. If the idea is to protect data if someone can get hold of your storage devices, then your solutions works perfectly well. If someone can somehow hack into your server and login to a SQL console, then they can 'see' your data, there's not much you can do about it. Even if you setup a scheme to access it via an encrypted interface, they can still delete the data or sabotage the database in other ways (since they're already in the console). I haven't heard of a scheme that is 100% safe, secure and reliable. Let me know if you come across one.