SQL Server Performance

Who needs to backup service master key ?

Discussion in 'SQL Server 2005 General DBA Questions' started by acki4711, May 23, 2007.

  1. acki4711 Member

    Hi all,

    I use certificates (installed on the sql server cluster) to encrypt communication to all my clients (set the force encryption flag on server).
    If not other data encryption is used (to store encrypted data in db's) is it still nescesery to backup the SMK (service master key) for any desaster/restore scenario??

    For my understandig if my cluster burns down I set up new hardware, install os, sql server and a new cert, restore a backup of my sys db's (master, model, msdb) and all backups of my user db's. So far I haven't heard anything about resotring a SMK.

    I was a bit confused looking into this threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21670 so I started this new thread because it my interest others as well.

    Thx for an update

  2. satya Moderator

    If you are using a different data encryption then use relevant keys to handle the data.

    SMK is used to encrypt all database master keys as well as all server-level secrets such as credential secrets or linked server login passwords. The key itself is a 128bit 3DES key.

    The reason I have suggested to restore with FORCE option, RESTORE statement will read the new SMK from the file, will decrypt the data encrypted using the current SMK, and will re-encrypt it using the newly read SMK. If for any reason the SMK is corrupted or unable to read then the encryption or decryption of data will not be functional.

    So on the subject matter if you are in process to continue the old SMK then you have to restore them, otherwise build a new one.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page