May 26, 2011 at 9:14 pm
Comments posted to this topic are about the item The Backup Passwords
May 27, 2011 at 12:06 am
We have implemented something we call a poker key server. To retrieve a database encryption key you have to enter a long (20 character) password of which ten characters are placed in sealed envelopes in two person's locked file cabinets. If you enter the password incorrectly, the server "calls" and you are required to prove your identity with an iButton (from Maxim/Dallas Semiconductor) containing a unique serial number which is then hashed by the server using SHA-384. If the iButton hashes correctly it gives you the option of entering the 20-character password again.
It sounds like a lot of work, but two-person control combined with a skeptical key server ensures our intellectual property remains safe.
May 27, 2011 at 12:29 am
First though I have is a kind of function that returns a key base on some well know variables like: date when the backup run, user in charge of the backup and machine where the database is placed, and finally a variable value that few people know (you and another one). If the name of the backup include all the variables values (i.e. BCK_DDMMYYYY_NAME_XXXXBOX) except obviously the "secret" variable value, you can retrieve the password for the back up whenever you want and independently of who generate the backup and when it was generated.
The function to generate the variable should be protected accordingly.
May 27, 2011 at 2:53 am
I have a folder in my inbox marked 'passwords' in that i have folders named in the format yyyymmdd. every time the passwords change I create a new folder and save an email.
is this a good idea? no. is it the best option currently available to me? yes. it's either that or I keep a rolodex of passwords in my filing cabinet (probably a better idea)
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 27, 2011 at 3:22 am
Seems like a bit too much trouble for something as simple as keeping track of passwords for backups across time in a secure manner. There are a few simple alternatives here. Firstly one could use one of many simple programs available (many of which are free) for storing passwords with comments in a secure manner. The comments can be used to indicated the date stamp. Alternatively get someone in your company to create a simple app that saves your passwords and dates and an additional comment in an encrypted format. This will have two advantages over existing software. The first will be that it will be unique to your company and thus will not have a hack. The second will be that one can taylor the app to your individual company needs. To a person familiar with C++ or VB this should take no longer than an hour to develop and deploy.
May 27, 2011 at 3:26 am
Well, being an analyst programmer, I ought really go and do that myself. I might get round to it somewhen.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 27, 2011 at 5:53 am
KeePass (http://keepass.info) will archive old passwords for you. It also handles expiration, so you can look at your archived and know which dates it was active. Plus it has the nice benefit of having a URL/name that can be slightly rearranged for puerile entertainment. 🙂
May 27, 2011 at 6:35 am
Managing backups has never been my role, so I'm not too familiar with the technicals of how the various 3rd party solutions manage the keys. My question is: Does the database administrator really need to know the password for the backups in order to maintain the day to day backup process?
My thinking is that the passwords could be held by an executive manager. Even on those occasions where a restore from backup is required, the manager could supply the password remotely without revealing it to the database administrator. This may result in a slight delay of recovery time, but if one person holds the backups and another person holds the key, then one of them acting alone could not compromise the data, even if the password remains static over a long period of time.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2011 at 7:07 am
We have recently started using KeePass as well and consider it very useful.
May 27, 2011 at 8:15 am
I've not been responsible for backups for a long time, but here's how I think it should work.
Each backup set gets assigned a strong generated password that applies only to that set.
The Backup Set ID/Password pair is stored in:
a) A key server that the backup/restore software has access to based on user level permissions. Or...
b) A notebook in someone's office. Or...
c) Both (I actually prefer this as I don't trust the key server to not crash and burn.)
Backup tapes are moved offsite on a reasonably schedule (weekly?).
--
JimFive
May 27, 2011 at 8:18 am
Eric M Russell (5/27/2011)
My question is: Does the database administrator really need to know the password for the backups in order to maintain the day to day backup process?
Don't you need to supply the password each time you back up a database?
FYI, according to Books Online, the PASSWORD option is "intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users." and according to Books Online, "will be removed in the next version of Microsoft SQL Server."
May 27, 2011 at 8:26 am
Our primary system (not SQL server) has an encrypted password key table which keeps the archive of old passwords. When we do a backup, we select which key we want to encrypt it with without specifying the actual password. The same with a restore. So even people who do regular backups & restores don't need to know the password. The keys are named by date so it's generally pretty straight-forward to know which key was used for a given backup.
Now if a tape is restored to hardware other than our own it won't work because the key table wouldn't exist.
Leonard
Madison, WI
May 27, 2011 at 8:58 am
Peter Maloof (5/27/2011)
FYI, according to Books Online, the PASSWORD option is "intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users." and according to Books Online, "will be removed in the next version of Microsoft SQL Server."
It's also interesting to note that it says that preventing a restore is only thing it's intended to do and that the backup isn't encrypted.
The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password.
May 27, 2011 at 9:13 am
mgraham 67977 (5/27/2011)
We have implemented something we call a poker key server. To retrieve a database encryption key you have to enter a long (20 character) password of which ten characters are placed in sealed envelopes in two person's locked file cabinets. If you enter the password incorrectly, the server "calls" and you are required to prove your identity with an iButton (from Maxim/Dallas Semiconductor) containing a unique serial number which is then hashed by the server using SHA-384. If the iButton hashes correctly it gives you the option of entering the 20-character password again.It sounds like a lot of work, but two-person control combined with a skeptical key server ensures our intellectual property remains safe.
That sounds really cool. I've wished we had two factor authentication in SQL Server at the table or column level. Not sure how it would caused application issues, but it's something I'd like to see.
May 27, 2011 at 9:19 am
The passwords in native SQL Server backups are weak.
I was thinking more of managing the encryption keys/passwords for third party tools that encrypt the backups. Tools like SQL Backup Pro and others.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply