What does the Service Master Key protect?

Question

I am looking for a complete list of items that the Service Master Key is used to protect in SQL Server 2008.

Answer 1

Look at the encryption hierarchy found in this TechNet article. This document from MS shows the entire hierarchy. You can see that the Master Key is created from the Windows DPAPI service and is used for:

-DB Master Key

-Certificates

-Symmetric Keys

-Asymmetric Keys

-TDE

-Transact SQL Encryption Functions

-Passwords (I'm not 100% on this, but it is in the technet article and it was mentioned in the comments so I added it)

Was this for a school project or a cert exam? Do you need any more background info?

Answer 2

Based on my, potentially flawed, understanding from Steve Jones's session The Encryption Primer the important concept is that we use keys to protect other keys and a lot of this is mathematical matryoshka dolls. The article linked by @Ali Razeghi has an excellent picture showing the layers. Slide 30 of Steve's presentation also has a pretty picture.

The Service Master Key, SMK, is created when SQL Server is installed according to MSDN. Steve's PowerPoint, slide 31, states it is created when first needed and explicit CREATE statement is required. At any rate, it is created per instance of SQL Server and encrypts the Database Master Key for the system database, master.

Slides 32 and 33 had these bullets

• Must be manually backed up. BACKUP SERVICE MASTER KEY
• Must be restored in a DR situation to open other keys secured by this key (Database Master Keys)
• Can be regenerated if necessary.
• This can cause data loss
• Encryption is now AES
• A restore or regenerate requires a decryption and re-encryption of all keys protected by this key – VERY RESOURCE INTENSIVE
• The FORCE option in restores bypasses errors.

I'm trying to recall his demo. I think he created a database master key in a catalog, which was secured with the service master key (again the whole layering thing). Then when he exported the key, it was secured so that only the service account would have access to it.

-- Create a Database Master Key
IF NOT EXISTS
(
    SELECT
        SK.*
    FROM
        sys.symmetric_keys AS SK
    WHERE
        SK.symmetric_key_id = 101
)
BEGIN
    -- http://msdn.microsoft.com/en-us/library/ms174382.aspx
   CREATE MASTER KEY
   ENCRYPTION BY PASSWORD = 'pass@word1';
END
GO

-- Encrypt the database master key (DMK) with the server master key (SMK)
-- http://msdn.microsoft.com/en-us/library/ms186937.aspx
ALTER MASTER KEY
ADD ENCRYPTION
BY SERVICE MASTER KEY;

GO

-- Export the databse master key so we can recover
--
-- From BOL
-- The master key must be open and, therefore, decrypted before it is
-- backed up. If it is encrypted with the service master key, the master
-- key does not have to be explicitly opened. But if the master key is
-- encrypted only with a password, it must be explicitly opened.
--
-- We recommend that you back up the master key as soon as it is
-- created, and store the backup in a secure, off-site location.

-- http://msdn.microsoft.com/en-us/library/ms174387.aspx

--OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pass@word1';
BACKUP MASTER KEY
    TO FILE = 'j:\tmp\exportedmasterkey'
    ENCRYPTION BY PASSWORD = 'ADifferentPass@word1';

GO

After running the above, if I navigate to my exported key file and examine the properties, you can see the Windows DPAPI having done it's thing (DPAPI protects SMK) and set the security on the file so that only the service account should have access to it.

At this point, I'm well out of my league but that's my very crude understanding of SMK.