Scheduled Restores?

• 
  Thanks Tyanni for your feedback. This feature is very high on our list too and was under strong considerations for V 6.0 but it couldn't make it.
  
  But I am sure that this will definitely make into one of future SQL Backup releases very soon.
  
  Thanks,
  Priya

  priyasinha June 04, 2009 16:10

  - 0 +
• 
  Since I have your ear, any idea if I can encrypt the password in the restore job in v6? I understand putting this functionality back into the product (it was removed at some point in the past) is was/is being considered.
  
  Thanks!
  Tim

  tyanni June 04, 2009 16:15

  - 0 +
• 
  Hi Tim,
  
  If I understand correctly you are refering to using <ENCRYPTEDPASSWORD> in restore just like it is shown in backup script.
  
  The issue is that when you schedule restores script similar to the following:
  
  Code:
  EXEC master..sqlbackup '-sql "RESTORE ... WITH PASSWORD = [password]..."'
  
  The password is out in the open, in plain text. Regardless of how the password may be encrypted (different encryption scheme, different salt etc), as long as another user is able to access the script (via msdb, profiler, network sniffer etc), they can simply run the same script to run the restore without having to know the actual password. Thus you actually need to secure the script contained in the msdb table, and limit user rights to run Profiler.
  
  SQL Backup 3 allowed the use of encrypted passwords, but this lead to a false sense of security. We had a lot of internal discussions regarding this. We thought that DBAs were not worried that the restore script was exposed, not realising that the encrypted password was as good as the actual password. By forcing the use of the password in plain text, DBAs become more aware of the need to secure the script. And for that reason we pulled <ENCRYPTEDPASSWORD> support away from restore script.
  
  If you think there are better ways to encrypt restore script then please do suggest us. We would really appreciate it.
  
  Thanks,
  Priya

  priyasinha June 04, 2009 16:46

  - 0 +
• 
  priyasinha -
  
  The problem with this is that the password is also sent clear text in EMAILS, which is a HUGE problem. I actually didn't catch this until now. I understand some of what you are saying, although I still disagree because nothing should ever be sent in clear text over the network. In discussion here, we all agreed that the solution is a form of public/private key - require a key in order to do a restore.
  
  That being said, passing the password in clear text in the emails is a disaster - it can't be kept secure unless we start encrypting the entire channel.

  tyanni June 19, 2009 09:10

  - 0 +
• 
  Hi Tim,
  
  I was under the impression that the automated emails sent by SQL Backup for restores obfuscated the password; when I run a restore with a MAILTO command, it shows the password as:
  ... PASSWORD = 'XXXXXXXXXX', ...
  
  Is this the scenario you were talking about? Or is there a different case we aren't aware of? I'd certainly agree that sending out passwords in clear text would be a serious security issue.
  
  Thanks for the feedback,

  mattchandler June 19, 2009 11:34

  - 0 +
• 
  Emails DO NOT OBFUSCATE for restores created as a scheduled task manually on the server. The emails for backup jobs, all created via the gui, are obfuscated. It also appears that emails sent when the restore is created via the gui also do obfuscate.
  
  So - emails sent when a restore job is performed via the gui are fine. Emails sent when a restore job is run via a sql agent job, are NOT fine.
  
  Since scheduled restores are still not available via the gui (hint hint) we have to do it via agent jobs. This is a significant part of our workflow and can't be changed. At this point, I am looking at turning off the emails, but will involve some serious discussion with others above me since it means no awareness of the job outcome. This is a VERY serious issue...
  
  Tim

  tyanni June 19, 2009 11:51

  - 0 +
• 
  Hi Tim,
  
  If you specify the MAILTO, MAILTO_ONERRORONLY, or MAILTO_ONERROR keywords as part of the restore command SQL Backup uses, then SQL Backup ought to send out an email with the password obfuscated. This should be exactly the same as performing a restore from the GUI.
  
  If you send out an email from SQL Server containing the command string used (for example), then I'm afraid it will necessarily have the password used in plain text.
  
  Thank you for raising this issue. I think its fair to say that this usecase wasn't considered when we first removed the encrypted password in restore commands. Although its unlikely that we will implement a similar feature for the forthcoming release I hope we can do something to help in a future release. As Priya mentioned earlier in this thread, its quite possible that a 'scheduled restore' wizard might be added to SQL Backup in an upcoming version.

  mattchandler June 19, 2009 12:18

  - 0 +
• 
  We ARE using the MAILTO option. SQL is not sending the email, Redgate is. Thus, it should be working.
  
  I can understand that scheduled restores may not be implemented immediately. However, I need a solution to the plain text password issue, as its clearly a bug since MAILTO should be encrypting it.
  
  here is the actual command being run as a scheduled job in SQL (I've removed any identifiable information) -
  
  EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [DB_test] FROM DISK = ''\\path\to\my\share\Mirror\full_backup.sqb'' WITH RECOVERY, MAILTO = ''me@mydomain'', MOVE ''DB_Test_data'' TO ''D:\MSSQL.1\MSSQL\Data\DB_Test.mdf'', MOVE ''DB_Test_log'' TO ''D:\MSSQL.1\MSSQL\Data\DB_Test.ldf'', PASSWORD = ''HNFSD&^#JMMKJ'', REPLACE"'

  tyanni June 19, 2009 12:25

  - 0 +

Add comment

Please sign in to leave a comment.