Make sure you back up the Service Master Key
6April 8, 2015 by Kenneth Fisher
I’m by no means an expert in SQL Server encryption. What I do know however, is that the Service Master Key is the top of the encryption chain on an instance. This means that any certificate or key will be encrypted using, in part, the Service Master Key. So if you are using encryption then you absolutely need to backup this key for DR purposes.
Backing up the SMK (Service Master Key) is pretty simple using the BACKUP SERVICE MASTER KEY command:
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'
Now you may be thinking “But I’m not using encryption, why should I worry about this?” Well you may be using encryption and not realize it. Setting aside the possibility of someone setting up encryption and not telling you (don’t laugh, I’m betting it’s happened to at least one person reading this) did you know that passwords used in a linked server are encrypted? This means that if you have a disaster and you are using linked servers you had best hope that either the SMK is backed up, or you have all of the passwords used in the linked servers. Even if they are a decade old. (Nope, hasn’t happened to me. Not at all.)
Basically I like to follow the golden rule. No not that one, the other one. “Better safe than sorry.” Back it up, store it with your regular backups (making sure you have the password stored somewhere securely in separate location). Also remember that the SMK can change (for example when you change service accounts) so make sure to take these backups on a regular basis also. That way if you actually need it, you have it.
P.S. The restore command for the SMK is RESTORE SERVICE MASTER KEY.
SQL Studies 
Agreed. Always back up the service master key.
To what you say about it being used for encryption, in a typical use case, yes, the service master key is important. SQL Server uses it to decrypt the database master key, which then in turn is used to decrypt the certificate or asymmetric key, which is then used to decrypt the symmetric key. There’s your keychain.
However, you can break it at any level. Typically we see the break when we restore a database to a new server. That’s why knowing the database master key is important. You have to OPEN MASTER KEY with the password to get access to it. SQL Server won’t have it by default. If you want to restore the keychain and SQL Server being able to automatically open the database master key, you have to then ALTER MASTER KEY and then ADD ENCRYPTION BY SERVICE MASTER KEY. That re-establishes the keychain.
I did say you could break the chain at any level. This is easy. When you are creating the crypto object, you can specify encryption by password. If you do this, then there is no keychain above the object. And that means neither the database master key nor the service master key comes into play with that object unless you later alter the object to add encryption by something in the keychain.
Great points all and thanks for mentioning them. I was assuming (and we all know what that does) that if you were actually using encryption you would realize that your keys (all levels) need to be backed up. I was more going for the case where you don’t realize you are using encryption, for example the passwords in linked servers. I probably should have gone somewhat broader though.
[…] Make sure you back up the Service Master Key Re-Inventing the Recursive CTE – Added to Curah Exam 70-461 Work with data (27%) What is a CTE – Added to Curah Exam 70-461 Work with data (27%) CTEs, Inline Views, and What They Do – Added to Curah Exam 70-461 Work with data (27%) QS-Config v2.0 coming soon User Account Control and Admin Approval Mode – The impact on PowerShell SQL SERVER – The Basics of the File System Task – Part 2 – Notes from the Field #075 – Added to Curah SQL Server Integration Services Dealing with high severity errors in SQL Server Interview Questions for Hiring PowerShell Database Developers Cardinality Estimator – What’s new in SQL Server 2014 […]
Doesn’t the master database contain the service master key? And if so then restoring a backup of the master db should bring across the SMK?
If I understand correctly, and I’ll admit I haven’t look at it recently so I could be wrong, but part of the service master key is something coming from the server itself. That means that, if you restore to a new server, yes, the SMK is brought across, it’s still not going to work.
The SMK is the starting key of the key chain. It’s first generated when you install SQL Server. If you move to a new machine, you do have to handle it properly. Instruction here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql