Logshipping with encrypted password

Koen Mevissen

I've been testing with setting up logshipping through SQL Backup in order to replace the SQL Server logshipping. Main reason for this is easy to configure file encryption, and easier configuration of retention periods on the storage locations.

While testing with the set up I was checking the backup and restore jobs and statements. I noticed in the backup job, the password for file encryption is nicely encrypted as well, in between tags: <ENCRYPTEDPASSWORD>MyEncryptedPassword</ENCRYPTEDPASSWORD>

I noticed on the restore statement, the password was unencrypted. I tried changing this to <ENCRYPTEDPASSWORD>MyEncryptedPassword</ENCRYPTEDPASSWORD> but that made the statement fail.

Is it possible to configure the restore job with the password beeing encrypted, instead of unencrypted and thus readable? Or is there a reason to leave it unencrypted?

petey

It's not possible to use an encrypted password for the restore. Reason is because if someone gets hold of the encrypted password, e.g. from the backup job, that person can then simply restore the backup without knowing the actual password e.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE ... WITH PASSWORD = [<ENCRYPTEDPASSWORD>MyEncryptedPassword</ENCRYPTEDPASSWORD>] ...

Also, by not encrypting the password in the restore job, you tend to be more careful with managing access to the jobs.