How to Create a Service Master Key in SQL Server

Introduction

The Service Master Key is the root of SQL Server's Encryption Hierarchy. As such, there can only be one service master key per SQL Server instance. The service master key is used to protect (encrypt) other keys, mainly the database master keys. It cannot be used directly to encrypt data. And – you can't create one yourself...

Creating the Service Master Key

Wait, did you not just say that you could not create the service master key yourself? Well, you can and you cannot. However, before we go there, let us look at what this mysterious thing is used for.

I already said that the service master key is used to protect database master keys. However, they can be protected with just a plain old password too. Another thing that actually requires the service master key is the storage of linked server login credentials. Their passwords are encrypted using the service master key. There is not a lot of documentation about this available, but I assume that actually any kind of credential is protected by the service master key.

So, how do you create one? You do not need to create on yourself. Any time SQL Server has to protect one of those entities, it automatically creates a new service master key, if none already exists. If you for some reason want to force its creation, just create a linked server login or a database master key.

What is the Service Master Key and How is it Protected?

The service master key itself is a symmetric encryption key. For SQL Server version 2005 to 2008 R2 it was used with the triple DES encryption algorithm. Since SQL Server 2012 new service master keys are AES keys. However, upgrading an existing SQL Server installation does not change the key.

The service master key itself is protected by the Windows data protection API, using the password of SQL Server's service account as a key. This fact has two important implications: First, your entire encryption hierarchy hinges on the strength of that very password. Make sure it is a very strong (and long) and truly random password. (You will never have to type it again after all.) Second, only the Windows account that was executing SQL Server at the time the key was created is able to access that key. If you change the service account, you will not be able to access the entire encryption hierarchy anymore. That potentially includes entire databases, if they are protected with TDE.

However, if you use the SQL Server Configuration Manager to change the service account, it will automatically take care of decrypting the service master key before the account change and re-encrypting it afterwards. This is another reason, why you always want to use that tool when changing SQL Server service level settings.

No Service Master Key Example

There is no direct way to see if the current instance already has a service master key. (See Update below.) Therefore, I cannot really show you a good example either. There are however telltale signs that we can use, for example the is_master_key_encrypted_by_server column in sys.databases:

A value of 1 in this column for any database means that its database master key is encrypted using the service master key, therefore the service master key must have been created before.

Summary

The service master key is the root of SQL Server's encryption hierarchy and potentially protects many other keys. The service master key itself is protected by the Windows data protection API and is bound to the service account executing SQL Server. It is created automatically when it is needed for the first time.

Update (2014-04-26)

There is a way after all to see if the service master key has been created yet. The service master key is a symmetric key stored in the master database. As such it can be found using the sys.symetric_keys catalog view in the that very database. You can for example use this query:

It will return a result set like the following:

Encryption Hierarchy Series

This article is part of the Encryption Hierarchy series. Check out The SQL Server Encryption Hierarchy for an overview of the hierarchy and a list of all posts that are part of the series.