SQL Server 2005: How to recover when the service master key (SMK) is not accessible

I wrote earlier today a reply on this topic on the public forums, but now that I checked, the reply appears to have got lost, although I still entertain the hope it may only have got delayed and will appear there in 24 hours. Anyway, this is the reason why I prefer to write longer posts on this blog rather than on forums – they don’t get lost as easily, either due to forum bugs or to threads going old and forgotten.

So, the problem I want to discuss is about what can be done if the SMK, for some reason or another, becomes inaccessible. I had previously touched on this in a previous article, but this time I want to go in more details.

First, let’s look into why such a thing could happen. For the SMK to become inaccessible, something out of the ordinary must happen – either a disk corruption, which would most likely impact more data than just the SMK, or a change in the system configuration that would invalidate the DPAPI encryption of the SMK, or some other problem that I don’t know yet about – maybe some obscure software error we have yet to see. In the years since SQL Server 2005 has shipped, I have seen only one case where the SMK became inaccessible and the reason for that was that the master database had been restored on a different system, such that the DPAPI encryptions of the SMK were invalid – even this issue had happened in an internal testing environment and not in a real production scenario. So, there is a way you can realistically end up with an inaccessible SMK, but it is not an ordinary scenario.

The following discussion assumes that something happened to the SMK and just to the SMK – I don’t make any assumption on what happened, I just assume that this SMK problem is not compounded by the existence of other problems affecting other areas than the SMK. Also, before launching yourself into a recovery operation, make sure that you can at least recover from that – you should backup your database files such that you don’t get in a worse situation than when you started.

Let’s review the encryption hierarchy around the SMK, because this helps us understand what we need to fix. On one hand, the SMK has two DPAPI encryptions: using the service account and the machine account credentials, so for the SMK to become inaccessible, the system must be incapable of decrypting both these encryptions (also see this article). On the other hand, the SMK is used for encrypting three different classes of entities: credentials, linked server passwords, and database master keys (also see this article). This information allows us to determine what must have happened for the SMK to become inaccessible – both DPAPI encryptions must be undecryptable, and what is the effect of the SMK becoming inaccessible – credentials, linked server passwords, and database master keys become undecryptable by the system. (If you’re reading this article while working with future versions of SQL Server, keep in mind that the list of entities encrypted by the SMK may change.) We can also use this information to verify that the SMK is indeed inaccessible; to do this, we could attempt to use the SMK to encrypt a new entity, such as a new database master key (in a database created just for the purpose of this test) or a credential secret – if such operations fail, a final test can be made by attempting to regenerate the SMK – if this fails as well, it becomes pretty clear that the problem we’re dealing with is related to the SMK.

So, the best fix for the problem would be to fix the DPAPI encryptions of the SMK. We can do this in two ways:

(A) If we have a backup of the SMK (this is why such backups are recommended), then we can restore that backup. We will need to use the FORCE option because the current SMK cannot be decrypted. This should fix our SMK and we can check this using the tests I mentioned above.

(B) If we don’t have a backup of the SMK and if the issue happened because we moved the databases from a machine to another and if that machine is still available, then all is not lost. We can either make a backup of the SMK on the original machine (if it still has the database system on it) and then apply it according to the (A) solution, or we can copy the database files back to the original machine, to make such a backup. Either way, the purpose would be that we want to make a backup of the SMK and then restore it on the machine that has the SMK problem.

If neither of these apply – we don’t have a backup of the SMK and there is no original system where we can make one, then we’re in some trouble. Not a lot, but we’ll have some non-trivial cleanup to do. So let’s assess the situation in this case: we don’t have a SMK backup and we can’t access the SMK, so let’s face it: we’ve lost the SMK; this means we’ve lost the credentials and linked server passwords encrypted by the SMK – we’ll need to regenerate all of these with help from whoever created them. The good part is that we didn’t loose the database master keys – unless we managed to also forget the passwords protecting them – that would be quite unfortunate. Because DbMKs are always encrypted by a password, besides a default SMK encryption, the latter can always be fixed as long as we know the DbMK password. So, let’s enumerate the steps we need to follow to cleanup the situation:

(a) We need to regenerate the SMK, because the current one is now unrecoverable. So we need to use REGENERATE and the FORCE option to create a new and valid SMK.

(b) For each DbMK that had a SMK encryption, we need to open the DbMK using its password encryption and then we need to re-encrypt the DbMK using the SMK. This is the same kind of step that we would need to do when moving a database from one system to another. Here is the TSQL for it:

(c) We need now to recreate all credentials and linked server passwords.

The steps above should bring our database system back to normal operational state as far as the SMK is concerned. It’s going to be painful to do (c), but that’s the price to pay because we didn’t have a SMK backup. (b) and (c) need to be done after (a) because they need a valid SMK, but their order can be swapped, i.e. we can do (c) before (b).

Thanks for the details, however i was wondering abour scenarios as to why would we need to restore SMK/ DMK, at all. have seen some scenario defined by yourself above,

I would have thought that this was required just o avoid data loss, in a scenario if a SMK was lost/corrupted, why dont we just restart the server and let it create a new SMK and DMK for us and then as detailed in one of your other post if we have the

SMK/DbMK restore syntax is meant to help in key corruption scenarios. Creating a new SMK/DbMK when the current copy is corrupted means that we cannot decrypt the data already encrypted with the old copy, so that data is effectively lost – that is bad. So the proper way for recovery is to restore such key from a backup.

Note that the DDL for the SMK/DbMK does not have the creation options available for generic symmetric keys, so there is no way to regenerate the same SMK/DbMK – there isn't even any CREATE SMK syntax – the SMK is created automatically by the server.

For the DbMK, assuming the database is still attached to the original server and you have a valid SMK encryption, you can just add another password encryption. If you do not have the SMK encryption and you do not know the password, then the only choice is to restore the key from a backup for which you know the password.

I am not sure what you are referring to when you use the term "credential". There are objects in SQL Server that are called credentials and are created with the CREATE CREDENTIAL statement (technet.microsoft.com/…/ms189522.aspx). As BOL states, the secret piece of a credential object is encrypted by the SMK.

I inherited a SQL Server 2008 R2 that was migrated from one physical server to a VM. The SMK was not backed up and the old server was disposed. This mean the SMK is lost. The current server does encrypt correctly with the migrated MK, but decryption returns NULL. I was left with the MK password, but when I use the command:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

It returns:

Msg 15151, Level 16, State 1, Line 1

Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

I'm assuming the password is incorrect, but this was the password given. Would you happen to know why it returns the issue? Is there any other way I could proceed to recover the encrypted data? Please let me know. Thanks.

First, let me say that I have not worked with SQL Server recently so at this point I can only rely on memory and quick reference checks.

I imagine that if the password is correct, then the problem is one of permissions, as the message indicates: you need CONTROL permission on the database. Check that you are executing the command as a database administrator or as someone granted CONTROL permission on the database.

Alternatively, it might be the case that a database master key was never created in that database.

If you still cannot figure out the issue, try asking the question on the SQL Server security forums: