Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: How do I backup SQL server's encryption keys?

    I have created the following encryption keys


    /*************** CREATE MASTER KEY *********************************/
    IF NOT EXISTS (
    SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
    )
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@$$w0rd'
    GO


    /*************** CREATE CERTIFICATE *********************************/
    IF NOT EXISTS (
    SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'
    )
    CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';
    GO

    /*************** CREATE SYMMETRIC KEY *********************************/
    CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;


    Now, my problem is, how do I backup these keys and restore them in the future. I if possible, i need a sql statement for the Backup and Restore operations.

    Thanks ahead...

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The only key you need to backup and possibly restore is the database master key. Once you have that, all other keys can be read. You can also backup the server master key. I would only restore that to a DR server, where I would expect to restore databases that have encryption in them. Have a look at this article.

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    Ok,As MCrowley Mentioned that you can backup server master key ,I will spot on that point

    To back up the service master key

    In SQL Server Management Studio, connect to the SQL Server instance containing the service master key you wish to back up.

    Choose a password that will be used to encrypt the service master key on the backup medium. This password is subject to complexity checks. For more information, see Password Policy.

    Obtain a removable backup medium for storing a copy of the backed-up key.

    Identify an NTFS directory in which to create the backup of the key. This is where you will create the file specified in the next step. The directory should be protected with highly restrictive ACLs.

    In Query Editor, execute the following Transact-SQL command: BACKUP SERVICE MASTER KEY TO FILE = '<complete path and filename>' ENCRYPTION BY PASSWORD = '<password>' ; GO

    Copy the file to the backup medium and verify the copy.

    Store the backup in a secure, off-site location.
    To get more read
    How to: Back Up the Service Master Key

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •