May 13, 2008 at 9:12 pm
Comments posted to this topic are about the item Encrypting Data
May 14, 2008 at 4:05 am
... 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.
May 14, 2008 at 7:33 am
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)?
May 14, 2008 at 7:43 am
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.
May 14, 2008 at 8:15 am
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.
May 14, 2008 at 8:42 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 14, 2008 at 9:07 am
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!
May 14, 2008 at 9:26 am
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.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 14, 2008 at 9:42 am
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.
May 14, 2008 at 10:36 am
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.
May 14, 2008 at 11:04 am
Steve Jones - Editor (5/14/2008)
...It's unlikely that your files get stolen. However, if they do it's a problem. So how much effort is worth putting in?...
If you look at how many laptops are stolen, or tapes lost when being shipped off-site, etc., I think theft/loss of media is a definite risk and said media should be encrypted. Just look at the number of identity theft alerts where the source is lost tapes. Granted, the number lost versus the number that successfully reach their destination on a daily basis is very small, perhaps even minuscule, but it's still a risk. And when you look at the hit your business would take if you can't say to the press "our data was encrypted with strong encryption, so the risk of identity theft is pretty much zero", then I think it's definitely worth encrypting before your tapes leave your site.
Not that I'm paranoid or anything. 😀
We currently do our backups across the wire on the WAN to a remote site a mile away, so we're not actually moving tapes. The risk of one disaster wiping out both sites is kind of small as we're not tectonically-active. A jumbo jet creaming downtown on just the right vector would do it, so would a nuke, I don't know what else might that wouldn't give us enough time to get our servers or backups out of the area. And regardless, our ERP system backs up to the vendor on the east coast every night, so our ERP system could be up as soon as we got two servers and a high-speed circuit or a courier run.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 14, 2008 at 11:09 am
Just curious - I was under the impression that encryption by certificate was really not recommended for large amounts of data, since it can only encrypt 117 bytes at a time. How does the bigger data size play into that? Do the repeated 117-byte chunks (or rather - the encrypted length of the 117 bytes) make it a lot easier to detect the patterns, and thus break the encryption?
If I'm going to go to the trouble of going after encryption, it might as well be something bullet-proof (to anyone). The only one strong enough to resist just about any attack I know of is PGP (in the >4kb key ranges). I'd be a lot less inclined to go there if the encryption mechanism is mediocre, since I'd be more likely to screw myself than to slow down a hacker after my data (who would have the tools needed to break this readily available).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 12:40 pm
if you change the key , does previously encrypted live data need to be re encrypted?
May 16, 2008 at 4:32 am
Whilst Steve is quite correct in saying that managing the encryption/decryption keys is the hard part, it doesn't need to be *that* hard.
PKI, Public Key Infrastructure, is designed specifically for this purpose. If you implement your own PKI, set up your own Certificate Authority and generate your own Root Certificate (eg. using Microsoft Certificate Services), you can publish certificates for a variety of purposes, manage their distribution, and also manage their revocation.
I'm not saying this is easy, but it does mean you don't have to manually backup certificates, etc.
I know that MSSQL 2005 can create self-signed certificates. Does anyone know if a certificate, signed by a separate authority, can be used instead? If yes, how does it link in with the SERVICE MASTER KEY and DATABASE MASTER KEY?
Matt Miller (5/14/2008)
Just curious - I was under the impression that encryption by certificate was really not recommended for large amounts of data, since it can only encrypt 117 bytes at a time. How does the bigger data size play into that? Do the repeated 117-byte chunks (or rather - the encrypted length of the 117 bytes) make it a lot easier to detect the patterns, and thus break the encryption?
In general, the more encrypted data you have to play with, the easier it is to detect patterns (and use statistical methods to greatly reduce the number of brute-force attempts required).
However, a typical implemention of asymmetric keys is to securely transmit a random symmetric key to both parties; from that point on, encryption/decryption is carried out using the faster symmetric key. The actual algorithm used for asymmetric keys (eg. RSA, Diffie-Hellman) and symmetric keys (eg. AES, Blowfish, DES) can vary, as well as the key size.
My reading of BOL suggests that when a cert is used by MSSQL, only asymmetric encryption is carried out; can anyone confirm this?
Andy
May 16, 2008 at 8:26 am
Certificates implement asymmetrical encryption. I believe they encrypt a symmetric key, which is used to encrypt the data. Asymmetric keys have limits to how much data they encrypt, AFAIK.
PKI should work, but it still requires backups. You need to be sure that the servers containing the root certificates can be recovered if there are issues, they can be restored with PKI intact. It's a similar problem, but it has economies of scale since you're talking about certificates for lots of purposes rather than just 1 or 2.
I'm not sure how this works with the Service Master Keys. The Database Master is encrypted with the Service Master. I'd assume the Service Master is integrated with PKI, but if it is, I haven't seen instructions on how to recover it if there's an issue. Since you need it to get the DMK open, if there is some issue with the server, you need a way to recover this.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply