SQL Server 2005: A look at the master keys – part 2

This is a continuation of a previous post, in which I discussed the service master key (SMK) and the database master keys. I mentioned in that post that a new encryption will be added to the SMK and I will describe it in this article. Also, a few things have changed since I wrote my previous article, so I’ll provide updates here.

So, the SMK used to be encrypted through DPAPI using the service account credentials. Because this encryption could be invalidated by a service account change, a second encryption was added, which is done using DPAPI and the machine credentials – I will refer to this new encryption as the machine key encryption and to the original encryption as the service account encryption. So the SMK always has these two encryptions: the service account encryption and the machine key encryption. Each of these encryptions can become invalid in a specific scenario: a service account change will invalidate the service account encryption and a cluster failover will invalidate the machine key encryption. On server startup, these two encryptions are verified and if one is determined to be invalid, it gets recreated based on the other valid encryption. While having these two encryptions means that it’s extremely unlikely that the SMK could become undecryptable, I still want to reiterate the advice from my previous post about keeping a backup file of the current SMK at all times.

Because the SMK is verified during server startup, it will be created the first time the server will be started, which normally happens during setup time. In the unlikely event that both encryptions of the SMK are invalid, the server will make an errorlog entry with message 15466 – An error occurred during decryption. So the presence of such a message in the errorlog indicates that the SMK is undecryptable. While the message itself is generic, its presence in the errorlog can only happen because of this specific reason. If such a scenario would arise, the SMK can be restored from a backup using RESTORE SERVICE MASTER KEY with the FORCE option; the FORCE option will be necessary to bypass the decryption of the SMK – because in this case it cannot be performed.

The presence of the two encryptions and the way they get validated during server startup is the mechanism that enables transparent re-encryption of the SMK during service account changes; my previous observation that this is achieved through backup/restore of the SMK is outdated.

I mostly discussed the case of the SMK because this key is the most important key in the SQL Server key hierarchy and also because most of the statements that apply to the SMK also work similarly for the DbMK. One important difference between the SMK and the DbMK is that the DbMK can be encrypted using several passwords (also see the key encryption overview I posted recently). Furthermore, the SMK encryption of the DbMK can be dropped, so that the DbMK can only be used after it is explicitly opened using the OPEN MASTER KEY statement – the SMK encryption can be dropped with the ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY statement. The encryptions of the SMK cannot be dropped – they are managed by the server.

A DbMK must always have a password encryption – the last password encryption cannot be dropped for a DbMK. When moving databases from one server to another, the SMK encryption of the DbMK will have to be readded manually, if it is desired. For this purpose, the DbMK must first be opened using one of its password encryptions, then the ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY statement can be used to add the SMK encryption.

I hope you will find this information useful in managing the master keys and troubleshooting issues with them.

