Steve asked me about encryption in SQL Server 2005. I have found a few areas of information that are useful. In BOL there is an article about the Encryption Hierarchy this explains quite a lot about how it is organised. I quite like it because it has a diagram which is easy to understand.
SQL Server encrypts data with a hierarchical encryption key management infrastructure. Each layer encrypts the layer below it.
The first is the service master key This secures:
There are optional database master key's This is used for:
There are a number of Cryptographic Functions in Transact-SQL these support encryption, decryption, digital signing and the validation of digital signatures.
To see what is going on there are a number of Security Catalog Views which show you cryptographic information these are useful.
One tip I would recommend is to backup the service master key and secure it offsite for safety you can use ‘BACKUP MASTER KEY’ and ‘RESTORE MASTER KEY’ Transact-SQL statements.
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp_backups\keys\service_master_key' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';GO
RESTORE SERVICE MASTER KEY FROM FILE = 'c:\temp_backups\keys\service_master_key' DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'; GO
But very important When the service master key is restored, SQL Server decrypts all the keys and secrets that have been encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file. If any one of the decryptions fails, the restore will fail. You can use the FORCE option to ignore errors, but this option will cause the loss of any data that cannot be decrypted.