locked
SQL Server DATA Encryption - Clarification RRS feed

  • Question

  • This is to check if my understanding is correct on Data encryption in SQL server 2005/2008.

    When we use Data encryption using the natice SQL server capabilities - using the funcaiton EncryptByCertificate/Key/Asymetrickey functions, The master Key, Certificate and all the symmetric and asymmetric keys ar stored in the same user database where the data encryption is being used.

    So, it is possible for a backup operator to take a backup of the database and restore it in a different server and decrypt it.

    Is the above undersntaing is correct (I know that this can be prevented using TDE in SQL 2008, but here i am talking abt only the data encryption features)

    Kindly reply

     

    Thursday, December 3, 2009 11:53 AM

All replies

  • So, it is possible for a backup operator to take a backup of the database and restore it in a different server and decrypt it.
    No, it is not possible, unless one of the following is true:

    - The backup operator already knows the password protecting the DbMK - so he had access to the data all the time
    - The backup operator restores the database to a server that has the same SMK - so, again, he had access to the data all the time

    You cannot circumvent encryption by restoring a database on a different server.
    This post is provided "AS IS" with no warranties, and confers no rights.
    Thursday, December 3, 2009 7:11 PM
  • I don't think it is correct.

    The backup operator can restore the database in any of the server where he has sysadmin privillege.
    Then its only a matter of regenerating the master key. All the certificates associated with that will get encrypted based on that.
    Friday, December 4, 2009 4:16 PM
  • But the existing user certs must be decrypted before being encrypted with the new master key.  The password protecting the old master key is needed in order to do that. 
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, December 5, 2009 5:22 PM