January 14, 2015 at 2:54 pm
Background
I am a report writer primarily focused on data in a database that stores information from a vendor supplied management system. On a regular basis I am asked to identify when a data change was made, and what the data was like at a specific time. An example of a request is a report that shows orders confirmed but not sent at the present date, compared to those a year again. You'd think you could just show orders where current date is between the confirmation and sent dates, compared to those where the current date minus 1 year is between the confirmation and sent dates. In this business, it is common practice for employees to re-jig the data to look good to head office; quite often by deleting a range of orders, then re-creating them with more favorable figures. This means when I am trying to track when Order_ID 1234 was created I quite often find it was well after the order was actually completed, that it was retroactively deleted and re-added. What was the data held in the "non-re-jigged" version that existed a year ago? Often it's significantly different than the new version. I can't change the vendor's database, or enforce better processes to stop this kind of data manipulation. Most operations aren't logged, and I can't add my own logging to that system.
The database is backed up by full and diff logs by an outside vendor, however their process means I don't have access to their backup systems. Besides either doing my own change database, taking full database backups nightly seems to be the best approach to gaining historical data.
Actual query
I need to create my own nightly database backup for reporting purposes. Currently I run a SSIS package to create a database backup, then put this inside an encrypted 7-zip (AES-256 + Encrypted filenames, Ultra compression), then delete the .bak file and store the resulting archive on an office workstation. Two flaws with this process are:
The database backup is un-encrypted during the time it is being encrypted, someone could use recovery software to retrieve the full database backup from this point in time.
The 7zip encryption key is stored in the SSIS package, and the 7zip process is called with the encryption key as a command parameter - it would be reasonably easy to read the command parameters that task was started with.
I need to be able to backup from the live server, then restore onto my reporting development workstation.
I am looking at creating a symmetric key on both the live server and development workstation with a password I know and have stored in a secure password management system. This would mean the database could be backed up and restored without having the text key stored anywhere, and using BACKUP WITH COMPRESSION and not doing the extra 7zip compression step.
Is this a sensible approach to this non-critical backup stored on a development workstation?
January 14, 2015 at 3:19 pm
Security of this type is in the eye of the beholder I think.
Personally, I would pay the cost and get Red-Gate's SQL Backup and be done with it. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply