Where SQL stores database master key encrypted by service master key?

Question

Hi,

can anybody confirm/clear the statement on the BOL (http://technet.microsoft.com/en-us/library/bb964742.aspx)

When database master key created then : "When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the SMK. It is stored in both the database where it is used and in the master system database"

This is my understanding:

We store Database master key in 3 (three) locations.

1. Master database encrypted with SMK.
2. In the actual database encrypted with SMK.
3. In the actual database encrypted with password.

Thank you.

Roman

Answer 1

Hi Roman Troshkov,

A service master key (SMK) is generated on and for a SQL Server instance, and a database master key (DMK) is used for a database i.e. they are unique to a service/database.

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database, once you created SMK, we suggest you to back up the Service Master Key and store the backed up copy in a secure, off-site location.

Without SMK and DMK, a server/database cannot encrypt/decrypt data using Symmetric Keys so if you haven't backed them up and your server/database crashes, you will lose all your data.

 

---

Regards, Amber zhang

Answer 2

Hi Amber,

Thanks for looking in to my question.

The part you referencing is pretty clear. I am actually having difficulties understanding of where does SQL store encrypted Database Master Key.

The document for which I have link in my initial mail does not state that clear. I was looking for this question in multiple sources but still not able to get straight answer.

 

Here is an actual problem (I should probably have started with it in a first place).

We use SQL cell level encryption in database. So, we have a chain of keys: DB Master, Asymmetric, Symmetric . Symmetric one is used for a data encryption.

We have database configured with SQL mirroring.

The problem is that when we failover database to the partner(mirror) server, we are not able to open Asymmetric key. This can be fixed by opening DBMK copy encrypted by a password end enable encryption with service master key (statement: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY).

Problem repeats if we flip database to the original position. This time principal server gets a problem until we do same trick (open database master key using a password and enable encryption with service master key).

We actually have found a workaround to this and now we store credentials on both SQL servers using sp_control_dbmasterkey_password.

Now back to my question…

From what I see and experience it looks like statement  “ADD ENCRYPTION BY SERVICE MASTER KEY” actually stores encrypted DBMK in the database itself. That is why it gets overwritten when we execute the statement again on the mirror server. And that is why we are not able to use that statement if we have a read-only copy of database.

On the other side, the document which I am referencing to (and some other I have found on the net) is telling that Database Master Key encrypted by Service Master Key is stored in the Master database.

Wondering if it is stored in both (master DB and actual DB) for some redundancy or else. Any way we may see that for sure?

Thanks,
Roman