none
Backup of the Service Master Key

    Question

  • For backing up the service master key for SQL Server, is there any thing that says how often the key should be backed up?

    We are currently not encrypting anything in the database, but we are looking at encrypting the database when we move to SQL Server 2008R2 or SQL Server 2012 from SQL Server 2005.

    Thanks

    Don Jones

    Friday, November 30, 2012 7:08 PM

Answers

  • Hi Done Jones,

    The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key.

    Back up the Service Master Key and store the backed up copy in a secure, off-site location.

    If you used ALTER SERVICE MASTER KEY to change the service master key of an instance of SQL Server, you need to back up the key. If you do not change it, it is OK to keep the old Service Master Key backup.

    If you have any feedback on our support, please click here.


    Thanks.


    Maggie Luo
    TechNet Community Support

    Monday, December 03, 2012 10:12 AM

All replies

  • Hello,

    This is based of of internal policy, SMK changes, and required complience policies.

    For example:

    If you have an SMK that was changed 2 months ago and have off-site backups that date back to 4 months then I would hope a copy of the SMK from before it was changed is available. This obviously all depends on if you're actually using the SMK to do automatic key decryption or not, etc.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, November 30, 2012 7:24 PM
  • Hi Done Jones,

    The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key.

    Back up the Service Master Key and store the backed up copy in a secure, off-site location.

    If you used ALTER SERVICE MASTER KEY to change the service master key of an instance of SQL Server, you need to back up the key. If you do not change it, it is OK to keep the old Service Master Key backup.

    If you have any feedback on our support, please click here.


    Thanks.


    Maggie Luo
    TechNet Community Support

    Monday, December 03, 2012 10:12 AM
  • Hi Don,

    There is no need to backup the SERVICE MASTER key, because SERVER MASTER KEY is created automatically when SQL Server starts-up and it is encrypted using Windows Operating System Data Protection (API).

    When encrypting your database, you should only backup your Database Master Key (DMK), Symmetric Keys, Asymmetrick Key and Certificates.

    I suggest you to read my following post that discusses the various options available for encryption in SQL Server 2005 onwards:

     


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Monday, December 03, 2012 12:48 PM
    Monday, December 03, 2012 12:48 PM