Timeline for What does the Service Master Key protect?
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 21, 2013 at 2:02 | history | edited | Ali Razeghi - AWS | CC BY-SA 3.0 |
added 2 characters in body
|
| Aug 21, 2013 at 1:25 | comment | added | Aaron Bertrand | Posted a Q & A to clarify this. | |
| Aug 20, 2013 at 23:44 | history | edited | Ali Razeghi - AWS | CC BY-SA 3.0 |
removed a entry that Aaron B showed was inaccurate in the MS TechNet paper
|
| Aug 20, 2013 at 22:30 | comment | added | Aaron Bertrand |
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.
|
|
| Aug 20, 2013 at 22:16 | comment | added | Aaron Bertrand |
Well ENCRYPTBYPASSPHRASE is non-deterministic (it uses 3DES with a phrase and string as input, similar to HASHBYTES) and I don't think any key is required. At least I have no problems running ENCRYPT/DECRYPT on all of my VMs, even where no certificates or symmetric/asymmetric keys exist.
|
|
| Aug 20, 2013 at 20:51 | comment | added | Ali Razeghi - AWS | Seems like the Technet article from MS needs to be updated or clarified then. How do you think MS SQL Server will actually encrypt/decrypt your value though if not for the Master key? I'm not familiar with any other way we could do the encryption in SQL Server. | |
| Aug 20, 2013 at 19:26 | comment | added | Ali Razeghi - AWS | I didn't see anything in depth explaining why it needs the master key to do so, but I suspect it has to do with SQL Server internals. The data needs to be encrypted by the SQL Server Engine (not another service, such as a website) and then decrypted without using certs in this example. Since it's not using a cert and key to encrypt it, then the master key would be the next candidate up the chain to encrypt it while still having it done in SQL Server. I didn't see anything in the documentation regarding as to WHY though, this is just my educated guess. | |
| Aug 20, 2013 at 19:21 | comment | added | ScottStonehouse | Seems odd to me that the Service Master Key would be required for ENCRYPTBYPASSPHRASE. Is there anything in the documentation that explains this? | |
| Aug 20, 2013 at 19:18 | history | edited | Ali Razeghi - AWS | CC BY-SA 3.0 |
added 122 characters in body
|
| Aug 20, 2013 at 19:17 | comment | added | Ali Razeghi - AWS | @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. | |
| Aug 20, 2013 at 18:57 | comment | added | RLF | 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." | |
| Aug 20, 2013 at 18:51 | comment | added | ScottStonehouse | I thought I read somewhere that it might also be used to protect credentials, but I can't find a reference... | |
| Aug 20, 2013 at 18:50 | comment | added | ScottStonehouse | 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. | |
| Aug 20, 2013 at 18:45 | history | answered | Ali Razeghi - AWS | CC BY-SA 3.0 |