Password Encryption Enhancement Request

PDinCA

Didn't find a post elsewhere on the forum, do excuse if already requested...

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...

James Moore

Hi there,

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

PDinCA

Point taken...

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...

MrRobot

> One solution would be to use a different encryption scheme for our restore password and our backup password

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

petey

The issue is that when users schedule restores at regular intervals e.g. log shipping, they need to run a script similar to the following:

EXEC master..sqlbackup '-sql "RESTORE ... WITH PASSWORD = [password]..."'

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.

MrRobot

he can simply run the same script to run the restore

-full stop-

You're absolutely right petey, it was so obvious that I felt ashamed for missing the point I fully agree with all your remarks.

Perhaps the winapi could be used to keep the context limited, but again this brings a lot of considerations and potential problems.