May 27, 2011 at 1:04 pm
Why create a false sense of security by encrypting the backups, while giving "trusted" employees all the keys? Trust no one! 🙂
Since the PASSWORD feature is going to be removed from SQL Server, let's encrypt the backup files after they're created or intercept the output file stream during backup -- we want to prevent password snooping via SQL Profiler.
An encryption service running on a locked-down app server would use a randomly generated password for each file, which is saved with the file name to a table somewhere -- both the file name and the password are encrypted, so look-ups are by encrypted file name. Users are never exposed to any of the actual values used along the way, and users looking at the stored data would not know which row corresponds to which file (to that end, randomize the order of the stored data so it's not sequential).
Even though each file is now secured by a unique and permanent password, the names/passwords file must be doubly protected. To ensure one person does not hold all the keys for decrypting this names/password data, I like the idea proposed earlier of having two individuals each making up and entering half of the encryption key and storing the two halves separately, off-site. These will only be needed only if the service machine dies, since the service would store the only digital copy of the key using Windows Data Protection locked to the machine.
If the key needs to be changed, all names/passwords data must be decrypted and re-encrypted with the new key. The encrypted files do not need to be re-encrypted because individual passwords are never revealed.
A thief who manages to acquire an encrypted backup file would also need the encrypted name/password file and would also need to steal each half of the encryption key.
OK, this may have gone a bit overboard, but it's probably nothing a .Net Master would find challenging if given a couple of months to work on it. 🙂 Of course, now an IT master will probably come along to say that this process is already built-into SCOM or something.
May 28, 2011 at 6:32 am
brian.schallhammer (5/27/2011)
Why create a false sense of security by encrypting the backups, while giving "trusted" employees all the keys? Trust no one! 🙂Since the PASSWORD feature is going to be removed from SQL Server, let's encrypt the backup files after they're created or intercept the output file stream during backup -- we want to prevent password snooping via SQL Profiler.
An encryption service running on a locked-down app server would use a randomly generated password for each file, which is saved with the file name to a table somewhere -- both the file name and the password are encrypted, so look-ups are by encrypted file name. Users are never exposed to any of the actual values used along the way, and users looking at the stored data would not know which row corresponds to which file (to that end, randomize the order of the stored data so it's not sequential).
Even though each file is now secured by a unique and permanent password, the names/passwords file must be doubly protected. To ensure one person does not hold all the keys for decrypting this names/password data, I like the idea proposed earlier of having two individuals each making up and entering half of the encryption key and storing the two halves separately, off-site. These will only be needed only if the service machine dies, since the service would store the only digital copy of the key using Windows Data Protection locked to the machine.
If the key needs to be changed, all names/passwords data must be decrypted and re-encrypted with the new key. The encrypted files do not need to be re-encrypted because individual passwords are never revealed.
A thief who manages to acquire an encrypted backup file would also need the encrypted name/password file and would also need to steal each half of the encryption key.
OK, this may have gone a bit overboard, but it's probably nothing a .Net Master would find challenging if given a couple of months to work on it. 🙂 Of course, now an IT master will probably come along to say that this process is already built-into SCOM or something.
There are some interesting things to worry about there, if you really want a secure trust-no-one setup.
First, you have no security at all against one of the two half-password holders stepping under a bus. If that happens, your backups and the filename-password store are so much junk. So the system is good on the secrecy aspects of security, but worse than useless on the availability aspects of security. This can be fixed by the usual game of generating N key components with the ability to reassemble the key from any M components, where M < N, and the scheme probably needs that change before it is useful.
Second, random keys are a mythical beast unless you have a genuine source of random data; for this task it probably needs to be an automatic random source, because manual intervention in the key generation is a pain (and may not be as random as you might think). Your locked down app server doing the encryption/decryption must have this hardware device, or your security will only be as good as your non-random (pseudo-random) key generation.
Third, the security is only good as the locked down app server's security; for example, if someone can get a state dump of the encryption program while it's encrypting, they can maybe get the key. So that serever probably needs physical security and needs to be also the file server for the encrypted data; you need also to find some way of getting the master file and the backups off this server onto some external (off-site) storage if you care about disaster recovery, and you have to do that in such a way that you don't reduce the secureness of the server's lockdown (for example by attaching it to an inadequately secured network in order to copy files to networked storage). As the master password is held on that server in a form (according to your description) in which the server can be got to use it to begin a decryption, the security overall can't be greater than the security of beginning that operation: so if you the security provided by more than one person having to consent to decryption, you need some two-person access to the decryption process, not just to the process of reconstructing the key; it's probably easiest if the app handles that key as an encrypt only key, so that you have to reconstruct from teh components held by people every time you want to decrypt, and accept the pain of generating a new key at that point.
Fourth, distribution of the master key (ie the key which decrypts the file-key file or table) components is potentially an issue; the master key has to be generated on the locked down server, but the components have to be got to each holder in a secure manner; so you probably want each holder to generate an asymmetric key pair; then the public keys can be given to the locked down app server, and the master key components can each be encrypted with the holder's public key for distribution.
Fifth, the master key needs be replaced every time a holder leaves and also every time it is reconstructed for decryption. This can be a real pain if you have high staff turn over.
Sixth, there is no point at all in securing backups in such a manner unless the database that they are backups of is secured at least as soundly. Sounds obvious, doesn't it, but people have been known to forget it.
Tom
May 28, 2011 at 9:51 am
I was thinking that Keepass would be good for this. Even without expiration, one could create subfolders to help organize the passwords along with timeframes that the password is effective.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 31, 2011 at 9:08 am
Tom, excellent addendum. The security of the encrypted backups is only as secure as your database environment, no matter how careful you are with passwords. And at the very least, more than one person needs to know how to retrieve the keys in case someone gets hit by a bus.
June 1, 2011 at 8:48 am
As I see it, there are three main objectives to be met:
- Each backup should have its own unique password.
- The method of password generation must not be directly related to, or easily derived from, the backup identifiers (time, machine or operator etc.
- Physically unconnected methods should be used for storing the backups versus passwords.
My simple method uses a hardcopy logbook and a book of cloakroom tickets as a set of unique random numbers, as per the following process:
- Make the backup assigning a cloakroom ticket number as the password.
- Log the backup in the hardcopy log noting the identifier (tape, filename, date, time machine, operator etc) together with the password used.
- Store the backup in the normal way.
- Delete / destroy the random number to prevent re-use.
Put the hardcopy log and unused cloakroom tickets back in the office document safe or similar.
The impossible can be done at once, miracles may take a little longer 🙂
June 1, 2011 at 10:35 am
- Each backup should have its own unique password.
...
- The method of password generation must not be directly related to...
- Physically unconnected methods should be used for storing the backups versus passwords.
- Make the backup assigning a cloakroom ticket number as the password.
I might be wrong, but aren't those ticket spools numbered consecutively? That violates two of your principles The passwords aren't really unique (they are an increment) and the passwords are directly related to the backups by their sequence. In addition you have severely limited the password space to 6-8 characters digits only.
To solve all of those problems you could just use a software password generator and write that password into the log book.
--
JimFive
June 2, 2011 at 3:21 am
Well naturally you wouldnt pick the next stub. just flick through the book to a random page and pick a random ticket out of it.
I like tihis idea.
You could make the password stronger by having 2 books, one with numbers and one with letters.
pick number 7, that becomes 0007 in the password, pick letters ABC and that becomes ABC! in the password. or, pick D and it becomes D!!! giving you a password of 0007D!!!.
according to my password strength calculator it would take an average desktop PC 38 days to crack that.
add another book of numbers to give you a 12 character password and it would take 607 thousand years to crack.
ticket stubs are a good idea.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 2, 2011 at 6:30 am
Thanks Ben.
Just to confirm the finer detail:
Tickets are pulled from the book at random, numbered 1-999, but in addition to the big number there is a 6 character alphanumeric book identifier.
By combining the two, and using leading zeros on the number, we get a minimum of a 9 character password eg. ABCDEF007.
The important thing is to store the log book in a physical place completely unconnected with the backups.
Anything stored on-line - e.g. a spreadsheet log relating to a set of backup directories and files - must be viewed as being in the same physical place.
The impossible can be done at once, miracles may take a little longer 🙂
June 2, 2011 at 7:31 am
All this talk about code books and elaborate schemes for generating one-off passwords sounds like overkill. If a tornado should strike your company headquarters, I hope you have a backup copy of your code book somewhere, and more than one person knows how to use it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 2, 2011 at 7:36 am
TO THE CLOUD!! 🙂
June 2, 2011 at 8:53 am
Dave:
It sounds like you have a good system that works well for you. If you don't mind a few questions:
1. Are your passwords recorded manually, and if so, have you factored in the human element, for example:
* Transcription errors, like reversing the order of characters, or forgetting to put a slash through a zero, so that it looks like the letter 'O'.
* The ink smearing just enough that an 'F' looks like a 'P'.
* The drop of mustard that no one noticed that makes an entry unreadable.
2. How do you get to the logbook at 3AM? Does the oncall DBA keep it, and if so, how do they secure it?
Thanks,
Peter
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply