SQL Server 2005: A look at the master keys

I'd like to go into this post over the service and database master keys and explain the basics of how these are used in SQL Server 2005.

Note: credentials and asymmetric keys are new types of entities in SQL Server 2005 and, unless qualified otherwise, they are not used here in their everyday sense.

Service Master Key (SMK)

This key is used to encrypt all database master keys as well as all server-level secrets such as credential secrets or linked server login passwords. The key itself is a 128bit 3DES key. The 3DES algorithm was chosen because of its availability on all Windows platforms supported by SQL Server 2005. The SMK is encrypted using DPAPI and the service account credentials. A second encryption of the SMK will be added in future builds; I'll add information about it in another post.

Database Master Keys (DMK)

There can be only one such key per each database. Like the SMK, each DMK is a 128bit 3DES key. The DMKs are encrypted using a password and by default an additional encryption by the SMK is also made. A DMK is used to protect database level secrets such as the private keys of certificates or asymmetric keys. The purpose of the encryption by the SMK is to allow the server to be able to internally decrypt the DMK without requiring a password from the user. If this is not desired (because every sysadmin would have access to the DMK), the SMK encryption can be removed, but then the DMK can only be opened by specifying the password that was used to encrypt it.

Unlike the DMKs, which must be explicitly created and can be dropped when they are no longer required, the SMK is created the first time it is needed (currently, this happens when the server is started for the first time) and it can never be dropped. Hence, there is no CREATE or DROP DDL for it, only ALTER.

Because the SMK is the key that by default encrypts the database master keys, which in turn by default encrypt the other keys, it becomes clear that this is one of the most important pieces of information in the server. I strongly recommend keeping a backup of the SMK in a safe place at all times. This will be invaluable in the unlikely case that the SMK gets corrupted. The SMK can be backed up and restored using the BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY DDL. A password will be required to encrypt the SMK before it is written to the backup file; the password strength will be enforced on Windows platforms that provide the password policy API.

I will focus now mainly on the SMK. The DDL that manages the DMKs is very similar to the DDL for the SMK, so there is not much to say about DMKs that can not be inferred from how the SMK is managed.

So, for the SMK, I'd like to cover the following 3 topics: how to regenerate a SMK, the meaning of the FORCE options for the REGENERATE and RESTORE operations, and the impact of a service account change.

(a) SMK regeneration

If you have reason to believe your current SMK might have been compromised or if you would just like to change the SMK regularly as part of your security policy, you can regenerate the SMK. This can be done using the statement:


This statement will generate a random new SMK, will decrypt the data encrypted using the current SMK, and will re-encrypt it using the newly generated SMK. This statement will either succeed in generating a new SMK or it will fail with an error without changing any data.

(b) FORCE option

Let me give first some information about how the RESTORE SERVICE MASTER KEY statement works: a restore will basically be similar to a regeneration, except the new key is not randomly generated, but it is loaded from a file created using the BACKUP SERVICE MASTER KEY statement. This means that the sequence of steps is similar to the one described at (a): the RESTORE statement will read the new SMK from the file, will decrypt the data encrypted using the current SMK, and will re-encrypt it using the newly read SMK. So these operations are fairly similar, the only difference between them being the source of the new SMK.

The FORCE option helps deal with errors that occur during the decryption of the data that was encrypted by the current SMK. The FORCE option allows the statement to proceed by ignoring these errors. This can be useful in the case when the current SMK is corrupted and we want to reload it from a backup or in the case when the data encrypted by the SMK has become corrupted and we're unable to restore it. The data that cannot be decrypted with the current SMK will remain unchanged if the FORCE option is used.

The possibility that data might become corrupted is minimal, so I expect this option to be rarely used, but knowing what it does should be valuable if you ever need to use it.

(c) Service account change

Because the SMK is protected using DPAPI and the service account credentials, a service account change needs to take care about re-encrypting the SMK using the credentials of the new service account. For this reason, the service account change should never be done manually. Unlike the previous two topics, which are also relevant for the DMKs, this one is specific to the SMK.

In the past few months, the problem of a manual service account change that left the SMK undecryptable was the problem that we encountered most often related to the encryption feature. By RTM, this problem should become rare both because of adding the support for service account changes (which was unsupported until recent builds) and because of adding a new SMK encryption to supplement the encryption that uses the service account credentials. The symptom of this error is the "An error occurred during decryption" message. If you see this error message and want to check whether the SMK can be decrypted, you can attempt to regenerate the SMK and see if an error occurs while decrypting it.

To fix a SMK that was left undecryptable after a service account change, there are a couple of methods. Note that these will only fix the SMK encryption; a service account change that was done manually can break more things than just the SMK encryption; these instructions will only address the SMK restoration problem.

First and simplest solution is to restore the SMK from a backup if one is available. Hence, I'm reiterating my advice on backing up the SMK and storing the backup in a safe place. You should always have a backup of the current SMK.

Second solution, which may not work in all cases is to try and decrypt the SMK using the credentials of the old service account. This can be done using the following DDL:

ALTER SERVICE MASTER KEY WITH OLD_ACCOUNT = 'old_service_account', OLD_PASSWORD = 'old_service_account_password'

This solution will not work if the old service account was Network Service or Local System or Local Service, because no password can be specified for those accounts.

There is also a flavor of this syntax that can encrypt the SMK using the new service account credentials BEFORE the service account is changed. This variant might be useful in some troubleshooting scenarios but I find it unlikely to be used in a normal scenario.

Historically, the syntax from this second solution was intended to be used for the service account change, but this is now accomplished by backing up the SMK before the account change and restoring it after.

The complete syntax of the statements mentioned above is described in Books Online.

UPDATE: Make sure to also read Part 2 of this article, which contains several important updates.

Comments (4)

  1. kt says:

    Thanks for the info.

    I have a question: what should  I do if I need to restore a database , that has encrypted data, to a different server?  The database can have millions of rows of  encrypted data.

  2. See the answer in the second part of this article: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx

    Basically, you only need to restore the SMK encryption of the DbMK on the new server, if such encryption existed on the original server. To do this, on the new server you need to issue the following statements:



  3. jimbobmcgee says:

    Apologies for posting this so late after the article date, but I’m having a problem with my SMK and you seem like the best person to ask.  

    When I try to issue a CREATE CREDENTIAL call, I am told ‘An error occurred during decryption’

    I have tried to issue an ALTER SERVICE MASTER KEY FORCE REGENERATE, as per your suggestions elsewhere, but am told ‘An error occurred during encryption’.

    As per another suggestion, I have checked the permissions on the Protect folder in the App Data of SQL Server’s service account (a domain account, that I used SQL Server Configuration Manager’ to configure) and it seems fine.

    Are you able to help?



  4. Have a look at this more recent article and see if it helps you: http://blogs.msdn.com/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the-service-master-key-smk-is-not-accessible.aspx.

    If not, then please post this issue in the Security forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1) and we’ll continue the discussion there.

Skip to main content