What are the challenges you face when working across database platforms? Take the survey
Options

Password Encryption Enhancement Request

PDinCAPDinCA Posts: 642 Silver 1
edited June 27, 2008 8:30PM in SQL Backup Previous Versions
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...
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...

Comments

  • Options
    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
    James Moore
    Head of DBA Tools
    Red Gate Software Ltd
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    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... :)
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    > 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
  • Options
    peteypetey Posts: 2,358 New member
    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 = &#91;password&#93;..."'
    
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
Sign In or Register to comment.