I want to collect here some basic facts about the encryptions of the various cryptographic keys in SQL Server 2005:
– a database master key (DbMK) must always have at least one password encryption, but it can have more than one. Also, by default, the DbMK has a second encryption using the service master key (SMK). Encryptions of the DbMK are managed using ALTER MASTER KEY <…> ADD/DROP ENCRYPTION BY <…>.
– a certificate can have the private key either encrypted by a password or encrypted by the DbMK. A certificate is protected by only one encryption at a time. The encryption method for the private key can be changed using ALTER CERTIFICATE <…> WITH PRIVATE KEY (DECRYPTION BY/ENCRYPTION BY PASSWORD = <…>). For example, if a certificate’s private key is protected by the DbMK and we want to use a password instead, we can use:
ALTER CERTIFICATE cert_name WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = password)
If we want to switch back to the DbMK encryption, we can issue:
ALTER CERTIFICATE cert_name WITH PRIVATE KEY (DECRYPTION BY PASSWORD = password)
– an asymmetric key works like a certificate.
– a symmetric key can have multiple encryptions by passwords, asymmetric keys, or certificates. The only restriction here is that there must be at least one encryption.
Update (12/07/2005): I omitted to specify that symmetric keys can be encrypted also by other symmetric keys and that they cannot be encrypted using the database master key.