Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encrypting Data


Encrypting Data

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41024 Visits: 18868
Comments posted to this topic are about the item Encrypting Data

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
John Magnabosco
John Magnabosco
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 385
... adding to that you could even have unique encryption keys per column. Ah, but the headache that this causes is much better than the one educed by the disclosure of confidential data to the Snidley Whiplash-es of the world.
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1397 Visits: 852
Another thought would be doing a cost-benefit analysis of taking the "risk" to be encrypted or not. Of course, the risk if you are not encrypted is that the data falls into the wrong hands. The risk, on the other side as was mentioned in the article, is losing the certificate. Which risk would cost the company more? Helping customers when your database is lost or replacing the lost data? Is doing a cost-benefit analysis reasonable or is the generally accepted approach to encrypt regardless of the risk? It also seems that the cost of the certificates should be included in that cost-benefit analysis. How about the cost of performance (or is this a non-issue)?
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6237 Visits: 25280
Now Steve what did you mean to present to us with this editorial? (Pick one or more from the following)

1. A riddle
2. Insoluble problem
3. Inexplicable situation

Wikipedia:
Conundrum, a puzzle or a riddle designed to test for lateral thinking

Dictionary.com
A paradoxical, insoluble, or difficult problem; a dilemma

Thesaurus.com
Anything that arouses curiosity or perplexes because it is unexplained, inexplicable, or secret.
Synonyms: enigma, perplexity, puzzle, puzzler, riddle

For the potential problems you foresee the answer could be all of the above. Let us hope that the powers that be, who know little or nothing about preserving data, are not the ones that dictate by law what we must or must not do to preserve privacy/secrecy. Just charge us with the responsibility but not the how to of it.

Speaking as an American, request that members from other countries expand on their countires laws, regulations regarding privacy and secrecy and the difficulties they face and which they may have overcome.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3734 Visits: 990
I see lots of problems, but does anyone have a reasonable solution? If our system crashed I am afraid of trying to restore the keys as we have no DR plan and no resources to put a plan together.
Wayne West
Wayne West
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 3694
Encrypted backups = good.
Encrypted data on disk = ???

I can see some scenarios to maintain keys/certificates, along the lines of giving critical personnel (I was going to say key personnel, but didn't want to run the risk of a pun) portable HDDs or perhaps a couple of email accounts that you could email the key/certificates after they are compressed & encrypted with PKZIP. Either would be an easy repository, and if that account is compromised or that HDD is stolen, it wouldn't be too difficult to revoke/change the keys and reinitialize your off-network DR key scheme.

Maybe make the password to the zip a two-part password where C-Level #1 enters the first ten characters, C-Level #2 enters the second ten. They keep their parts in a sealed envelope in a personal safe. The C-Levels don't have physical (or electronic) access to the key repository, the ones with the access don't have the key to unlock the repository.

You're always going to have trust issues as long as you have humans, it's unavoidable. And regardless of our thinking of ourselves as trustworthy, which no doubt many of us are, we only have to look at Certegy as proof that there will always be exceptions.

Of course you should change the keys to the system, perhaps as often as you change your system passwords, probably at least annually. But you absolutely must maintain those old keys in case you're under a court order to produce data from last year's backup.


If you want to have some real fun, check out this Microsoft paper on "Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005" at http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx or this paper on encryption vs hashing at http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1285699,00.html.


As for my employer? No plans for disk encryption, we're looking at encrypting all of our LAN traffic, though.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Miles Neale
Miles Neale
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 1694
Steve,

Encryption is an interesting beast and should be embraced. Management of keys is a bit tricky which you stated well. And we all have a number of thoughtful opinions about why we should or should not use this type of encryption or none at all.

However all the arguments fade quickly when unauthorized fellows take or corrupt your data. If you have not been hit you have opinions, if you have been hit you have encryption and more.

Have a great day!

Not all gray hairs are Dinosaurs!
Wayne West
Wayne West
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 3694
I think theft of backups or servers is the bigger risk (Peter Gabriel's web server was stolen from his ISP recently, who knows what was on it and lost), corruption is an access issue more at the permissions/network level. If they can access the network through a compromised workstation, that station may contain the keys which would nullify encryption. If it doesn't contain keys, then your user is entering at least token information to open sessions and then your sorely inconveniencing the users.

Prevent unauthorized workstation access, prevent unauthorized data modification.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41024 Visits: 18868
I was presenting a puzzle/conundrum/whatever here. It's a problem I've seen since my use of PGP in the early 90's and one problem that so many people have faced. I don't have a good solution, but it's something that I think should be debated more and discussed.

A risk analysis makes sense, but how do you determine the risk of losing backup or mdf/ldf files? Apart from putting your thumb up in front of some scale and making a WAG. (wild guess)

It's unlikely that your files get stolen. However, if they do it's a problem. So how much effort is worth putting in? Hard to tell and if you're going to put in the effort with TDE or encrypted Litespeed/SQLBackup/SQLSafe backups, then how do you manage the keys/passwords? Not an easy thing to do.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Dan Smith-306792
Dan Smith-306792
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 210
Isn't part of the problem really a single point of failure? If the database certificate becomes corrupted or lost you are SOL.

Can the live database be encrypted using one certificate while backups of the database be encrypted using another certificate, or even another encryption technology (ex a PGP encrypted hard drive). That way if the database certificate is lost you have access to the backups using a different certificate and if the backup encryption key/certificate is lost you still have the live database.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search