SQL Server 2005: what to do when a decryption error occurs while regenerating or reloading a master key


Decryption errors during the regeneration of a master key are highly unlikely. I’ve never seen one of these occurring naturally so far (we caused these errors manually for testing), so a discussion on their topic has a slim chance of being useful in practice. However, I also know this topic isn’t covered anywhere else in the detail I’ll cover it here, and while writing an answer to a question about it, I realized I was actually writing an entire topic, not just an answer. So I’ve decided to write my answer as a new post that can serve as reference in case anyone will ever need this information or is just curious about the details.


I’ll discuss each master key separately.


Service Master Key


The entitites encrypted by the SMK are credential secrets, linked server login passwords, and DbMKs. DbMKs always have an additional encryption by a password, so, unless that password is forgotten, the key cannot be lost – even if the SMK encryption is corrupted. For the other two entities, if the SMK is changed and errors occur while attempting to decrypt them with the current key, then if FORCE is specified, the errors will be ignored and a new key will be regenerated anyway; this new key will naturally not be able to access the entities for which errors were encountered earlier, so they are likely lost – this is the reason why the error messages mention the possibility of data loss.


The FORCE option is an option for unblocking the regeneration or reload of the SMK and for ignoring any decryption errors that occur during the process. Without FORCE, decryption errors will abort the LOAD or ALTER REGENERATE operations. With FORCE, decryption errors are ignored and the processing of the entities for which the error was hit is skipped (no attempt will be made to reencrypt them with the new key because they can’t be decrypted using the current key). FORCE is a last-resort option. Do not use it for your initial attempts to regenerate or load a master key!!! Don’t panic – try to identify the cause of the decryption errors.


There are three possible errors that can occur when decrypting an entity with the SMK:


(1) the entity may be corrupted, so it cannot be decrypted by any key
(2) the SMK may be corrupted, so it cannot decrypt the entity
(3) the SMK may not be the correct key


If you suspect (1), the corrective action should be to fix the entity. For a credential secret or linked server login password, try resetting the information. For a DbMK, reload the DbMK from a backup. Barring other errors, this should fix the issue, so you can perform the operation without resorting to FORCE 🙂


If (2) is the case, which you could verify by attempting to encrypt with the SMK, for example, by generating a DbMK in a new test database (a failure should occur while encrypting the key if the SMK is corrupted), then the only solution is to restore the SMK from a backup. For this scenario, you need to use the FORCE parameter, because the current key cannot decrypt anything, so you cannot proceed otherwise.


It might not be immediately obvious how you could have (3), but this can be reached if while attempting to resolve (2), you load the wrong SMK backup. In this case, the key would be valid (i.e., not corrupted), but it would not match any of the encryptions. The solution for this scenario is again to load the proper backup and the FORCE option needs to be used here as well.


Database Master Key


The DbMK encrypts certificate and asymmetric key private keys. These do not have other encryptions, so if there are issues with their DbMK encryption, they are unusable.


The same three classes of errors can occur for the DbMK as for the SMK, and the way to deal with these errors is similar. For (1), the private key should be restored from a backup. For the asymmetric keys, if the key was generated in SQL Server, the only solution is to restore the entire database from a backup. For (2), one quick way to verify whether the DbMK is valid or not is to create a new test certificate (a failure should occur while encrypting the private key if the DbMK is corrupted). (3) is the same as for SMK.


One important thing to note from this discussion is the importance of backups. The solutions rely on having backups available that allow the administrator to restore the keys to their original state. Without backups, corruption will lead to data loss.


As a final advice, you should create a backup of the entire system before attempting to fix the issues, to ensure that you can always revert your actions.

