none
Encrypted Backup file RRS feed

  • Question

  • I backed up database with encryption by using master key and certificate. I expected that the backup file would not be available for restoring without password but the restore was possible without any authentication required. 

    Is the backup file really encrypted? How can it be ensured? Or the encrypted backup file means something else.

    Please help.

    Wednesday, July 17, 2019 4:17 AM

Answers

  • Did you perform the restore on the same SQL Server instance where the master key & certificate exists? If so then test it on a different SQL Server instance.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 17, 2019 5:35 AM
  • Hi Curendra,

     

    >>Is the backup file really encrypted? How can it be ensured? Or the encrypted backup file means something else.

     

    You cannot restore an encrypted backup without the certificate used to encrypt the backup.  SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate.

     

    As mentioned by Olaf, did you try to restore it on the same instance? For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Curendra Tuesday, July 23, 2019 3:44 AM
    Thursday, July 18, 2019 6:38 AM

All replies

  • Hi,

    Could you please tell me if you made a backup, as described in the next article?

    Back Up a Database Master Key

    Restore a Database Master Key



    Avis de non-responsabilité:
    Mon opinion ne peut pas coïncider avec la position officielle de Microsoft.

    Bien cordialement, Andrei ...

    MCP

    Wednesday, July 17, 2019 4:50 AM
  • Did you perform the restore on the same SQL Server instance where the master key & certificate exists? If so then test it on a different SQL Server instance.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 17, 2019 5:35 AM
  • Hi Curendra,

     

    >>Is the backup file really encrypted? How can it be ensured? Or the encrypted backup file means something else.

     

    You cannot restore an encrypted backup without the certificate used to encrypt the backup.  SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate.

     

    As mentioned by Olaf, did you try to restore it on the same instance? For more details, please refer to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Curendra Tuesday, July 23, 2019 3:44 AM
    Thursday, July 18, 2019 6:38 AM