Making a backup of your Service Master Key (SMK) should be one of the first things that you do if you are enabling encryption. This key encrypts and secures your other keys, and it will be needed when you have to rebuild the server.
Backing up the key is simple. You use the BACKUP SERVICE MASTER KEY command and it only has two arguments
- File – The path and name of the file for the backup.
- Password – provides security for the backup file. This is needed when restoring the file.
That’s it. Performing a backup is as simple as running a command like this:
-- Backup the SMK BACKUP SERVICE MASTER KEY TO FILE = 'c:\sqlbackup\MainServiceMaster.key' ENCRYPTION BY PASSWORD = 'S3cureP@ssword!sneeded'
Securing this file, however, is a little harder, and managing it is really hard. Ideally you do not want this key stored with the backup files for the server, because you don’t want anyone to have this and the data. However if you must do that, and that might be the practical thing to do so that it’s available when you use this tape/disk to recover the server, I recommend you do not keep the password with this file. Store it in an admin system somewhere else, preferably a protected system with something like KeePass or Password Safe.
A couple things to note. Just like with a backup, the service account needs write permissions on the path (local or UNC) to create the file. This command requires the CONTROL SERVER permission, so not anyone can use it.
Filed under: Blog Tagged: encryption, security, sql server, syndicated