Comments (12)

  1. Steve J C says:

    Hi Laurentiu

    This is great thanks, although I still haven’t found the asnwer to my question.

    If the SMK was corrupt and a backup did not exist, you can regenerate the SMK. Will you be able to alter the DbMK to drop and then re-add the SMK encryption as a method to resolve that.

    Cheers

  2. Yes, for your scenario (SMK corrupt with no backup), you can regenerate the SMK, and then you can just readd the SMK encryption by opening the DbMK using its password and then by adding the SMK encryption again. This will restore the state for the DbMK, but for credential secrets and linked login passwords, there is no reparation other than resettting those entities.

    Thanks

  3. CrispyRice says:

    Hi, I’m getting this problem:

    [298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]

    when trying to create a  replication publication, i though it might be due to the SMK, i have done everything suggest including FORCE REGENERATION, but still get the message.
    Have you any idea?

    Its a SQL 2005 Cluster

    Thanks,

    Chris

  4. You should ask this question on the replication forum at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=90&SiteID=1.

    This is most likely an issue related to encryption done in replication. I don’t think it has anything to do with the SMK or with the encryption features exposed in SQL Server 2005.

  5. grundt says:

    Scenario:

    New hardware: replacing production db server (A) with new server (C).

    Log shipping between server A (primary) and server B (secondary).

    A and C are at the same data center.

    B is at a different data center, far far away.

    Plan:

    Install SQL Server on C;

    Create tail log (backup log with no recovery) on A;

    Failover from A to B;

    Configure B as primary log shipping server;

    Restore database with standby on A;

    Copy db files (system & application) from A to C;

    Change startup param on C to point to copied system db files;

    Configure C as secondary ( B to C );

    Failover from B to C;

    The system db’s are being copied from A to C because it seems to be the only way to preserve the “STANDBY..” state on the application db.  This allows log shipping from B to C without having to restore a backup from B to C.

    After copying the system db’s from A to C; changing the startup param on C to point to those files; and starting SQL Server … I get the  “15466: An error ocurred during decryption” error.

    Backing up the Service Master Key on A, and restoring it on C fails with “15329: The current master key cannot be decrypted.”

    Q: is there some procedure that would allow me to end up with a good Service Master Key on C, without having to use the force option ?

  6. Are your service accounts on A and C different? If they are the same, you should not see this error. I expect to see such errors if the service accounts are different.

    FORCE loading the SMK should also work – it won’t be able to decrypt anything, so it will just load the SMK and re-encrypt it such that you can decrypt it; if you loaded the right SMK, then it will now decrypt whatever used to be encrypted with it. Of course, don’t throw away your database backup before this operation.

    So, if you can setup the service account to be the same on C as it was on A, you should be able to start the database normally, and then you could change the service account to something else, if you wish – but you must start the database under the same account as on the source server. Otherwise, you’ll have to use the FORCE option to reload the SMK.

  7. grundt says:

    During my experiment (see prior post), I ran these queries on server C:

    SELECT * FROM sys.symmetric_keys

    <results removed>

    SELECT * FROM sys.key_encryptions

    <results removed>

  8. I saved the results you posted, but I cut them before publishing your comment, because they were taking too much space. I also noticed that the crypt_property values from your second query are truncated.

    What is your question?

  9. grundt says:

    Laurentiu …

    I appreciate your responses and apologize if you’ve received this already.  I posted the message twice yesterday, but haven’t seen it appear.  Here goes again …

    The services had both been running under the LOCALSYSTEM account.

    Just to experiment, I created a user account on both servers, with the same name, and switched both services to use those accounts.

    The results were the same … I still wound up getting the “15329 The current master key cannot be decrypted” error.

  10. I moderate all comments, so they don’t appear immediately. And I check my blog for comments about once a day. I also received this last comment only once.

    Coming back to your problem – by same service account, I meant having the same domain user – both the domain and the user part have to be the same. Two local users are not the same, even if they have the same name.

    If you see this error when the service account on both servers is the same domain account, then let me know – that would probably indicate some bug – I don’t expect an error for this scenario. This is the only scenario that allows you to move the databases without having to force reload the SMK.

  11. grundt says:

    Thanks for that clarification.

    My IT person tells me we have no DOMAIN ( our servers are stand-alone ).

    So it sounds like using FORCE is my only option.

  12. FORCE will work, as discussed, or you could have the machines added to a domain, at least while you’re working on this machine replacement – you could then remove them after the replacement is completed.

Skip to main content