Comments (14)

  1. SteveJC says:

    Hi Laurentiu

    If we need to use the FORCE option when regenerating the SMK it mentions data loss.

    If after using the FORCE we alter the DbMK and drop and then add the encryption by the SMK, will this prevent the data loss?


  2. lcris says:

    I just wrote a new post on this topic:


    In the case of the DbMK, a loss could only occur if you don’t remember the DbMK password. Take a look at the above link and let me know if it doesn’t answer your questions.


  3. MelS says:

    Can you clarify the scenario in a cluster failover?  Are you saying that if the Service Master Key’s machine encryption is no longer valid because of a failover, any encryption that incorporates the SMK component in the DMK will fail?  Decrypt and encrypt no longer valid in that database while it is failed over?  Say it isn’t so…

  4. lcris says:

    It isn’t so. That’s why there are two encryptions of the SMK. If the machine key encryption is no longer valid, then the service account encryption will be used to access the SMK. Also, the machine key encryption will be restored if it was found to be invalid. So, it’s a self-healing mechanism. This is explained in the second paragraph of my original post.

  5. si.rapier says:

    I’ve got a couple of questions, hope they aren’t too simplistic.

    1. I understand that sql login passwords are hashed with SHA-1. Are they then encrypted via the service master key before being physically stored in the database file?

    2. If I’m running the sql service under the Network Service account and I restore my master database to another server do I need to restore the service master key if I run the sql service under the Network Service account on the new server? In other words, are the Network Service account credentials the same on different machines?

    I guess I’m most concerned about linked server passwords and other credentials rather than encrypted user database data, although cleary that would be affected in the same way.

  6. lcris says:

    1. For SQL logins, SQL Server only stores the SHA1 hashes, so no encryption is necessary. For linked server SQL logins, we need the actual passwords, so they are encrypted using the SMK. No SHA1 hashes are maintained for linked logins – there is no need because we have the passwords themselves.

    2. Yes, if the service account is Network Service, you should backup the SMK before moving the master database, and then you should restore it on the new server after copying the database. The only case when you don’t need to do this backup-restore of the SMK is if the service account on the source and destination server is the same domain account.

  7. Catherine says:

    We setup DR machine using disk Mirroring (e.g. EMC mirroring) from production to DR machine for the full databases disks. i.e. including the master, msdb & application databases
    If we use the SAME ‘DOMAIN SQL Server Services’ account in both Production and DR machine, when we fail over to the DR machine, the encrypted data should be readable. This means we do NOT need to explicity restoring the SMK backup from production to DR machine?
    If so, it will streamline the failover.

  8. lcris says:

    What does DR stand for? If the DR machine and the production machine have the same set of databases and if SQL Server runs under the same domain account, then you should not need to do anything for the SMK in the event of a failover – the DPAPI service account encryption of the SMK will be used to decrypt it. If you can’t make this work, please start a thread on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1 and we’ll look into it.


  9. tdubya says:

    Please let me know if this seems feasible or if it’s a totally screwy idea.  I have a SQL 2000 cluster with two nodes on which I need to be able to encrypt some sensitive data in one table.  We’re not quite ready to upgrade the whole system to SQL 2005.  I was thinking of installing SQL 2005 on the same cluster and letting it run in parallel with SQL 2000 and then moving just the one table that needs to be encrypted into the 2005 instance.  Would it be possible to do this and access the table in the 2005 instance using a DBLink and encrypt and decrypt the data in the process?  

  10. Catherine says:

    I have to appologize, the "DR" stands for "Disaster Recovery" machine.

  11. lcris says:

    Thanks for the explanation Catherine.

  12. lcris says:

    Re: tdubya’s post

    If I understand well, you want to move some data into SQL Server 2005, encrypt it there, and then access it from the SQL Server 2000 instance. The encryption should be no problem, you could automate it using triggers to do the encryption and a view for the decryption. But there may be issues if you have constraints that you need to maintain across the servers, for example. Basically, the encryption would not be a problem, but other aspects of accessing data on another server may be.

  13. bstichter says:

    lcris, A thought occured to me as I read this post and I wanted your thoughts.  It seems to me that in this era of virtualization the dual encryption of the SMK introduces a new problem.  No longer do sysadmins "just" backup the data (which they probably do as well), but now they backup the entire machine by taking a snapshot of the virtual.  Wouldn’t that mean that anyone who successfully obtains a backup of a virtual now have the machine credentials necessary to obtain the SMK?  In the pre-virtual world backups were safe with this sceme, but it seems they could now be compromised.  Am I right?

  14. lcris says:

    I don’t think the problem is introduced by the dual encryption – it is introduced by the ability to take the VM snapshot, which, depending on the extent of information that it captures, might expose sensitive information that is normally protected by the OS/applications. Loosing a snapshot would be similar (if not equivalent) to having the entire machine stolen.

    To decrypt the SMK using the machine credentials, you just need access to the machine and to the registry. You can probably get the registry contents from the snapshot and if you can restore the snapshot and logon to the system, then you can eventually decrypt the SMK (same path you would need to follow with a stolen machine).

    If you want to protect against a VM snapshot loss, the best solution is to cut the dependency on the SMK by having the root of your encryption key chain be protected by a password – even then you’re still running a risk if the snapshot could capture that password in memory (it should only be kept around while it is used for decryption, but if that’s when the snapshot occurs…).

    Note that if your data depends on the security of the SMK, then to protect it against the case of machine theft, you should also use a feature like Vista’s BitLocker, which will protect the DPAPI keys protecting the SMK. If we keep the comparison between machine loss and VM snapshot loss, then the equivalent of BitLocker in the latter case would be a snapshot encryption feature.

    Hope this helps. Also see http://blogs.msdn.com/lcris/archive/2005/12/20/about-security-and-encryption-with-references-to-sql-server-2005.aspx.