Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Encrypting Data Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2008 9:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
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
Post #500149
Posted Wednesday, May 14, 2008 4:05 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:03 PM
Points: 62, Visits: 379
... 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.
Post #500280
Posted Wednesday, May 14, 2008 7:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
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)?
Post #500465
Posted Wednesday, May 14, 2008 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 5,589, Visits: 24,969
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
Post #500481
Posted Wednesday, May 14, 2008 8:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 2,872, Visits: 719
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.
Post #500537
Posted Wednesday, May 14, 2008 8:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
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.
Post #500571
Posted Wednesday, May 14, 2008 9:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 2,297, Visits: 1,355
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!
Post #500595
Posted Wednesday, May 14, 2008 9:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
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.
Post #500619
Posted Wednesday, May 14, 2008 9:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
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
Post #500646
Posted Wednesday, May 14, 2008 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 1:09 PM
Points: 21, Visits: 209
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.
Post #500718
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse