SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backing up the Service Master Key

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
 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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...