locked
Backing up keys RRS feed

  • Question

  • Hi all,

    I've create a master key, certificate & symmetric key in SQL 2005 and could just do with a sanity check regarding the
    backup.  The bit I'm not sure about is the backup of the symmetric key and if my backup command below has got it: 


    create master key encryption by password='****************************'
    go
    
    CREATE CERTIFICATE TransfersCertificate WITH SUBJECT='Transfers Database'
    go
    
    CREATE SYMMETRIC KEY TransfersKey 
    WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE TransfersCertificate
    GO 
    
    BACKUP MASTER KEY TO FILE = 'c:\master_key_transfers.dat' 
    ENCRYPTION BY PASSWORD = '***************'
    GO
    
    BACKUP CERTIFICATE TransfersCertificate TO FILE = 'c:\certificate_transfers.dat'
        WITH PRIVATE KEY ( FILE = 'c:\private_key_transfers.dat' , 
        ENCRYPTION BY PASSWORD = '***************' );
    GO



    Regards
    Dave  




    Wednesday, October 7, 2009 3:24 PM

Answers

  • Backing up SYMMETRIC KEYs is not supported, but when you back up the whole database the SYMMETRIC KEYs are backed up within it.

      For scenarios where the objective is to share a key across multiple DBs there is an alternative, creating a SYMMETRIC KEY with an identity_value and key_source that can be regenerated in a different server/database. Laurentiu wrote an article describing this process in detail: http://blogs.msdn.com/lcris/archive/2006/07/06/sql-server-2005-how-to-regenerate-the-same-symmetric-key-in-two-different-databases.aspx

      One alternative better suited for external key management is to use EKM to access keys stored and managed outside SQL Server (i.e. in an HSM).

      I hope this information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, October 7, 2009 6:12 PM
  •   There is no backup operation for individual SYMMETRIC KEY objects, the only two alternatives are:
    * backup the entire database
    * manage the symmetric keys outside SQL Server using EKM.

      I hope thsi information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by york0001 Monday, October 19, 2009 7:34 AM
    Friday, October 9, 2009 12:11 AM
  • Hi,

    Have a look at Understanding Extensible Key Management on Books Online, see here: http://technet.microsoft.com/en-us/library/bb895340.aspx
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 9, 2009 7:55 AM

All replies

  • Backing up SYMMETRIC KEYs is not supported, but when you back up the whole database the SYMMETRIC KEYs are backed up within it.

      For scenarios where the objective is to share a key across multiple DBs there is an alternative, creating a SYMMETRIC KEY with an identity_value and key_source that can be regenerated in a different server/database. Laurentiu wrote an article describing this process in detail: http://blogs.msdn.com/lcris/archive/2006/07/06/sql-server-2005-how-to-regenerate-the-same-symmetric-key-in-two-different-databases.aspx

      One alternative better suited for external key management is to use EKM to access keys stored and managed outside SQL Server (i.e. in an HSM).

      I hope this information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, October 7, 2009 6:12 PM
  • Hi,

    I'm not looking to share the key(s) accross different databases, I'm purely thinking about recovery. 
    For example if the keys or certificate got corrupted how could I re-create them and salvage the
    data without a db restore?

    Can you tell me what is actually backed up by the command "with private key"?


    BACKUP CERTIFICATE TransfersCertificate TO FILE = 'c:\certificate_transfers.dat'
        WITH PRIVATE KEY ( FILE = 'c:\private_key_transfers.dat' , 
        ENCRYPTION BY PASSWORD = '***************' );
    



    Thanks
    Dave
    Thursday, October 8, 2009 8:34 AM
  •   There is no backup operation for individual SYMMETRIC KEY objects, the only two alternatives are:
    * backup the entire database
    * manage the symmetric keys outside SQL Server using EKM.

      I hope thsi information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by york0001 Monday, October 19, 2009 7:34 AM
    Friday, October 9, 2009 12:11 AM
  •   There is no backup operation for individual SYMMETRIC KEY objects, the only two alternatives are:
    * backup the entire database
    * manage the symmetric keys outside SQL Server using EKM.

      I hope thsi information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Just Google's "EKM" and it's returned a varied list.  Could you please tell me which EKM you are talking about?

    Dave

    Friday, October 9, 2009 7:10 AM
  • Hi,

    Have a look at Understanding Extensible Key Management on Books Online, see here: http://technet.microsoft.com/en-us/library/bb895340.aspx
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 9, 2009 7:55 AM