January 10, 2017 at 3:49 am
I hate to do a double post, however, my topic in the DR forum hasn't had any notice 🙁 so i thought I'd go into the ever more popular SQL 2012 Administration Forum.
I've just going to quote my post. If anyone has any insights, I'd really appreciate it.
Thom A (1/3/2017)
Hi All,I wanted to check how others had dealt with dealing with encrypted backups when doing a DR. I'll voice what I have a plan to do, but would appreciate any input that others might have.
We won't be encrypting all of our back ups, some don't hold any personal or sensitive data, and would give other little to no information. Those we do, however, I plan to have their passwords stored in two places. One in our "Password Vault", which is a third party application. This is hosted on our file server, which is backed up to tape on a daily basis, which are collected and held off site for several days before being returned to be reused.
Secondly, however, I want to store them in an encrypted field (using certificates and keys) in one of our databases. The reason for this is mainly the automation, as the third party application doesn't have any way to interact with it (that I know of), apart from the GUI.
I already have a Powershell script which restores our backups. Firstly it restores Master (for obvious reasons), and then restarts the service and restores the rest of our databases. It works for both our SIMPLE and FULL backup process, and I and very happy with it's performance. It doesn't require any user interaction apart to prompts to run the Master Restore Script it has generated(for some reason it always failed when it ran it in the same task, so the user has to run that separately).
My plan, therefore, is add change my existing to the following:
-After the Master has Restored, the next database to be restored will be the one that holds the encrypted database passwords.
-A prompt will be displayed where the database's own encryption password will need to be supplied.
-Restore the User Database.
-Amend the script the restore the remaining, however, add a variable into the script to obtain the password from the other database, and pass that. For exdample, the powershell would create a SQL script along the lines of:
--Assume that I have already opened the CertificateDECLARE @MediaPW VARCHAR(100);
SELECT @MediaPW = CONVERT(varchar, DecryptByKey(MediaPW))
FROM DBA.sec.DBEncPW
WHERE DatabaseName = 'Customer';
RESTORE DATABASE Customer
FROM DISK = 'C:\SQLBackups\Customer\Customer.Bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
MEDIAPASSWORD = @MediaPW,
RECOVERY;
Would this work, or does anyone know of a better solution for a DR process?
If people would like, I'm more than happy to share the Powershell script that I have created, in case it provides a bit more insight.
Many thanks for any and all help 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2017 at 6:15 am
Thom A - Tuesday, January 10, 2017 3:49 AMI hate to do a double post, however, my topic in the DR forum hasn't had any notice 🙁 so i thought I'd go into the ever more popular SQL 2012 Administration Forum.I've just going to quote my post. If anyone has any insights, I'd really appreciate it.Thom A (1/3/2017)
Hi All,I wanted to check how others had dealt with dealing with encrypted backups when doing a DR. I'll voice what I have a plan to do, but would appreciate any input that others might have.We won't be encrypting all of our back ups, some don't hold any personal or sensitive data, and would give other little to no information. Those we do, however, I plan to have their passwords stored in two places. One in our "Password Vault", which is a third party application. This is hosted on our file server, which is backed up to tape on a daily basis, which are collected and held off site for several days before being returned to be reused.Secondly, however, I want to store them in an encrypted field (using certificates and keys) in one of our databases. The reason for this is mainly the automation, as the third party application doesn't have any way to interact with it (that I know of), apart from the GUI.I already have a Powershell script which restores our backups. Firstly it restores Master (for obvious reasons), and then restarts the service and restores the rest of our databases. It works for both our SIMPLE and FULL backup process, and I and very happy with it's performance. It doesn't require any user interaction apart to prompts to run the Master Restore Script it has generated(for some reason it always failed when it ran it in the same task, so the user has to run that separately).My plan, therefore, is add change my existing to the following:-After the Master has Restored, the next database to be restored will be the one that holds the encrypted database passwords.-A prompt will be displayed where the database's own encryption password will need to be supplied.-Restore the User Database.-Amend the script the restore the remaining, however, add a variable into the script to obtain the password from the other database, and pass that. For exdample, the powershell would create a SQL script along the lines of:--Assume that I have already opened the CertificateDECLARE @MediaPW VARCHAR(100);SELECT @MediaPW = CONVERT(varchar, DecryptByKey(MediaPW))FROM DBA.sec.DBEncPWWHERE DatabaseName = 'Customer';RESTORE DATABASE CustomerFROM DISK = 'C:\SQLBackups\Customer\Customer.Bak'WITH FILE = 1, NOUNLOAD, REPLACE, MEDIAPASSWORD = @MediaPW, RECOVERY;Would this work, or does anyone know of a better solution for a DR process?If people would like, I'm more than happy to share the Powershell script that I have created, in case it provides a bit more insight.Many thanks for any and all help 🙂
you're restoring the master database to a DR server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 12, 2017 at 8:06 am
That should work OK. I might ensure the account doing the restores has access to the certificate, but others are not granted permissions. Not sure it's a great help since a sysadmin is a weak point, but it's not bad.
I'm guessing the PoSh script runs under an account and that account connects to the SQL instance? Or do you have encrypted credentials to connect?
January 12, 2017 at 8:21 am
Steve Jones - SSC Editor - Thursday, January 12, 2017 8:06 AMThat should work OK. I might ensure the account doing the restores has access to the certificate, but others are not granted permissions. Not sure it's a great help since a sysadmin is a weak point, but it's not bad.I'm guessing the PoSh script runs under an account and that account connects to the SQL instance? Or do you have encrypted credentials to connect?
The only people who have sysadmin access are those that could be performing a DR anyway, so that shouldn't be a much of a weak point.
The script can be run under any user who has admin privileges on the server (as it needs to stop and start the SQL Service and add/reove Flags for Single user Mode). It specifically requests the password for the DR account when running as a prompt. if you don't have the password, you can't restore the databases. The DR account uses SQL Authentication, and has the DBCreator Server Role. When completing testing previously (without encryption), after each restore of a database is completed, the DR Account loses any access to data within the restored database, as the DR Account has no other permissions on the server.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2017 at 9:36 am
Thom A - Thursday, January 12, 2017 7:02 AMYes, Master database first, then the remainder.
its been said before but i'll say it again. Do not restore system databases across server. For a DR machine sync the objects in the 2 instances
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 12, 2017 at 9:40 am
Perry Whittle - Thursday, January 12, 2017 9:36 AMThom A - Thursday, January 12, 2017 7:02 AMYes, Master database first, then the remainder.its been said before but i'll say it again. Do not restore system databases across server. For a DR machine sync the objects in the 2 instances
How do you achieve this when the idea of DR is that the original machine has been lost/damaged beyond repair?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2017 at 9:48 am
The only concern I'd have is ensuring no one can access your password database. I'd be careful that admins don't accidentally add a user to this from another login. Perhaps a regular audit or report that ensures no new users are added. Or no users are added and only allow sysadmins access.
January 12, 2017 at 9:58 am
Steve Jones - SSC Editor - Thursday, January 12, 2017 9:48 AMThe only concern I'd have is ensuring no one can access your password database. I'd be careful that admins don't accidentally add a user to this from another login. Perhaps a regular audit or report that ensures no new users are added. Or no users are added and only allow sysadmins access.
We have reports that check for credential changes, so we have that covered. It's also stored in a separate schema, just to make sure in case someone grants access.
Someone malicious having to get a login added to the database with sysadmin (or correct permissions) is better than it is at the moment, where if some gets the back up they can restore the data. Plus, if they have sysadmin, then they could just access the data directly anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply