Protecting the SQL Server 2005 'Service Master Key', the root of all encryption

  • Article

Microsoft SQL Server 2005 introduces a new feature set, known internally as 'Secret Storage'.  Secret Storage is the overall umbrella technology for the data encryption facilities in the SQL Server database engine, including the key management story.  The key management of a SQL Server 2005 instance is rooted in the Service Master Key (SMK), a 128 bit 3DES key which is encrypted using the DPAPI and the service account credentials.  All other keys are protected in someway by the Service Master Key.  There is a diagram here of the key management hierarchy, and an excellent post with more details form Laurentiu here.

There is also a second encryption of the Service Master Key encrypted using the 'machine key'.  Both encryptions can be viewed using the sys.key_encryptions view in the 'master' database:

SELECT

* FROM sys.key_encryptions WHERE key_id = 102

key_id thumbprint crypt_type crypt_type_desc crypt_property
102 0x01 ESKM ENCRYPTION BY MASTER KEY 0xFFFFFFFFEC00000...
102 0x03 ESKM ENCRYPTION BY MASTER KEY 0xFFFFFFFFEC00000...

The key internally recognized with the 0x01 thumbprint is an encryption of the Service Master Key using the (SQL Engine) service account credentials.  The key internally recognized with the 0x03 thumbprint is an encryption of the Service Master Key using the machine key credentials.

The Service Master Key is loaded into memory at database start up time.  The Service Master Key is first decrypted using the 0x01 key and service account credentials, if that fails, it is decrypted using the 0x03 key and local machine key credentials, and in this failure case, we then replace the original encryption of the Service Master Key using the service account credentials with a new encryption using the new service account credentials.

The second encryption of the Service Master Key is a recent addition because service account changes (of the SQL Engine service) might have led to loss of data.  Originally (and this is the behavior in CTP16 and previous) we were attempting to de-crypt and re-encrypt the SMK when the service account was changed using the SQL Configuration Manager.  However, there were occasions when the user could be surprised:

  • If a user uses the Windows Services Control Manager (SCM) to change the service account (instead of SQL Configuration Manager), the decryption and re-encryption would not happen (Windows SCM does not have any hooks that would allow us to do this), and if the old account was deleted before realizing this, access to the Service Master Key could be lost forever (along with all the data it encrypted).
  • If a Local User is used as the service account, and the password for that account is reset, access to the Service Master Key is lost (again, along with all the data it encrypted).
  • The code we had that decrypted and re-encrypted the SMK during Service Account change (using SQL Configuration Manager) might fail, and because Service Account change is not an atomic action (there is no rollback functionality) we could leave the SQL instance in a state where it has a new service account, but the Service Master Key was still encrypted using the old service account credentials.  Again, leading to loss of access to everything the SMK is protecting.

To avoid these situations we added the second encryption of the Service Master Key using the machine key.  Specifically, the machine key encryption uses DPAPI and passes the flag CRYPTPROTECT_LOCAL_MACHINE. It also uses 256 bytes of 'Entropy' (randomly generated data), which is stored in the registry under:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security\Entropy

This registry key is ACL'ed so only Box Administrators and the SQLServer2005MSSQLUser$<ServerName>$<InstanceName> group have access.  The Entropy, while not increasing the overall strength of the encryption, does increase the difficulty for another application, running under the same service account as the SQL Server service, to compromise the encryption key.

(NOTE: In SQL Express, when using a User Instance, the Entropy is stored in a file call Entropy.bin, in the user Local Settings\Application Data\... directory).

We have only just checked this feature in, it will be available in the RTM bits, however it did not make it into the RTM Books Online, (however, everything happens seamlessly in the background, so there are no configuration options to worry about).

We take protection of the Service Master Key very seriously, and while we have done our best to make sure you never get into a situation where you cannot decrypt it, we also strongly encourage you to backup the SMK using the 'BACKUP SERVICE MASTER KEY' DDL.