Password Encryption Enhancement Request
PDinCA
Posts: 642 Silver 1
Didn't find a post elsewhere on the forum, do excuse if already requested...
The backup command uses
The backup command uses
PASSWORD = ''<ENCRYPTEDPASSWORD>owM5dGLGbGE=</ENCRYPTEDPASSWORD>''but the RESTORE shows the password in plain text
PASSWORD = ''mypassword''Please modify the RESTORE command to use the ENCRYPTEDPASSWORD form so I can capture the script for regular restores to dev boxes without disclosing the real encyription password to developers who may have access on the dev box...
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
Decide wisely...
Comments
The reason we don't allow this right now is that the encrypted password would loose all value if you could restore a backup using the encrypted version (If I could access backup job and see the <ENCRYPTEDPASSWORD> I could restore the database wherever I wanted to and access the data) - for a restore to happen we require that the original secret (the unencrypted password) is provided.
One solution would be to use a different encryption scheme for our restore password and our backup password but this could lead to confusion. I will log a feature request for our product manager and designers to look at for future releases.
Another way that we have looked at is to use SQL Server's security model to protect the password - create a function/database table etc which will return the password (or even the whole backup command) and secure the execution and access rights on that function/database table etc.
James
Head of DBA Tools
Red Gate Software Ltd
I think "informed confusion" would be fine! If the person setting up the job knows the password then providing two versions, one that only works with the BACKUP command and one that only works with the RESTORE would be OK.
Whichever solution you choose, encrypted (1 or 2 passwords) or plain, there are still "need to know" and "don't need to know" groups - it's being able to access the password that's the point-of-risk.
Hope you can devise a secure method that fits all scenarios, both same-server backup-restore and across server...
Keep us posted...
Decide wisely...
Exactly, the way it should be.
> but this could lead to confusion
I don't think it would lead to any confusion, esp. if the original password stays legal. Nothing in backup-restore procedures and generated scripts would have to be changed. But you would just have an option in the GUI to get a 'restore key'. And it would be as simple as using a new salt value in the encryption algorithm.
> I will log a feature request for our product manager and designers to look at for future releases.
Thank you
The password is out in the open, in plain text. Regardless of how the passwod 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), he can simply run the same script to run the restore without having to know the actual password. Thus, you 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. 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
You're absolutely right petey, it was so obvious that I felt ashamed for missing the point
Perhaps the winapi could be used to keep the context limited, but again this brings a lot of considerations and potential problems.