I am looking for a complete list of items that the Service Master Key is used to protect in SQL Server 2008.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
-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?
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
Slides 32 and 33 had these bullets
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.
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.