SQL Server 2008 includes the following T-SQL statements to alter, back up, and drop SMKs:

• ALTER SERVICE MASTER KEY: Allows you to change or regenerate the SMK. This statement can be used to change the SMK and to automatically decrypt and re-encrypt the entire encryption key hierarchy.

• BACKUP SERVICE MASTER KEY: Backs up your SMK to a file. The SMK is encrypted before backup and stored in encrypted format. You must supply a password to be used to encrypt the SMK backup.

• RESTORE SERVICE MASTER KEY: Restores your SMK from a file. The SMK RESTORE statement requires you to supply the same password used when you backed up the SMK. Like ALTER SERVICE MASTER KEY, the RESTORE SERVICE MASTER KEY statement regenerates the entire encryption key hierarchy.

After installing a new SQL Server 2008 instance, you should immediately back up the SMK and store it in a safe location. The BACKUP SERVICE MASTER KEY statement takes the following form:

BACKUP SERVICE MASTER KEY TO FILE = 'c:\MK\backup_master_key.dat'

In this example, the SMK is backed up to the file c:\MK\backup_master_key.dat, and it is encrypted with the password p@$$w0rD. The encryption password is required if you need to restore the SMK.

If you need to alter, restore from backup, or regenerate your SMK, SQL Server will attempt to decrypt and re-encrypt all keys in the encryption key hierarchy. If any of these decryptions fail, the whole process will fail. If that happens, you can use the FORCE option on the ALTER SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY statements. However, be aware that if you must use the FORCE option, you can count on data loss.

Source of Information : Apress Accelerated SQL Server 2008


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner