Creating a Service Master Key in SQL Server
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.