Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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

Comments

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

Loading comments...