Skip to content

Creating a Service Master Key in SQL Server

December 19, 2011

The basis for all the encryption in SQL Server is the Service Master Key, which is the top of the encryption hierarchy. You can see the image below from Books Online of how this is setup.

The service master key is automatically created when you create a key that needs it, usually a database master key. So there is no CREATE SERVICE MASTER KEY command.

This key is encrypted and protected by the Windows DPAPI and the linked to the SQL Server service account. Since it secures all other keys in the encryption hierarchy, if you regenerate it, or restore it, all other keys it protects must be decrypted and re-encrypted. That can be a resource intensive operation, so don’t do that lightly.

You can regenerate a service master key, and you do that with the ALTER SERVICE MASTER KEY command like this:

ALTER SERVICE MASTER KEY REGENERATE

That’s it. If it works, no errors, no results. If an error occurs, you’d need to deal with it. However this isn’t something you should run often, and if you encounter errors while doing this, I’d suggest you immediately stop, backup all databases and master keys, and then work through the issues.

In another post, I’ll talk a little about the alterations you can make to the Service Master Key and how to back it up or restore it.

About these ads

From → Blog

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,635 other followers

%d bloggers like this: