Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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?

share|improve this answer
    
LOL - Not for a school project or cert, I actually want to know! That info is impossible to find written anywhere. And I've seen the diagrams before, but I have no idea how to read them. I assumed they were just used to show managers how complicated things are. –  ScottStonehouse Aug 20 '13 at 18:50
    
I thought I read somewhere that it might also be used to protect credentials, but I can't find a reference... –  ScottStonehouse Aug 20 '13 at 18:51
1  
And at technet.microsoft.com/en-us/library/ms187788.aspx you will read: "The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost." –  RLF Aug 20 '13 at 18:57
    
@ScottStonehouse According to the technet article, 'passwords' are included as protected but it looks like I didn't state it, I'll do so, thanks. I only asked if it was for a project since I could direct you to additional resources if you needed it. Let me know if you need more help. –  Ali Razeghi Aug 20 '13 at 19:17
1  
And another proof that data "encrypted" using ENCRYPTBYPASSPHRASE is not protected by the service master key, try this: 1. Create a new database, and store such a value in a table. 2. Back up the database and restore it on a different instance. 3. Use DECRYPTBYPASSPHRASE against that value. Voila! Decrypted data not protected by the service master key. –  Aaron Bertrand Aug 20 '13 at 22:30

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. enter image description here

enter image description here

enter image description here

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.