Location: BlogsBlogland
Posted by: host	7/23/2008 6:41 AM
When an instance of SQL Server 2005 is installed a Service Master Key is created. This Service Master Key is the top unit in the encryption key management hierarchy for SQL Server. Its purpose is to encrypt the certificates, symmetric keys and asymmetric keys that are employed on any/all databases that reside within that instance. The Service Master Key is based off of the service account credentials as well as the machine key from the Windows Data Protection API. Without it, all subsequent keys would not be decipherable; thus rendering the data, in which the encryption was implemented, forever a mystery. Regular database backups are a best practice that most DBAs execute. This practice allows the database to be restored from a specific point in time in the event of data loss or other nightmare scenarios. If encryption is implemented a simple database backup will not suffice for recovery. The Service Master Key is not included in the standard database backup process. Backing up the Service Master Key is accomplished by executing the following script:           USE [master]          BACKUP SERVICE MASTER KEY TO FILE = 'filename'                ENCRYPTION BY PASSWORD = 'password'         GO By executing this script a file is created in the path and given the file name identified in the argument. Once this file has been created it can then be backed up through the regular server backup process. The file itself is encrypted with the password that is supplied in the argument. This prevents casual snooping of its contents or unauthorized recovery of the Service Master Key onto another server. Careful consideration of the location in which the backup file is written is strongly recommended. It would be further recommended that the file is written to a separate physical server than the one that the database backup file resides. While this will not prevent the thief from being able to restore the database onto another server it will prevent them from decrypting the encrypted data that is secured by the Service Master Key. The act of backing up anything is useless without knowing how to recover the backed up item. The following script allows you to restore the Service Master Key:          USE [master]          RESTORE SERVICE MASTER KEY FROM FILE = 'filename'               DECRYPTION BY PASSWORD = 'password'         GO The argument is the full path and file name of the backup file. The argument is the same password that was used to encrypt the Service Master Key backup file. For more information regarding the encryption hierarchy, go to: http://msdn.microsoft.com/en-us/library/ms189586.aspx For more information about Service Master Keys, go to: http://msdn.microsoft.com/en-us/library/ms189060.aspx For more information regarding backing up a Service Master Key, go to: http://msdn.microsoft.com/en-us/library/aa337561.aspx For more information regarding restoring a Service Master Key, go to: http://msdn.microsoft.com/en-us/library/aa337510.aspx
Permalink |  Trackback